Joins?

Question

Post reply

Joins?

Viewing 4 posts - 1 through 5 (of 5 total)

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

Fatherjack SSCommitted Points: 1575 More actions · Answer 1

[Answer removed. I need to read the question better next time - the other answers have the detail I missed!]

Andy Hyslop SSCrazy Eights Points: 9198 More actions · Answer 2

Hi

This should help

SELECT a1.CUSTID, a1.COMPANY AS [Co Name], a2.STATUS AS [TYPE], a3.ORDPROD AS [Product]

FROM dbo.TABLE1 AS a1

INNER JOIN dbo.TABLE2 AS a2 ON a1.CUSTID = a2.CUSTID

LEFT OUTER JOIN dbo.TABLE3 AS a3 ON a1.CUSTID = a3.CUSTID AND (a3.ORDPROD LIKE 'OTHER%')

WHERE

(a1.CITY = 'THIS')

AND (a2.STATUS = 'THAT')

You don't need the TOP 100 PERCENT for this

You had the JOINS right however you WHERE clause turned the LEFT JOIN to an INNER join

Hope this helps

Andy

==========================================================================================================================
A computer lets you make more mistakes faster than any invention in human history - with the possible exceptions of handguns and tequila. Mitch Ratcliffe

Cadavre SSC-Forever Points: 41690 More actions · Answer 3

This: -

AND (a3.ORDPROD LIKE 'OTHER%')

breaks your outer join and turns it into an inner join.

Try this: -

SELECT a1.CUSTID, a1.COMPANY AS [Co Name], a2.STATUS AS [TYPE], a3.ORDPROD AS [Product]

FROM dbo.TABLE1 a1

INNER JOIN dbo.TABLE2 a2 ON a1.CUSTID = a2.CUSTID

LEFT OUTER JOIN (SELECT CUSTID, ORDPROD

FROM dbo.TABLE3

WHERE ORDPROD LIKE 'OTHER%') a3 ON a1.CUSTID = a3.CUSTID

WHERE a1.CITY = 'THIS' AND a2.STATUS = 'THAT';

or this: -

SELECT a1.CUSTID, a1.COMPANY AS [Co Name], a2.STATUS AS [TYPE], a3.ORDPROD AS [Product]

FROM dbo.TABLE1 a1

INNER JOIN dbo.TABLE2 a2 ON a1.CUSTID = a2.CUSTID

LEFT OUTER JOIN dbo.TABLE3 a3 ON a1.CUSTID = a3.CUSTID AND a3.ORDPROD LIKE 'OTHER%'

WHERE a1.CITY = 'THIS' AND a2.STATUS = 'THAT'

--EDIT--

ah, too slow :hehe:

Forever trying to learn
My blog - http://www.cadavre.co.uk/
For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Wayne Coles SSC Enthusiast Points: 131 More actions · Answer 4

Cheers guys

That worked a treat.

I've been coming back to this since yesterday. I wanted to work it out myself, but I think I could have been there another month and still not considered it went before the "Where"

Seems I have some new learning to do

I was a bit disappointed that when I ran it through to create a view, it was not corrected correctly at that point

still, can't have everything I s'pose 😉

thanks again

Wayne