Creating a 1:1 JOIN on a 1:M relationship
-
I have a query for a report. I was under the impression that there was a 1:1 relationship between two of the tables; I have discovered (a year later) this is not the case. Essentially what I have is a [Customer] table and a [CustomerInfo] table. I need to join the [CustomerInfo] table for one column an integer column called SIC. I was doing just a regular left join on CustomerID. The problem: I was getting duplicate customers for those customer records that have more than one record in the [CustomerInfo] table. As this is for reporting, and there is not "supposed" to be more than one record in the [CustomerInfo] table it would be a acceptable solution to only return the first [CustomerInfo] record for each [Customer]. It does not work to duplicate [Customers] in this case. Is there a way that I can join only the first [CustomerInfo] record to each [Customer]? I realize that I will omit some [CustomerInfo] records but that is completely fine in this case. For instance I would like a query that does this.
[Customer]
CustID CustName 1 cust1 2 cust2 [CustInfo]
CustID SIC 1 125 1 null ---(will get omitted) 2 123 !!!!!!!RESULT!!!!!!!!!
CustID CustName SIC 1 cust1 125 2 cust2 123 Thanks!
-
SELECT *
FROM Customer
LEFT JOIN CustInfo
ON Customer.CustId = CustInfo.CustId
AND CustInfo.SIC IS NULL
--
Adam Machanic
whoisactive -
Need a unique key on CustomerInfo. I'll assume it's CustInfoID. I'll also assume that the "first" record means the one with the lowest CustInfoID.
select *
from Customer
left join CustomerInfo
on (CustomerInfo.CustID = Customer.CustID)
and not exists(
select *
from CustomerInfo2
where (CustomerInfo2.CustID = CustomerInfo.CustID)
and (CustomerInfo2.CustInfoID > CustomerInfo.CustInfoID)
)
-
SIC is not always null just in my example.
-
Problem is that [CustomerInfo] has no unique ID column. Any ideas?
-
Sam,
CustomerInfo has no primary key at all?
That's definitely a much bigger problem than your current query! All tables should have a primary key... Fix that issue first, then work on the query.
--
Adam Machanic
whoisactive -
I agree it is a huge problem. But here is the deal. I cannot change it. It is a third party database and cannot really be changed. I know it sux. I have been battling with this crappy database/schema for 2 years now. It is bad. No keys, no datatypes other than strings, multiple values in one column (I do a lot of substirng() functions), the data is not really normalised in any way that make sense. In short; I cannot change this piece of crap! I have to speand my crappy life working around issues like the one that I am describing to you!
-
I don't know how you can possibly hack around this thing without any keys in place. Maybe you could add an IDENTITY column, at least, that you could use for situations like this? That shouldn't interfere with any of the queries already written, and would give you much greater flexibility.
--
Adam Machanic
whoisactive -
You are right. I think that I might do that. It is just a DTS copy that is imported nightly anyway. I just don't like to make changes because it becomes a nightmre to administer.
-
Try the following statement :
select * from Customer left join
(select distinct CustId,SIC from CustInfo a
where SIC=(select max(SIC) from CustInfo
where a.CustId=CustId)
 
as CustInfoSelectionon Customer.CustId=CustInfoSelection.CustId
-
Do you always want the "last" entry into the Customer Info table for that CustID? (any dates or other data in the Customer Info table you could use?)
I wasn't born stupid - I had to study.
-
Worked like a charm...
THANKS!!!!
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply