get intersection of 2 subqueries

Posted on 2011-04-26
Last Modified: 2012-05-11

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

Question by:andieje
    LVL 15

    Expert Comment

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

    Expert Comment

    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.
    LVL 38

    Expert Comment

    by:Aaron Tomosky
    LVL 40

    Expert Comment

    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
    LVL 142

    Expert Comment

    by:Guy Hengel [angelIII / a3]
    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 Comment

    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
    LVL 142

    Expert Comment

    by:Guy Hengel [angelIII / a3]
    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
    LVL 4

    Accepted Solution

    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
    select cola from a inner join b inner join c where fieldy = Y
    group by cola having count(cola) > 2

    Author Comment

    thanks - the union idea seems to work

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    What Is Threat Intelligence?

    Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

    Loading csv or delimited data files to MySQL database is a very common task frequently questioned about and almost every time LOAD DATA INFILE comes to the rescue. Here we will try to understand some of the very common scenarios for loading data …
    A lot of articles have been written on splitting mysqldump and grabbing the required tables. A long while back, when Shlomi ( had suggested a “sed” way, I actually shell …
    This video is in connection to the article "The case of a missing mobile phone (". It will help one to understand clearly the steps to track a lost android phone.
    Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

    737 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    18 Experts available now in Live!

    Get 1:1 Help Now