Joining unmatched tables with duplicates
Hi, I am a beginner SQL user and am trying to take table 1 (contracts) and pull in the calltype from table2 (equipment) with the control number as the common field.
However, there are duplicate control numbers in table2 which are causing me an issue.
I have created this query on table 2 to get the data I want to combine with table1:
Select DISTINCT calltype, controlnumber
FROM ControlTracking.dbo.Equipment
WHERE CALLTYPE IS NOT NULL
I want to join or what ever is the best way the data results from the query above to my table1 to pull in the calltype field into my table1.
I've read about inner joins, outer join, left, right, unions and I just can't seem to get it to do what I want.
Data Example:
Table2:
Control Call
Number Type
15837 2
15837 2
16268 1
16269 2
16270 3
16279 2
16279 2
Table1:
Control Customer Sales
Number Number Rep
15837 ABC Sam
16268 DEF Jen
16269 GHI Beckey
16270 JKL John
16279 MNO Mary
Expected Results:
Control Call Customer Sales
Number Type Number Rep
15837 2 ABC Sam
16268 1 DEF Jen
16269 2 GHI Beckey
16270 3 JKL John
16279 2 MNO Mary
Thanks in advance for any help you can provide.
Software/Hardware used:
Microsoft SQL Server 2005
Software/Hardware used:
Microsoft SQL Server 2005
ASKED:
November 29, 2012 5:50 PM
UPDATED:
November 29, 2012 6:12 PM
Answer Wiki:
Based on what you've provided (and given that I can't really read the samples you provided) you'll want an inner join like this.
select {your columns}
from contracts
inner join (select distinct calltype, controlnumber from Equiptment where calltype is not null) equiptment on contracts.calltype = equiptment.calltype 
All Answer Wiki Contributors:
Denny Cherry 
64,520 pts. ,
Michael Tidmarsh 11,400 pts. ,
jruth13 25 pts.
64,520 pts. ,
Michael Tidmarsh 11,400 pts. ,
jruth13 25 pts. To see all answers submitted to the Answer Wiki: View Answer History.
Welcome to ITKE, jruth13! We have plenty of SQL experts who should be able to help you with your problem.
Don’t forget to fill out your profile page and check out our FAQ for more information on how our community works.
Thank you for your response. Sorry about the format of the post. When I wrote it the data was nice and neat in readable columns, but when I clicked submit it changed it to what you see there.I have used your suggestions and my query is as follows:SELECT *FROM ControlTracking.dbo.ContractINNER JOIN(select distinct ControlTracking.dbo.Equipment.ControlNumber from ControlTracking.dbo.Equipment)
ControlTracking.dbo.Equipment ON ControlTracking.dbo.Contract.ControlNumber = ControlTracking.dbo.Equipment.ControlNumberWHERE RecordStatus <> 0 I get this error message: Msg 102, Level 15, State 1, Line 6
Incorrect syntax near ‘.’. It does not like the “ControlTracking.dbo.Equipment ” before ON Can you give me some help on what is supposed to go before the ON after the select distinct statement?Thanks!
“It does not like the “ControlTracking.dbo.Equipment ””
That is because that is probably an invalid string for a table alias. Denny was giving the subquery an alias, but you used a fully qualified table name instead, and that is not supported.
Ok, thanks for your help.I got it to work like this:
Select DISTINCT
calltype, controlnumber
INTO #CT_CallType
FROM ControlTracking.dbo.Equipment
WHERE CALLTYPE IS NOT NULL
AND CALLTYPE != 05
AND CALLTYPE != 06
AND CALLTYPE != 08
AND CALLTYPE != 10
SELECT
ContractType,#CT_CallType.CallType,ControlTracking.dbo.Contract.ControlNumber,(Select
distinct #CT_CallType.ControlNumber) CTCN
FROM ControlTracking.dbo.Contract
INNER JOIN
#CT_CallType ON
ControlTracking.dbo.Contract.ControlNumber = #CT_CallType.ControlNumber
WHERE RecordStatus <> 0
DROP TABLE #CT_CallType