Results 1 to 4 of 4
  1. #1
    Join Date
    Oct 2005
    Posts
    34

    Unanswered: INNER JOIN and Unmatched query in one query?

    I'm having a bit of a problem trying to get the two to work together in one query. Basically, I have to do a query where the manufacturing company fields in two seperate tables (first table [A] being a master table having ALL data and the second table [B] only containing partial data) are equal to one another, then find out which model numbers, with the company name, are missing in the second table vs the first. the query so far looks like this with no luck:

    select a.company, a.model_number
    from A INNER JOIN B on A.company=B.Company
    where a.model_number NOT IN
    (select E.model_number
    from A as D LEFT JOIN B as E on D.country=E.country
    where e.country is null)


    Any insight as to what I'm doing wrong?

  2. #2
    Join Date
    Jul 2004
    Location
    Southampton, UK
    Posts
    368
    Your problem is here:

    (select E.model_number
    from A as D LEFT JOIN B as E on D.country=E.country
    where e.country is null)

    Suppose you list all fields from both tables (always useful for helping understand what's going on) and remove the null for now so you have sql:

    select D.model_number, D.country, E.model_number, E.country
    from A as D LEFT JOIN B as E on D.country=E.country;

    The result will return something like:


    mod1____countryw_____mod1_____countryw
    mod2____countryx_______________________
    mod3____countryy_____mod3_____countryy
    mod4____countryz_______________________

    You'll notice here that there are gaps (nulls) in the e.country column so if you then add your null criteria you will have:

    select D.model_number, D.country, E.model_number, E.country
    from A as D LEFT JOIN B as E on D.country=E.country where e.country is null;

    mod2____countryx_______________________
    mod4____countryz_______________________

    But the only column you are asking to be returned is e.model_number (the third blank column in my example). But there's nothing to return.

    However, is you ask to list d.model_number (the first column), I think this is what you want.

    So the sql should be:
    SELECT D.model_number
    FROM A AS D LEFT JOIN B AS E ON D.country = E.country
    WHERE E.country Is Null;

    There's no need to feed this as a sub-query as you already have your answer (if I've understood your question )

    hth
    Chris

  3. #3
    Join Date
    Oct 2005
    Posts
    34
    Quote Originally Posted by wysiwyg6000
    I'm having a bit of a problem trying to get the two to work together in one query. Basically, I have to do a query where the manufacturing company fields in two seperate tables (first table [A] being a master table having ALL data and the second table [B] only containing partial data) are equal to one another, then find out which model numbers, with the company name, are missing in the second table vs the first. the query so far looks like this with no luck:

    select a.company, a.model_number
    from A INNER JOIN B on A.company=B.Company
    where a.model_number NOT IN
    (select E.model_number
    from A as D LEFT JOIN B as E on D.country=E.country
    where e.country is null)


    Any insight as to what I'm doing wrong?
    I think I gave you the incorrect second half of the query. here is what it should have been:

    select a.company, a.model_number
    from A INNER JOIN B on A.company=B.Company
    where a.model_number NOT IN
    (select d.model_number
    from A as D LEFT JOIN B as E on D.model_number=E.model_number
    where e.model_number is null)

    The goal here is to show all of the missing records in table B vs table A, but ONLY show the records where the company names match 100%. That's why I had to go for the inner join up top.

  4. #4
    Join Date
    Jul 2004
    Location
    Southampton, UK
    Posts
    368
    Sorry I must be having a dull day 'cos I'm not with you. Perhaps you could demonstrate what you expect to happen with some sample data.
    Chris

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •