Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Joins? Expand / Collapse
Author
Message
Posted Wednesday, October 24, 2012 8:50 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, November 19, 2012 3:51 AM
Points: 16, Visits: 61
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%')

Post #1376525
Posted Wednesday, October 24, 2012 8:58 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Yesterday @ 2:11 AM
Points: 279, Visits: 575
[Answer removed. I need to read the question better next time - the other answers have the detail I missed!]
Post #1376532
Posted Wednesday, October 24, 2012 9:00 AM


Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Friday, October 10, 2014 1:07 AM
Points: 692, Visits: 2,803
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
Post #1376533
Posted Wednesday, October 24, 2012 9:01 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 5:08 AM
Points: 2,379, Visits: 7,583
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



Not a DBA, just 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


LinkedIn | Blog coming soon (for sufficiently large values of "soon" )!
Post #1376534
Posted Wednesday, October 24, 2012 9:16 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, November 19, 2012 3:51 AM
Points: 16, Visits: 61
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







Post #1376543
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse