Intersection of dymamically created SELECT statement

[erixire]

Hi,

I want to know if it's possible to do a few intersection (WHERE EXISTS, IN, INNER JOIN, I really dont' know how???) on some select statement created dynamically. The number of fields and there names are always the same in the select statements. But the number can vary. So I want to do something like this (I know the syntax is not correct):

(Select ... from ... where ...)
INTERSECTION
(Select ... from ... where ...)
INTERSECTION
(Select ... from ... where ...)
INTERSECTION
(Select ... from ... where ...)

What I want in the output is the rows I can found in all of the 'select' statement results (so the intersection of all of them).

Thanks.

 

[donutman]

I'm not certain what you imply by INTERSECTION. Do you really mean that the data must "match" in some way to be included or are you trying to "add" additional rows or possibly, you may want to only select rows that are complete duplicates in all tables? Regardless we will likely need to know the which columns need to match.
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[dbomrrsm]

I thin you mean UNION but for that the select statements all need to be bringing back the same number of fields with the same data types.

[blue]DBomrrsm[/blue]

 

[erixire]

Hi again,

I haven't been clear enough, sorry for that.

I really mean 'INTERSECTION' mathematically speaking (set theory). I want the intersection of all my sets returned by the 'select' statement. For exemple:

--Suppose that this is the output of the first 'select ... from ... where ...'
Field1 Field2
------ ------
foo 1
foo 2
foo 3
bar 1
bar 2
bar 3

--Suppose that this is the output of the second 'select ... from ... where ...'
Field1 Field2
------ ------
foo 1
bar 2
magic 5
magic 6

--Suppose that this is the output of the third 'select ... from ... where ...'
Field1 Field2
------ ------
foo 1
foo 3
bar 1
bar 2
bar 3

...And so on...

--I want the output:
Field1 Field2
------ ------
foo 1
bar 2

Which is the intersection of all 3 results. The thing is, there is no 'INTERSECT' command with MS SQL Server, so I cannot do something like that:

(select ... from ... where ...)
INTERSECT
(select ... from ... where ...)
INTERSECT
(select ... from ... where ...)

The number of (select ... from ... where ...) is never the same (it can range from 1 to 10). So the only thing I can work with is the complete string of the differents 'select' statements. I want to combine them to have the rows which are present in all the individual results, so the 'intersection'.

Can somebody help me with this one?

Thanks.

 

[SQLSister]

What you are looking for is a join. If you want all the records from both selects use an inner join, if you want all the records from table1 and only those which match in table2, use a left join. When you join tables ( or selct statments as derived tbles) you need to know what fields thay have ion comon to join on.

For instance, if each part of the join is identified by the customer ID, that is the join field. Joins can be performed on one or more fields, but the more fields the less efficent the join is likely to be. It is best to design so you can join on only one field where possible.

To be more specific, you would need to provide more ifomation such as the data you have inthe various tables (or selects) and the result set you want.

Questions about posting. See faq183-874
Click here to learn Ways to help with Tsunami Relief

 

[donutman]

Based upon the example you provided, what SQLSister is saying is that you would perform the following query:

[Blue]SELECT[/Blue] T1.Field1[Gray],[/Gray] T2.Field2 
   [Blue]FROM[/Blue] Table1 T1 [Blue]INNER[/Blue] [Gray]JOIN[/Gray] Table2 T2
      [Blue]ON[/Blue] T1.Field1[Gray]=[/Gray]T2.Field1 [Gray]AND[/Gray] T1.Field2[Gray]=[/Gray]T2.Field2
   [Blue]INNER[/Blue] [Gray]JOIN[/Gray] Table3 T3
      [Blue]ON[/Blue] T2.Field1[Gray]=[/Gray]T3.Field1 [Gray]AND[/Gray] T2.Field2[Gray]=[/Gray]T3.Field2

-Karl


[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[erixire]

Thanks for the replies,

Actually, my problem is that the request is created dynamically. Let me explain. I have a PHP script which query a database. A table is created from that query. I want my users to be able to sort thru the first result by clicking one element from the result. Because I still have the string of the request in memory, I want to take the first query and combine it with another query (created dynamically based on the 'word' clicked by a user on an html table) which will be more specific. So imagine the user have this result at the screen (in HTML with HREFs on each elements of the table):

Field1 Field2
------ ------
foo 1
foo 2
foo 3
bar 1
bar 2
bar 3

If the user click the number '1', I want to have this second result at the sceen:

Field1 Field2
------ ------
foo 1
bar 1

And then, if the user click the word 'foo', I want to have this result:

Field1 Field2
------ ------
foo 1

And so on...

Because I can construct each (select ... from ... where) statement dynamically and put it in a string (say query1, query2, query3, etc), I was wandering if there was a way to do an intersection (or inner join, or whatever) from each query so that I can construct dynamically a big request like that:

$finalQuery = $query1 + "key word for intersection" + $query2 + "key word for intersection" + $query3

So that $query1 = (select ... from ... where ...)
$query2 = (select ... from ... where ...)
$query3 = (select ... from ... where ...)

Which will result as this new query:
(select ... from ... where ...)
intersect
(select ... from ... where ...)
intersect
(select ... from ... where ...)

Thanks a lot for trying to help me.