• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 631
  • Last Modified:

get intersection of 2 subqueries

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
0
andieje
Asked:
andieje
  • 2
  • 2
  • 2
  • +3
1 Solution
 
Jagadishwor DulalBraces MediaCommented:
It will be better if you can post field name of your both table. Yes you must use a join query for this.
0
 
msk_apkCommented:
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.
0
 
Aaron TomoskyTechnology ConsultantCommented:
0
Get quick recovery of individual SharePoint items

Free tool – Veeam Explorer for Microsoft SharePoint, enables fast, easy restores of SharePoint sites, documents, libraries and lists — all with no agents to manage and no additional licenses to buy.

 
SharathData EngineerCommented:
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
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
0
 
andiejeAuthor 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
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
0
 
msk_apkCommented:
intersect is the operator that can be applied between those queries but mysql does not support intersect. hence

select cola from a inner join b inner join c where fieldx = X
union
select cola from a inner join b inner join c where fieldy = Y
group by cola having count(cola) > 2
0
 
andiejeAuthor Commented:
thanks - the union idea seems to work
0

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

  • 2
  • 2
  • 2
  • +3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now