Many to Many Check

  • An SQL puzzle that's tripped me up. I wound up doing it brute force.

    Simple Many to Many between Customer and Location. Intervening table is CustomerLocation. They are linked by CustomerPK and LocationPK. My idea was to fetch the crossproduct of Customers and Locations joined to the CustomerLocation table to see which, if any, weren't linked...

    SELECT Customer.CustomerPK, Location.LocationPK

    FROM Customer, Location

    This will fetch the cross product of customers and locations quite happily, but now I want to join it to CustomerLocation...

    SELECT Customer.CustomerPK, Location.LocationPK, CustomerLocation.CustomerLocationPK

    FROM Customer, Location

    LEFT OUTER JOIN CustomerLocation

    ON CustomerLocation.CustomerPK = CustomerPK

    AND CustomerLocation.LocationPK = Location.LocationPK

    But it always errors in the join clause complaining that it can't bind Customer.CustomerPK.

    Scratching my head, I decided to try:

    SELECT Customer.CustomerPK, Location.LocationPK, CustomerLocation.CustomerLocationPK

    FROM Customer

    JOIN Location

    LEFT OUTER JOIN CustomerLocation

    ON CustomerLocation.CustomerPK = Customer.CustomerPK

    AND CustomerLocation.LocationPK = Location.LocationPK

    But again, the outer join fails, claiming unable to bind Customer.CustomerPK

    Suggestions?

    (I solved the immediate problem with a brute-force solution, that didn't matter since the cross product was only 26 records.)

    [font="Arial"]Halfbubble ------------
    You just can't do this stuff unless you're at least half a bubble off center.[/font]

  • Half Bubble (8/19/2009)


    An SQL puzzle that's tripped me up. I wound up doing it brute force.

    Simple Many to Many between Customer and Location. Intervening table is CustomerLocation. They are linked by CustomerPK and LocationPK. My idea was to fetch the crossproduct of Customers and Locations joined to the CustomerLocation table to see which, if any, weren't linked...

    SELECT Customer.CustomerPK, Location.LocationPK

    FROM Customer, Location

    This will fetch the cross product of customers and locations quite happily, but now I want to join it to CustomerLocation...

    SELECT Customer.CustomerPK, Location.LocationPK, CustomerLocation.CustomerLocationPK

    FROM Customer, Location

    LEFT OUTER JOIN CustomerLocation

    ON CustomerLocation.CustomerPK = CustomerPK

    AND CustomerLocation.LocationPK = Location.LocationPK

    But it always errors in the join clause complaining that it can't bind Customer.CustomerPK.

    Scratching my head, I decided to try:

    SELECT Customer.CustomerPK, Location.LocationPK, CustomerLocation.CustomerLocationPK

    FROM Customer

    JOIN Location

    LEFT OUTER JOIN CustomerLocation

    ON CustomerLocation.CustomerPK = Customer.CustomerPK

    AND CustomerLocation.LocationPK = Location.LocationPK

    But again, the outer join fails, claiming unable to bind Customer.CustomerPK

    Suggestions?

    (I solved the immediate problem with a brute-force solution, that didn't matter since the cross product was only 26 records.)

    To paraphrase, You are looking for Customers with no Locations and Locations with no Customers, correct?

    select

    'Customer' as TableName,

    Cust.CustomerPK,

    CustLoc.LocationPK

    from

    dbo.Customer Cust

    left outer join dbo.CustomerLocation CustLoc

    on (Cust.CustomerPK = CustLoc.CustomerPK)

    where

    CustLoc.LocationPK is null

    union

    select

    'Location',

    CustLoc.CustomerPK,

    Loc.LocationPK

    from

    dbo.Location Loc

    left outer join dbo.CustomerLocation CustLoc

    on (CustLoc.LocationPK = Loc.LocationPK)

    where

    CustLoc.CustomerPK is null;

    Give the above code try and let us know if it works.

  • I never think of union.

    This isn't quite it either. I'm looking at 18 customers and 2 locations, so there should be 18*2 customerlocations. Your query finds me 2 rows, but there are only 22 customerlocation rows.

    It looks for customers that don't have a CustomerLocation row, and locations that don't have customerlocation rows.

    Maybe I can use count and group by...

    select Customer.customerpk, COUNT(*)

    from Customer

    left outer join CustomerLocation on CustomerLocation.CustomerPK = Customer.customerpk

    group by Customer.customerpk

    Now this gives me a list of how many customerlocation rows each customer has, but doesn't tell me which are missing. I suppose I could build a temporary table of the cross product and compare that with the customerlocation table, but in production data, I'm not sure if that'd be better than my brute force approach...

    [font="Arial"]Halfbubble ------------
    You just can't do this stuff unless you're at least half a bubble off center.[/font]

  • Half Bubble (8/20/2009)


    I never think of union.

    This isn't quite it either. I'm looking at 18 customers and 2 locations, so there should be 18*2 customerlocations. Your query finds me 2 rows, but there are only 22 customerlocation rows.

    It looks for customers that don't have a CustomerLocation row, and locations that don't have customerlocation rows.

    Maybe I can use count and group by...

    select Customer.customerpk, COUNT(*)

    from Customer

    left outer join CustomerLocation on CustomerLocation.CustomerPK = Customer.customerpk

    group by Customer.customerpk

    Now this gives me a list of how many customerlocation rows each customer has, but doesn't tell me which are missing. I suppose I could build a temporary table of the cross product and compare that with the customerlocation table, but in production data, I'm not sure if that'd be better than my brute force approach...

    Obviously I don't understand your problem, and what I paraphrased must not be it either.

    Please explain your problem. Then, provide us with the DDL for the three tables, sample data for those tables in a readily consummable format that is representative of your problem, and what the expected results from the query should be based on that sample data.

    For assistance with this request, please read AND follow the instructions in the first article you will find referenced below in my signature block.

  • if you are saying that all customer / locations combinations should be in table CustomerLocation and you want to find the combinations that are "missing" then please try this

    SELECT xref.CustomerPK, xref.LocationPK

    FROM (SELECT Cust.CustomerPK, dbo.Location.LocationPK

    FROM dbo.Location CROSS JOIN dbo.Customer Cust) AS xref

    LEFT OUTER JOIN

    dbo.CustomerLocation ON xref.CustomerPK = dbo.CustomerLocation.CustomerPK

    AND xref.LocationPK = dbo.CustomerLocation.LocationPK

    WHERE (dbo.CustomerLocation.CustomerPK IS NULL) AND (dbo.CustomerLocation.LocationPK IS NULL)

    ORDER BY CustomerPK, LocationPK

    If I missed understood, then apologies 🙂

    regards gah

    ________________________________________________________________
    you can lead a user to data....but you cannot make them think
    and remember....every day is a school day

  • gah (8/20/2009)


    if you are saying that all customer / locations combinations should be in table CustomerLocation and you want to find the combinations that are "missing" then please try this

    SELECT xref.CustomerPK, xref.LocationPK

    FROM (SELECT Cust.CustomerPK, dbo.Location.LocationPK

    FROM dbo.Location CROSS JOIN dbo.Customer Cust) AS xref

    LEFT OUTER JOIN

    dbo.CustomerLocation ON xref.CustomerPK = dbo.CustomerLocation.CustomerPK

    AND xref.LocationPK = dbo.CustomerLocation.LocationPK

    WHERE (dbo.CustomerLocation.CustomerPK IS NULL) AND (dbo.CustomerLocation.LocationPK IS NULL)

    ORDER BY CustomerPK, LocationPK

    If I missed understood, then apologies 🙂

    regards gah

    And another option if this is the case:

    select

    cust.CustomerPK,

    loc.LocationPK

    from

    dbo.Customer cust cross join dbo.Location loc

    except

    select

    custloc.CustomerPK,

    custloc.LocationPK

    from

    dbo.CustomerLoction;

  • And another option if this is the case:

    select

    cust.CustomerPK,

    loc.LocationPK

    from

    dbo.Customer cust cross join dbo.Location loc

    except

    select

    custloc.CustomerPK,

    custloc.LocationPK

    from

    dbo.CustomerLoction;

    Nice one Lynn...always forget about "except", spend most of life in SQL 2000...thanks for the reminder:-)

    ________________________________________________________________
    you can lead a user to data....but you cannot make them think
    and remember....every day is a school day

  • Nice, Lynn!

    select

    cust.CustomerPK,

    loc.LocationPK

    from dbo.Customer cust cross join dbo.Location loc

    except

    select

    custloc.CustomerPK,

    custloc.LocationPK

    from

    dbo.CustomerLoction;

    [font="Arial"]Halfbubble ------------
    You just can't do this stuff unless you're at least half a bubble off center.[/font]

  • How's this?

    select customer.CustomerPK, Location.LocationPK, CustomerLocation.CustomerLocationPK

    FROM Customer

    CROSS JOIN Location

    left outer join CustomerLocation on CustomerLocation.CustomerPK = Customer.CustomerPK AND CustomerLocation.LocationPK = Location.LocationPK

    WHERE CustomerLocation.CustomerLocationPK IS NULL

    [font="Arial"]Halfbubble ------------
    You just can't do this stuff unless you're at least half a bubble off center.[/font]

  • dunno...why dont you tell us:-D

    does it give you the results you require?

    ________________________________________________________________
    you can lead a user to data....but you cannot make them think
    and remember....every day is a school day

  • Let's see, do I have those tables? No. Do I even have any data for those tables if I had them? No. Do I even fully understand the problem you are trying to solve? No.

    Sorry, you have to tell us if the solution you posted works or not.

  • You have to guess

    [font="Arial"]Halfbubble ------------
    You just can't do this stuff unless you're at least half a bubble off center.[/font]

  • Half Bubble (8/21/2009)


    You have to guess

    Guess...???

    You posted a problem,

    You were given some possible solutions based on the limited information you gave,

    You did not respond to request to provide more details such as table defs and sample data,

    You then post your solution and ask "how this?"....and then say "You have to guess"...!!!!

    Sorry, its not my problem..its yours

    ________________________________________________________________
    you can lead a user to data....but you cannot make them think
    and remember....every day is a school day

  • gah (8/21/2009)


    Half Bubble (8/21/2009)


    You have to guess

    Guess...???

    You posted a problem,

    You were given some possible solutions based on the limited information you gave,

    You did not respond to request to provide more details such as table defs and sample data,

    You then post your solution and ask "how this?"....and then say "You have to guess"...!!!!

    Sorry, its not my problem..its yours

    I quite agree. Answer me this; why should we help you when you don't seem interested in helping yourself?

    We don't get paid for the assistance we give, we are volunteering our time and knowledge in order to help others increase their knowledge and skills. We all have other things we could be doing, but chose to give back to the SQL Server Community that has been there when we also need help.

    Please keep that in mind when you are asking for help.

  • Well, Lynn, you posted a nice solution that gave a result. I said it was nice. (Scroll up) Seeing it run, I was able to settle on a variation. My program no longer does it the hard way.

    [font="Arial"]Halfbubble ------------
    You just can't do this stuff unless you're at least half a bubble off center.[/font]

Viewing 15 posts - 1 through 15 (of 15 total)

You must be logged in to reply to this topic. Login to reply