Joins within joins - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

Joins within joins

Author

Topic

rcp
Starting Member

32 Posts

Posted - 2012-01-18 : 09:47:49

Hi I am stuck, I am trying to get the unmatched from one table then get the unmatched from an other table with certain criteria. This is the query so far

WITH
ORDERTABLE
AS
(SELECT order_id FROM CUSTORDERS
WHERE CONVERT(date,created_date,101) = '2011-12-05'
and [status] IN ('paid','preauth','refund'))
SELECT ORDERTABLE.*
FROM ORDERTABLE

LEFT Join
(
WITH
T1Counts
AS
(
SELECT * FROM [orp].[dbo].[SAMPLE]
WHERE CONVERT(date,created_date,101) = '2011-12-05'
),
T2Counts
AS
(
SELECT * FROM [orp].[dbo].[SAMPLE]
WHERE CONVERT(date,created_date,101) = '2011-12-05'
and paymentstatus like 'completed'
)
SELECT distinct(T1.order_id) AS FailuresSAMPLE
FROM T1Counts T1
LEFT JOIN T2Counts T2
ON T1.order_id=T2.order_id
WHERE T2.order_id is null
) F1

on ORDERTABLE.order_id=F1.FailuresSAMPLE
WHERE FailuresSAMPLE is null

Regards,

Roland

X002548
Not Just a Number

15586 Posts

Posted - 2012-01-18 : 09:54:36

SELECT a.*, b.*, c.*
FROM myTable1 a
LEFT JOIN myTable2 b
ON a.key = b.key
LEFT JOIN myTable3 c
ON a.key = c.key
WHERE b.key IS NULL OR c.key IS NULL

????

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2012-01-18 : 09:57:14

your CTE placement is messed up. This is what I think you were trying to write (note that you must declare all the CTE's up front before using them).



;WITH ORDERTABLE AS (
	SELECT order_id
	FROM CUSTORDERS
	WHERE
		CONVERT(date,created_date,101) = '2011-12-05'
		and [status] IN ('paid','preauth','refund')
	)
,T1Counts AS (
	SELECT * FROM [orp].[dbo].[SAMPLE]
	WHERE CONVERT(date,created_date,101) = '2011-12-05'
	)
,T2Counts AS (
	SELECT * FROM [orp].[dbo].[SAMPLE]
	WHERE
		CONVERT(date,created_date,101) = '2011-12-05'
		and paymentstatus like 'completed'
	)
SELECT
	ORDERTABLE.*
FROM
	ORDERTABLE
	LEFT Join  (
		SELECT distinct(T1.order_id) AS FailuresSAMPLE
		FROM
			T1Counts T1
			LEFT JOIN T2Counts T2 ON T1.order_id=T2.order_id
		WHERE
			T2.order_id is null
		)
		AS F1 on ORDERTABLE.order_id=F1.FailuresSAMPLE
WHERE
	FailuresSAMPLE is null

However, there are *many* things suspect about this code.

1) Don't do stuff like this:

quote:
CONVERT(date,created_date,101) = '2011-12-05'

By wrapping the column in a function it is no longer sargeable (you can't use any index on the column). I guess you must be using some weird type for dates. either change the column to a date type or do the manipulation on the variable ('2011-12-05') to put it into a format directly comparable to the column.

2) This:


LEFT Join  (
		SELECT distinct(T1.order_id) AS FailuresSAMPLE
		FROM
			T1Counts T1
			LEFT JOIN T2Counts T2 ON T1.order_id=T2.order_id
		WHERE
			T2.order_id is null
		)
		AS F1 on ORDERTABLE.order_id=F1.FailuresSAMPLE
WHERE
	FailuresSAMPLE is null

What you are doing here is checking that something DOES NOT exists for a given predicate. This would be much better written as a NOT EXISTS clause. However, I can't be bothered to rewrite it. Doing the check they way you are is quite expensive compared to a NOT EXISTS alternative. Also it reads better because you are declaratively stating what you want.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

rcp
Starting Member

32 Posts

Posted - 2012-01-18 : 11:02:27

X002548, thanks for this I have written a query using your example. Charlie, I will try rewriting the query to use NOT EXIST.

Thanks for you help,

Roland

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2012-01-18 : 11:09:03

My first point was actually more important.

Don't manipulate columns in the where clause (or a join condition)

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

X002548
Not Just a Number

15586 Posts

Posted - 2012-01-18 : 11:31:48

post what you have now

I believe if you add a where clause it will turn it into an INNER JOIN

Can you post the real table names for tables 1,2,3?

And what the criteria is suppose to be for each?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

rcp
Starting Member

32 Posts

Posted - 2012-01-18 : 12:31:36

WITH
T1Counts
AS
(
SELECT * FROM [orp].[dbo].[paypal_log]
WHERE CONVERT(date,created_date,101) = '2011-12-05'
),
T2Counts
AS
(
SELECT * FROM [orp].[dbo].[paypal_log]
WHERE CONVERT(date,created_date,101) = '2011-12-05'
and paymentstatus like 'completed'
)
SELECT distinct(T1.order_id) AS FailuresPayPal
FROM T1Counts T1
LEFT JOIN T2Counts T2
ON T1.order_id=T2.order_id
WHERE T2.order_id is null

Then the results from this need to match onto a table called customer_order with criteria where [status] IN ('Paid','preAuth') and again return the NOT EXISTS values.

I hope this help.

Regards,

Roland

X002548
Not Just a Number

15586 Posts

Posted - 2012-01-18 : 12:38:56

[code]

SELECT a.*, b.*, c.*
FROM customer_order a
LEFT JOIN (SELECT * FROM [orp].[dbo].[paypal_log]
WHERE CONVERT(date,created_date,101) = '2011-12-05') AS b
ON a.Order_Id = b.Order_ID
LEFT JOIN (SELECT * FROM [orp].[dbo].[paypal_log]
WHERE CONVERT(date,created_date,101) = '2011-12-05'
AND paymentstatus like 'completed') AS c
ON a.Order_Id = c.Order_Id
WHERE (b.Order_Id IS NULL OR c.Order_IdIS NULL)
AND a.[status] IN ('Paid','preAuth')

[/code]

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

X002548
Not Just a Number

15586 Posts

Posted - 2012-01-18 : 12:39:48

It would be easier with the DDL, Sample Data and expect results

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

rcp
Starting Member

32 Posts

Posted - 2012-01-18 : 12:52:08

Hi Brett, thanks agian for the response. In the end I would like to do counts on the number of orders that failed our security.

WITH
T1Counts
AS
(
SELECT
distinct order_id
FROM [orp].[dbo].[paypal_log]
WHERE CONVERT(date,created_date,101) = '2011-12-05'
and (paymentstatus <> 'completed' OR paymentstatus is null)
),
T2Counts
AS
(
SELECT
distinct order_id
FROM [orp].[dbo].[paypal_log]
WHERE CONVERT(date,created_date,101) = '2011-12-05'
and paymentstatus like 'completed'
)
SELECT T1.order_id
FROM T1Counts T1
LEFT JOIN T2Counts T2
ON T1.order_id=T2.order_id
WHERE T2.order_id is null

This tells me all orders that went through paypal and failed. They can then try a different payment method of payment. So if they get a [status] in customer_orders that is ('Paid','preAuth') with the same order_id then they have managed to make a payment. Everyone else left overe in the above query is a true fail.

All I am really after is numbers of failures not really detail, but am using the deatil to do checks first.

Regards,

Roland

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources