SQL JOINS

Question

Post reply

SQL JOINS

Vinay Theethira

Right there with Babe

Points: 775
More actions
June 23, 2010 at 11:44 pm

#224230

Hi All,
Here is my SQL Query, This query should not fetch duplicate contactID(i.e Same ContactID).but when i execute the query i get duplicated ContactID.I know it is a problem with Joins. Could anyone tell me what is wrong with my Query,
Here is my query
select ContactTracking.Contact_ID
FROM
ContactTracking ContactTracking
LEFT OUTER JOIN Issues Issues ON ContactTracking.Issue_ID = Issues.Issue_ID
LEFT OUTER JOIN HRA hra ON hra.HcnID = ContactTracking.HcnID
INNER JOIN HPS_COMMON.dbo.wrk_user wrk_User ON ContactTracking.UserID = wrk_User.id_User
INNER JOIN HPS_COMMON.dbo.def_Usertype def_Usertype ON wrk_User.id_UserType = def_Usertype.id_UserType
WHERE
ContactTracking.contact_date>'10-jan-2009'
AND ContactTracking.Call_end_time is not null
AND Contact_ID in ('122734','122738')
And here is the ContactId result i get,
122734
122734
122738
122738

Viewing 7 posts - 1 through 6 (of 6 total)

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

vijay.s SSCrazy Points: 2161 More actions · Answer 1

Vinay,

Add some sample data and expected result.

So it will help u help us

NewBeeSQL SSCrazy Points: 2170 More actions · Answer 2

Post DDL, DML statements in readily consumable format for faster response...

-----------------------------------------------------------------------
For better assistance in answering your questions[/url]
Perforamance Issues[/url]
Cross Tabs and Pivots[/url]
Cross Apply[/url]
The Numbers or Tally Table- Jeff Moden[/url]

Vinay Theethira Right there with Babe Points: 775 More actions · Answer 3

Some sample datas are,

122731

122732

122733

122734

122735

122736

122737

122738

122739

According to the Query the Expected result is,

122734

122738

But i am getting as,

122734

122738

shield_21 Ten Centuries Points: 1033 More actions · Answer 4

Hi there,

There's nothing wrong with your query.. Use DISTINCT keyword to eliminate the duplicate values..

Syntax here:

SELECT DISTINCT <column 1>, ... <column N>

FROM <table>

Just a side note, alias name is mostly used to shorten the name of the table.. 🙂

Cheers

vijay.s SSCrazy Points: 2161 More actions · Answer 5

vijay.s

SSCrazy

Points: 2161

June 24, 2010 at 3:36 am

#1184392

Or Use group by claue for your id

OTF SSCertifiable Points: 6104 More actions · Answer 6

If your sole aim is to return Distinct ContactId's then introducing a Distinct or Group By clause should

fix it but you may want to go over what it is you are actually trying to achieve. The sample data you have provided is insufficient for us to determine this.

Bear in mind that if records in the ContactTracking Table have many matches in the joined Tables then the result would include duplication of the ContactIds.