What is the correct query for that?  
Author Message
Alex_H





PostPosted: Tue Sep 05 18:24:53 CDT 2006 Top

SQL Server >> What is the correct query for that?

Hi all,

I have a table that has an id (and other fields) :
Table1_id
...

Then I have another table with also an id (and other fields) :
Table2_id
...

And I have my pivot table for the 'many to many' relationship :
pivot_idTable1
pivot_idTable2

Let's say the pivot table contains these rows :
1, 1
2, 1
2, 2
3, 1
3, 2
3, 3

What I want is a stored proc to list all the Table1 entries that has
EVERY Table2 ids that I provide. For example, if I'd execute my stored
proc as follow :
EXEC sp_GetTable1Rows('1, 2')
That should return me the rows 2 and 3 of the Table1, because only
those twos contains both the Table2 ids 1 and 2.
If I execute :
EXEC sp_GetTable1Rows('1, 2, 3')
That should return me only the row 3 of the Table1, because only that
row contains the Table2 ids 1, 2 and 3.

Are my explanations clear enough? :S
Thanks for all your help!

SQL Server176  
 
 
Hugo





PostPosted: Tue Sep 05 18:24:53 CDT 2006 Top

SQL Server >> What is the correct query for that?

>Hi all,
>
>I have a table that has an id (and other fields) :
>Table1_id
>...
>
>Then I have another table with also an id (and other fields) :
>Table2_id
>...
>
>And I have my pivot table for the 'many to many' relationship :
>pivot_idTable1
>pivot_idTable2
>
>Let's say the pivot table contains these rows :
>1, 1
>2, 1
>2, 2
>3, 1
>3, 2
>3, 3
>
>What I want is a stored proc to list all the Table1 entries that has
>EVERY Table2 ids that I provide. For example, if I'd execute my stored
>proc as follow :
>EXEC sp_GetTable1Rows('1, 2')
>That should return me the rows 2 and 3 of the Table1, because only
>those twos contains both the Table2 ids 1 and 2.
>If I execute :
>EXEC sp_GetTable1Rows('1, 2, 3')
>That should return me only the row 3 of the Table1, because only that
>row contains the Table2 ids 1, 2 and 3.
>
>Are my explanations clear enough? :S
>Thanks for all your help!

Hi ibiza,

First, you'll have to transform the comma-delimited list of numbers to a
table with one column and one row for each number in the list. Check out
http://www.sommarskog.se/arrays-in-sql.html for several methods.

After that, google for "relational division" to discover how to proceed
from there.

Let me know if you need further assistance!

--
Hugo Kornelis, SQL Server MVP
 
 
ibiza





PostPosted: Tue Sep 05 19:18:45 CDT 2006 Top

SQL Server >> What is the correct query for that? Hi and thank you very much for your help!

That article on arrays in SQL is really useful, and I managed to build
a table from my query string. I use his Fixed-Length Array method with
strings, as it seems the fastest.

But I have some problems with the 'relational division'. That topic
looks very interesting, but quite complex. The best resource I found is
here : http://www.dbazine.com/ofinterest/oi-articles/celko1
but I feel it didn't help me much, because I still don't know what to
do with my dynamically created table (from the string). From the
examples I saw on the page, every query seems to deal with all the data
in the table (hangar for them). As for me, I don't want to validate if
(in my example) a certain idTable1 contains every idTable2, I need to
find those which contain the idTable2 I provide...

If you could lend me a hand and provide an example that applies to my
case, I'd be really grateful! No need to explain how to create the
table from the string, I only miss the relational division part.

Thanks again! :)



>
> >Hi all,
> >
> >I have a table that has an id (and other fields) :
> >Table1_id
> >...
> >
> >Then I have another table with also an id (and other fields) :
> >Table2_id
> >...
> >
> >And I have my pivot table for the 'many to many' relationship :
> >pivot_idTable1
> >pivot_idTable2
> >
> >Let's say the pivot table contains these rows :
> >1, 1
> >2, 1
> >2, 2
> >3, 1
> >3, 2
> >3, 3
> >
> >What I want is a stored proc to list all the Table1 entries that has
> >EVERY Table2 ids that I provide. For example, if I'd execute my stored
> >proc as follow :
> >EXEC sp_GetTable1Rows('1, 2')
> >That should return me the rows 2 and 3 of the Table1, because only
> >those twos contains both the Table2 ids 1 and 2.
> >If I execute :
> >EXEC sp_GetTable1Rows('1, 2, 3')
> >That should return me only the row 3 of the Table1, because only that
> >row contains the Table2 ids 1, 2 and 3.
> >
> >Are my explanations clear enough? :S
> >Thanks for all your help!
>
> Hi ibiza,
>
> First, you'll have to transform the comma-delimited list of numbers to a
> table with one column and one row for each number in the list. Check out
> http://www.sommarskog.se/arrays-in-sql.html for several methods.
>
> After that, google for "relational division" to discover how to proceed
> from there.
>
> Let me know if you need further assistance!
>
> --
> Hugo Kornelis, SQL Server MVP

 
 
Hugo





PostPosted: Sat Sep 09 17:12:42 CDT 2006 Top

SQL Server >> What is the correct query for that?

>Hi and thank you very much for your help!
>
>That article on arrays in SQL is really useful, and I managed to build
>a table from my query string. I use his Fixed-Length Array method with
>strings, as it seems the fastest.

Hi Ibiza,

Great! I'm glad you found his article to be of use. I always point to it
when answering questions related to arrays in SQL Server since he covers
everything I ever could include in a post and then some.

>But I have some problems with the 'relational division'. That topic
>looks very interesting, but quite complex. The best resource I found is
>here : http://www.dbazine.com/ofinterest/oi-articles/celko1
>but I feel it didn't help me much, because I still don't know what to
>do with my dynamically created table (from the string). From the
>examples I saw on the page, every query seems to deal with all the data
>in the table (hangar for them). As for me, I don't want to validate if
>(in my example) a certain idTable1 contains every idTable2, I need to
>find those which contain the idTable2 I provide...

You found a great link - it's a reprint of one of the chapters book in
"Joe Celko's SQL For Smarties" - a great book, BTW (not for beginners,
but a very good read for advanced SQL developers who want to take their
skills to the next level).

To stay with the pilot/plane/hangar equation, the following holds:
* Table with unpacked array of Table2-ids equates to hangar with planes
* Table1 equates to pilots
* Junction table equates to list of pilot flying skills.

Here's roughly how your query should look, based on some assumptions WRT
table and column names:

SELECT j.Table1ID
FROM Junction AS j

ON f.str = j.Table2ID
GROUP BY j.Table1ID
HAVING COUNT(*) = (SELECT COUNT(*)


(Untested - see www.aspfaq.com/5006 if you prefer a tested reply)

--
Hugo Kornelis, SQL Server MVP