Brutal Query

Question

Post reply

Brutal Query

Jules Bonnot

SSCertifiable

Points: 5488
More actions
June 30, 2006 at 3:51 am

#115572

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

www.sql-library.com[/url]

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

stax68 SSChampion Points: 11711 More actions · Answer 1

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

sushila SSC-Dedicated Points: 35293 More actions · Answer 2

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 !!!**

stax68 SSChampion Points: 11711 More actions · Answer 3

Ta muchly

Tim Wilkinson

"If it doesn't work in practice, you're using the wrong theory"
- Immanuel Kant

SwePeso SSC-Dedicated Points: 39757 More actions · Answer 4

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"

stax68 SSChampion Points: 11711 More actions · Answer 5

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

SwePeso SSC-Dedicated Points: 39757 More actions · Answer 6

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"

stax68 SSChampion Points: 11711 More actions · Answer 7

Thanks

Tim Wilkinson

"If it doesn't work in practice, you're using the wrong theory"
- Immanuel Kant

Jules Bonnot SSCertifiable Points: 5488 More actions · Answer 8

yep it works a treat. Just deleted 25 lines of dynamic sql and replaced it with your 5 lines

thanks

www.sql-library.com[/url]

stax68 SSChampion Points: 11711 More actions · Answer 9

no probs

Tim Wilkinson

"If it doesn't work in practice, you're using the wrong theory"
- Immanuel Kant

noeld SSC Guru Points: 96590 More actions · Answer 10

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

stax68 SSChampion Points: 11711 More actions · Answer 11

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