WHERE condition on JOIN - nothing joined onto

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

WHERE condition on JOIN - nothing joined onto

Author

Topic

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2009-09-30 : 12:45:39

Hi,

I have the following query, which is not functioning as desired.
Basically what happens is that not every "ORDER" has a corresponding "ATTACHMENT" so when this happens I think that my condition "OA.approvedByAdmin = 1 " causes the query to return 0.

When the orders have attachments the count is correct, when there is no attachment it is counted as 0.

How can I modify my query so that its correct?

Thanks very much!
mike123

SELECT count(distinct(o.orderID)) as Pending_Orders FROM tblOrders O
JOIN tblordereditems OI on OI.orderID = O.orderID
JOIN tblorder_attachments OA on OA.orderID = OI.orderID
WHERE O.approvedByAdmin = 1 AND OA.approvedByAdmin = 1 and OI.approvedByAdmin = 1
AND itemID NOT IN (SELECT itemID FROM tblItemAssignments)

Ifor
Aged Yak Warrior

700 Posts

Posted - 2009-09-30 : 13:14:09

With 1415 posts why are you not posting DDL and test data?

You may want something like the following:


SELECT COUNT(O.orderID) AS Pending_Orders
FROM tblOrders O
WHERE O.approvedByAdmin = 1
	AND EXISTS
	(
		SELECT *
		FROM tblordereditems OI
		WHERE OI.orderID = O.orderID
			AND OI.approvedByAdmin = 1
			AND
			(
				EXISTS
				(
					SELECT *
					FROM tblorder_attachments OA1
					WHERE OA1.orderID = OI.orderID
						AND OA1.approvedByAdmin = 1
				)
				OR NOT EXISTS
				(
					SELECT *
					FROM tblorder_attachments OA2
					WHERE OA2.orderID = OI.orderID
				)
			)
	)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-10-01 : 05:53:31

quote:
Originally posted by mike123

Hi,

I have the following query, which is not functioning as desired.
Basically what happens is that not every "ORDER" has a corresponding "ATTACHMENT" so when this happens I think that my condition "OA.approvedByAdmin = 1 " causes the query to return 0.

When the orders have attachments the count is correct, when there is no attachment it is counted as 0.

How can I modify my query so that its correct?

Thanks very much!
mike123

SELECT count(distinct(o.orderID)) as Pending_Orders FROM tblOrders O
JOIN tblordereditems OI on OI.orderID = O.orderID
LEFT JOIN tblorder_attachments OA on OA.orderID = OI.orderID AND OA.approvedByAdmin = 1
WHERE O.approvedByAdmin = 1 and OI.approvedByAdmin = 1
AND itemID NOT IN (SELECT itemID FROM tblItemAssignments)

modify like above and check

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2009-10-01 : 06:26:19

Hey Ifor,

