Joining unmatched tables with duplicates

25 pts.
Tags:
SQL Server 2005
SQL Server tables
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

Answer Wiki

Thanks. We'll let you know when a new response is added.

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

Discuss This Question: 4  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • Michael Tidmarsh
    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.
    33,105 pointsBadges:
    report
  • jruth13
    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!  
    25 pointsBadges:
    report
  • carlosdl
    "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.
    70,190 pointsBadges:
    report
  • jruth13
    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
    25 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following