We help IT Professionals succeed at work.

get intersection of 2 subqueries

andieje
andieje asked
on
Medium Priority
704 Views
Last Modified: 2012-05-11
Hi

If i had these 2 queries:

select * from a inner join b inner join c where field = X

select * from a inner join b inner join c where field = Y

what is the best way to find the rows from table a that are in both result sets? would i have to do a self join on the query results? Please could you advise how and how basic example query showing how to do this

thanks
Comment
Watch Question

Jagadishwor DulalSenior Web Developer
CERTIFIED EXPERT

Commented:
It will be better if you can post field name of your both table. Yes you must use a join query for this.

Commented:
you are applying where criteria on the same field and join fields are same. then i believe there should not be any overlap between first query result and second query result.
Aaron TomoskyDirector, SD-WAN Solutions
CERTIFIED EXPERT

Commented:
SharathData Engineer
CERTIFIED EXPERT

Commented:
Both the queries will give different result set and there won't be common records because field value is different in both queries and you have * in SELECT clause. post the result of both the queries and the expected result
Guy Hengel [angelIII / a3]Billing Engineer
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2009

Commented:
like in the other query, you can "join" 2 times the same table...

select * 
from a 
join b  on a.field = b.field
join c c1 on c1.field = b.field and c1.field = X
join c c2 on c2.field = b.field and c2.field = Y

Open in new window


c1 and c2 are aliases in the query for the same table, make the table "c" is used 2 times independantly (like 2 copies of the same table, but only logically, of course)

hope this helps

Author

Commented:
ok, sorry. I posted the question late at night and i can see i have messed up the question. Sorry about that.

select a.* from table a inner join b inner join c where 'some conditions'

select a.* from table a inner join d inner join e where 'some conditions'

So i have 2 queries which are selecting records from table a by totally different criteria based on joins to different tables

I want to find the records which are in the intersection of the 2 queries
Guy Hengel [angelIII / a3]Billing Engineer
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2009

Commented:
well, some posts with syntax suggestions have been given.
if you cannot give more details about the actual query, we cannot give more concrete query syntax ...

hope it helps nevertheless
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION

Author

Commented:
thanks - the union idea seems to work
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.