March 2, 2011 at 6:16 pm
I have a query that has dynamically created parts. It's not that complicated but I don't understand joins well enough to solve my problem. I only want one row returned from the advertising table. I'm getting multiples for the first one. I'm posting the resolved query string and data.
SELECT TOP (100) PERCENT AD.id, AD.package_id, AD.startdate, AD.enddate, AD.fee, au.Name AS marketer, ADdesc.description, c.Name AS companyname,
uihr.LastName + ', ' + uihr.FirstName AS hrcontactname, hr.contact_id AS hrcontact_id, uitax.LastName + ', ' + uitax.FirstName AS taxcontactname,
tax.contact_id AS taxcontact_id, uimkt.LastName + ', ' + uimkt.FirstName AS mktcontactname, mkt.contact_id AS mktcontact_id,
uiag.LastName + ', ' + uiag.FirstName AS agcontactname, ag.contact_id AS agcontact_id
FROM dbo.Advertising AS AD INNER JOIN
dbo.Admin_Users AS au ON au.adminID = AD.marketer_id LEFT OUTER JOIN
dbo.Advertising_Components AS Adc ON Adc.advertising_id = AD.id LEFT OUTER JOIN
dbo.Advertising_Descriptions AS ADdesc ON ADdesc.id = Adc.component_id LEFT OUTER JOIN
dbo.Companies AS c ON c.ID = AD.company_id LEFT OUTER JOIN
dbo.Client_Services_Contacts AS hr ON hr.contact_id = 27020 AND hr.ContactTypeID = 11 AND hr.service_id = AD.id LEFT OUTER JOIN
dbo.Client_Services_Contact_Types AS hrtypes ON hrtypes.id = hr.ContactTypeID LEFT OUTER JOIN
dbo.Users_Info AS uihr ON uihr.ID = hr.contact_id LEFT OUTER JOIN
dbo.Client_Services_Contacts AS tax ON tax.contact_id = 27020 AND tax.ContactTypeID = 12 AND tax.service_id = AD.id LEFT OUTER JOIN
dbo.Client_Services_Contact_Types AS taxtypes ON taxtypes.id = tax.ContactTypeID LEFT OUTER JOIN
dbo.Users_Info AS uitax ON uitax.ID = tax.contact_id LEFT OUTER JOIN
dbo.Client_Services_Contacts AS mkt ON mkt.contact_id = 27020 AND mkt.ContactTypeID = 13 AND mkt.service_id = AD.id LEFT OUTER JOIN
dbo.Client_Services_Contact_Types AS mkttypes ON mkttypes.id = mkt.ContactTypeID LEFT OUTER JOIN
dbo.Users_Info AS uimkt ON uimkt.ID = mkt.contact_id LEFT OUTER JOIN
dbo.Client_Services_Contacts AS ag ON ag.contact_id = 27020 AND ag.ContactTypeID = 14 AND ag.service_id = AD.id LEFT OUTER JOIN
dbo.Client_Services_Contact_Types AS agtypes ON agtypes.id = ag.ContactTypeID LEFT OUTER JOIN
dbo.Users_Info AS uiag ON uiag.ID = ag.contact_id
WHERE (0 = 0) AND (hr.contact_id = 27020) OR
(0 = 0) AND (tax.contact_id = 27020) OR
(0 = 0) AND (mkt.contact_id = 27020) OR
(0 = 0) AND (ag.contact_id = 27020) OR
(0 = 0) AND (0 = 1)
ORDER BY AD.startdate DESC
[font="Arial"]
19713702011/03/010 N/ANULLApple Computer, IncorporatedLanter, Vaughn27020Lanter, Vaughn27020Lanter, Vaughn27020NULLNULL
19713702011/03/010 N/ANULLApple Computer, IncorporatedLanter, Vaughn27020Lanter, Vaughn27020Lanter, Vaughn27020NULLNULL
19713702011/03/010 N/ANULLApple Computer, IncorporatedLanter, Vaughn27020Lanter, Vaughn27020Lanter, Vaughn27020NULLNULL
19713702011/03/010 N/ANULLApple Computer, IncorporatedLanter, Vaughn27020Lanter, Vaughn27020Lanter, Vaughn27020NULLNULL
19713702011/03/010 N/ANULLApple Computer, IncorporatedLanter, Vaughn27020Lanter, Vaughn27020Lanter, Vaughn27020NULLNULL
19713702011/03/010 N/ANULLApple Computer, IncorporatedLanter, Vaughn27020Lanter, Vaughn27020Lanter, Vaughn27020NULLNULL
19713702011/03/010 N/ANULLApple Computer, IncorporatedLanter, Vaughn27020Lanter, Vaughn27020Lanter, Vaughn27020NULLNULL
19713702011/03/010 N/ANULLApple Computer, IncorporatedLanter, Vaughn27020Lanter, Vaughn27020Lanter, Vaughn27020NULLNULL
19712500 N/ANULLChevron CorporationLanter, Vaughn27020NULLNULLLanter, Vaughn27020NULLNULL
19713600 N/ANULLOracle CorporationNULLNULLNULLNULLNULLNULLLanter, Vaughn27020
[/font]
March 2, 2011 at 6:24 pm
Tracking down row multiplication in complex Outer Join queries requires access to the actual data.
What you're going to need to do is break the query down into smaller parts, and check each part to see where it starts multiplying the results. Most likely, there will be an incomplete join condition between two tables, and you'll have to find it that way.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply