June 30, 2006 at 3:51 am
I have to create an sp which excepts a coma delimited list of ids of varying length.
these are the primary keys of a table. i then have to check that the child records(properties) of these entities are all the same.
I have sorted out getting the ids into a table variable just not sure if there is a set based way to compare then.
Assuming i am only given two ids in the input parameter i join the child tables on and get the following potential record sets
The first record set below has identical properties for both id 1 and 2 . The second record set
has different properties becasie id 2 has fk 19 and id 1 doesnt. if all fks match the sp should return true and false if there is any difference. This is obviously greatly complicated by the fact that the number of ids passed in can vary.
I thought about using full outer joins and creating seperate temp tables for each id. But because i dont know how many tables to create i would have to use dynamic SQL .. Umm just had an idea anyway ..
Thanks in advance,
Jules
create
table test1 (id int, fk int)
insert
table
select
1, 13
union
select
1, 14
union
select
1, 15
union
select
1, 16
union
select
1, 17
union
select
1, 18
union
select
2, 13
union
select
2, 14
union
select
2, 15
union
select
2, 16
union
select
2, 17
union
select
2, 18
create
table test2(id int, fk int)
insert
table
select
1, 13
union
select
1, 14
union
select
1, 15
union
select
1, 16
union
select
1, 17
union
select
1, 18
union
select
2, 13
union
select
2, 14
union
select
2, 15
union
select
2, 16
union
select
2, 17
union
select
2, 19
June 30, 2006 at 4:37 am
if exists(
select fk from tbl group by fk having count(distinct id) != (select count(distinct id) from tbl)
)
select @retval = 0
Tim Wilkinson
"If it doesn't work in practice, you're using the wrong theory"
- Immanuel Kant
June 30, 2006 at 5:57 am
Tim - applause from the stands - just for the sheer brevity of the solution when compared to the convolutions that Jules was contemplating...
**ASCII stupid question, get a stupid ANSI !!!**
June 30, 2006 at 6:37 am
Ta muchly
Tim Wilkinson
"If it doesn't work in practice, you're using the wrong theory"
- Immanuel Kant
June 30, 2006 at 7:02 am
Doesn't it only calculate if the number of fk for each id is different?
What if ID 1 has fk 3 and 4, while ID 2 has fk 3 and 5? Both have 2 fk.
-- prepare test data
declare @test1 table (id int, fk int)
insert @test1
select 1, 13 union all
select 1, 14 union all
select 1, 15 union all
select 1, 16 union all
select 1, 17 union all
select 1, 18 union all
select 3, 13 union all
select 3, 14 union all
select 3, 15 union all
select 3, 16 union all
select 3, 17 union all
select 3, 18 union all
select 2, 13 union all
select 2, 14 union all
select 2, 15 union all
select 2, 16 union all
select 2, 17 union all
select 2, 18
declare @test2 table (id int, fk int)
insert @test2
select 1, 13 union all
select 1, 14 union all
select 1, 15 union all
select 1, 16 union all
select 1, 17 union all
select 1, 18 union all
select 3, 13 union all
select 3, 14 union all
select 2, 13 union all
select 2, 14 union all
select 2, 15 union all
select 2, 16 union all
select 2, 17 union all
select 2, 19
-- get the id that has different fk!
SELECT DISTINCT COALESCE(t1.id, t2.id) ID
FROM @test1 t1
FULL OUTER JOIN @test2 t2 ON t2.id = t1.id AND t2.fk = t1.fk
WHERE t1.id IS NULL
OR t2.id IS NULL
The real trick is to make the query dynamic, and here is how you do it.
SELECT DISTINCT COALESCE(t1.id, t2.id, ...ID column for every table...) ID
FROM @test1 t1 -- first table
FULL OUTER JOIN @test2 t2 ON t2.id = t1.id AND t2.fk = t1.fk -- second table
FULL OUTER JOIN 3rdTable t2 ON t3.id = t1.id AND t3.fk = t1.fk -- third table
WHERE t1.id IS NULL -- first table
OR t2.id IS NULL -- second table
OR t3.id IS NULL -- third table
N 56°04'39.16"
E 12°55'05.25"
June 30, 2006 at 7:05 am
Peter, can you come up with an example in which my solution doesn't give the right result? (Clue: the answer is no.)
Tim Wilkinson
"If it doesn't work in practice, you're using the wrong theory"
- Immanuel Kant
June 30, 2006 at 7:18 am
I thought the idea was to return to ID where there was discrepancy, not the fk itself.
My mistake. You have written an awsome query! Now it is mine turn to remember this one
N 56°04'39.16"
E 12°55'05.25"
June 30, 2006 at 7:27 am
Thanks
Tim Wilkinson
"If it doesn't work in practice, you're using the wrong theory"
- Immanuel Kant
June 30, 2006 at 7:50 am
June 30, 2006 at 7:53 am
no probs
Tim Wilkinson
"If it doesn't work in practice, you're using the wrong theory"
- Immanuel Kant
June 30, 2006 at 1:16 pm
Peter, can you come up with an example in which my solution doesn't give the right result? (Clue: the answer is no.)
assume first table has id =3 and fk =17 only and second table id =4 and fk=17 only (just one record in each table)
I am not sure that query returns the correct answer...!?!
* Noel
June 30, 2006 at 6:16 pm
What do you mean by 'the second table'? Where has this second table come from, and how does it get itself involved in my SQL statement?
Tim Wilkinson
"If it doesn't work in practice, you're using the wrong theory"
- Immanuel Kant
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply