Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Joins?


Joins?

Author
Message
Wayne Coles
Wayne Coles
Grasshopper
Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)

Group: General Forum Members
Points: 17 Visits: 76
I am struggling to understand joins and hope somebody can help me

I have three tables that I am querying, tables one and two contain fixed customer information where you cannot have an entry for a customer in table1 without it creating a record in table2 and the relationship is always one to one linked by custid which I am comfortable with.

Table3 lists orders placed by the customers, where a customer might have placed multiple orders for a particular product or not ordered at all. I am looking to list all customers in a certain location regardless of whether they have ordered or not with any that have placed multiple orders having multiple entries.

I have written the below query but it does not return the customers in tables1&2 who have not placed an order. I cannot request NULLs as this is for a specific product and table3 contains other products they have purchased.

I am using SQL Server 2008 which we have recently upgraded to

After many unsuccessful attempts, I reverted to writing it as I would in SQL2000, (second query) and that worked returning all customers including those that had not ordered. So I decided to use “create view” with this code and see what happened.

SQL created the view and corrected the syntax to joins, exactly the same as I had written but it no longer includes the customers who had not ordered.

Can anybody help me with what I am doing wrong and what I need to add to include all customers?

Thanks

Wayne

SELECT TOP (100) PERCENT 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
WHERE (a1.CITY = 'THIS') AND (a2.STATUS = 'THAT') AND (a3.ORDPROD LIKE 'OTHER%')


SELECT TOP (100) PERCENT a1.custid, a1.COMPANY AS [Co Name],
a2.STATUS AS [TYPE],
a3.ORDPROD AS [Product]
FROM TABLE1 a1, TABLE2 a2, TABLE3 a3
where a1.CUSTID = a2.CUSTID and a1.CUSTID *= a3.CUSTID
and (a1.CITY = 'THIS') AND (a2.STATUS = 'THAT') AND (a3.ORDPROD LIKE 'OTHER%')
Fatherjack
Fatherjack
SSC Veteran
SSC Veteran (290 reputation)SSC Veteran (290 reputation)SSC Veteran (290 reputation)SSC Veteran (290 reputation)SSC Veteran (290 reputation)SSC Veteran (290 reputation)SSC Veteran (290 reputation)SSC Veteran (290 reputation)

Group: General Forum Members
Points: 290 Visits: 627
[Answer removed. I need to read the question better next time - the other answers have the detail I missed!]
Andy Hyslop
Andy Hyslop
Say Hey Kid
Say Hey Kid (706 reputation)Say Hey Kid (706 reputation)Say Hey Kid (706 reputation)Say Hey Kid (706 reputation)Say Hey Kid (706 reputation)Say Hey Kid (706 reputation)Say Hey Kid (706 reputation)Say Hey Kid (706 reputation)

Group: General Forum Members
Points: 706 Visits: 3023
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
Cadavre
SSCrazy
SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)

Group: General Forum Members
Points: 2598 Visits: 8437
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

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/



If you litter your database queries with nolock query hints, are you aware of the side effects?
Try reading a few of these links...

(*) Missing rows with nolock
(*) Allocation order scans with nolock
(*) Consistency issues with nolock
(*) Transient Corruption Errors in SQL Server error log caused by nolock
(*) Dirty reads, read errors, reading rows twice and missing rows with nolock


Craig Wilkinson - Software Engineer
LinkedIn
Wayne Coles
Wayne Coles
Grasshopper
Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)

Group: General Forum Members
Points: 17 Visits: 76
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search