Thanks for that but I think visakh16 is on the right track. Sorry for not posting data Im sure it would have been easier to understand :( Appreciate the input.

Visakh16, this fixes my problem, but it however creates a new one

Right now when I don't have a row in tblOrderAttachements, I get a count of 1, which is the problem I had before fixed.

The problem is, when I have a row in tblOrderAttachments, with a value 0 for approvedByAdmin, I still get a count of 1 returned. It should be 0 as the WHERE clause "WHERE approvedByAdmin = 1" is not met.

Thanks!
Mike

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-10-01 : 07:11:55

Now it is about time to give samples and wanted results!

No, you're never too old to Yak'n'Roll if you're too young to die.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-10-01 : 08:49:54

[code]SELECT COUNT(DISTINCT oa.OrderID) AS Pending_Orders
FROM tblOrders AS o
INNER JOIN tblOrderedItems AS oi ON oi.OrderID = o.OrderID
AND oi.ApprovedByAdmin = 1
LEFT JOIN tblOrderAttachments AS oa ON oa.OrderID = oi.OrderID
AND oa.ApprovedByAdmin = 1
WHERE o.ApprovedByAdmin = 1
AND NOT EXISTS (SELECT * FROM tblItemAssignments AS b WHERE b.ItemID = o.ItemID)[/code]

N 56°04'39.26"
E 12°55'05.63"

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2009-10-01 : 08:58:26

Hey Guys,

The following should query should not bring back a value of "1" because the "approvedByAdmin" column in tblOrderAttachements has not been set to "1"

Any help much appreciated!

Thanks again,
mike123

Query:

SELECT count(distinct(o.orderID)) as Pending_Orders FROM tblOrders O
JOIN tblordereditems OI on OI.orderID = O.orderID
JOIN tblorder_attachments OA on OA.orderID = OI.orderID
WHERE O.approvedByAdmin = 1 AND OA.approvedByAdmin = 1 and OI.approvedByAdmin = 1
AND itemID NOT IN (SELECT itemID FROM tblItemAssignments)

DATA

INSERT into tblOrders (clientID,orderCode,approvedByAdmin, approvedByAdmin_Date,orderDate) Values (36,'vn152t',1,getDate(),getDate())
INSERT into tblOrderedItems (orderID,itemCode,approvedByAdmin, approvedByAdmin_Date) Values (10,'xy024g',1,getDate())
INSERT into tblorder_attachments (attachmentCode,orderID,commentsInstructions, approvedByAdmin, approvedByAdmin_Date) Values (11,'H2Z6W6',10,'',0,NULL)

Tables:

CREATE TABLE [dbo].[tblOrderedItems](
[itemID] [int] IDENTITY(1,1) NOT NULL,
[orderID] [int] NOT NULL,
[itemCode] [varchar](6) NOT NULL,
[approvedByAdmin] [tinyint] NOT NULL,
[approvedByAdmin_Date] [datetime] NULL
) ON [PRIMARY]

CREATE TABLE [dbo].[tblOrders](
[orderID] [int] IDENTITY(1,1) NOT NULL,
[clientID] [int] NOT NULL,
[orderCode] [varchar](6) NOT NULL,
[approvedByAdmin] [tinyint] NOT NULL,
[approvedByAdmin_Date] [datetime] NULL,
[orderDate] [datetime] NOT NULL
) ON [PRIMARY]

CREATE TABLE [dbo].[tblOrder_Attachments](
[attachmentID] [int] IDENTITY(1,1) NOT NULL,
[attachmentCode] [varchar](6) NOT NULL,
[orderID] [int] NOT NULL,
[commentsInstructions] [varchar](8000) NOT NULL,
[approvedByAdmin] [tinyint] NOT NULL,
[approvedByAdmin_Date] [datetime] NULL
) ON [PRIMARY]

GO

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-10-01 : 15:19:31

quote:
Originally posted by mike123

Hey Ifor,

Thanks for that but I think visakh16 is on the right track. Sorry for not posting data Im sure it would have been easier to understand :( Appreciate the input.

Visakh16, this fixes my problem, but it however creates a new one

Right now when I don't have a row in tblOrderAttachements, I get a count of 1, which is the problem I had before fixed.

The problem is, when I have a row in tblOrderAttachments, with a value 0 for approvedByAdmin, I still get a count of 1 returned. It should be 0 as the WHERE clause "WHERE approvedByAdmin = 1" is not met.

Thanks!
Mike

this may be:-


SELECT count(distinct(o.orderID)) as Pending_Orders FROM tblOrders O 
JOIN tblordereditems OI on OI.orderID = O.orderID 
LEFT JOIN tblorder_attachments OA on OA.orderID = OI.orderID 
WHERE O.approvedByAdmin = 1 and OI.approvedByAdmin = 1
AND (OA.approvedByAdmin = 1  OR  OA.approvedByAdmin  IS NULL)
AND itemID NOT IN (SELECT itemID FROM tblItemAssignments)

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2009-10-02 : 05:54:46

Hey Visakh16,

That's exactly what I was looking for.

Thanks once again! :)
mike123

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-10-02 : 07:24:51

welcome

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources