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 thisSELECT 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