full outer join?? - 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

full outer join??

Author

Topic

bismarkcount
Starting Member

15 Posts

Posted - 2007-11-05 : 16:55:26

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-11-05 : 16:59:12

what have you tried so far? post your scripts and any error msgs..

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

Van
Constraint Violating Yak Guru

462 Posts

Posted - 2007-11-05 : 17:03:45

Just out of curiosity which particular CIS class is this for?

bismarkcount
Starting Member

15 Posts

Posted - 2007-11-05 : 17:08:10

cant figure out how to use the full outer join with both tables and a calculation.
but i think that i can also use something like:

select scanner_input.id, scanner_input.quantity, sap_input.id, sap_input.quantity,
select(scanner_input.quantity - sap_input.quantity)
from scanner_input, sap_input

??

my target query is kinda more complex, but i simplified the concept to make it easier and faster to read for the bloggers. but that is the general idea.

any suggestions??

thnx

bismarkcount
Starting Member

15 Posts

Posted - 2007-11-05 : 17:10:31

this is not for school. this is for my work. to check if the scanned items in the shipment fit the SAP output quantity

Van
Constraint Violating Yak Guru

462 Posts

Posted - 2007-11-05 : 17:14:15

Something like this:

select sci.id, sci.quantity, sai.id, sai.quantity, sci.quantity - sai.quantity as 'Difference'
from scanner_input sci join sap_input sai on sci.id = sai.id

bismarkcount
Starting Member

15 Posts

Posted - 2007-11-05 : 17:27:49

and how could i put a condition on that query?
for example:
where sap_input.id = xxx

? thnx in advance

Van
Constraint Violating Yak Guru

462 Posts

Posted - 2007-11-05 : 17:29:02

Put this at the bottom

where sai.id = xxx

select sci.id, sci.quantity, sai.id, sai.quantity, sci.quantity - sai.quantity as 'Difference'
from scanner_input sci join sap_input sai on sci.id = sai.id
where sai.id = xxx

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-11-06 : 02:29:29

[code]DECLARE @Scanner TABLE (ID VARCHAR(5), Quantity INT, [Status] VARCHAR(2))

INSERT @Scanner
SELECT 'prod1', 6, 'OK' UNION ALL
SELECT 'prod2', 7, 'OK' UNION ALL
SELECT 'prod3', 3, 'P' UNION ALL
SELECT 'prod4', 3, 'OK' UNION ALL
SELECT 'prod5', 3, 'OK' UNION ALL
SELECT 'prod7', 3, 'OK'

DECLARE @Sap TABLE (ID VARCHAR(5), Quantity INT, [Status] VARCHAR(2))

INSERT @Sap
SELECT 'prod1', 6, 'OK' UNION ALL
SELECT 'prod2', 7, 'OK' UNION ALL
SELECT 'prod3', 3, 'P' UNION ALL
SELECT 'prod4', 2, 'OK' UNION ALL
SELECT 'prod6', 3, 'OK' UNION ALL
SELECT 'prod8', 3, 'OK'

SELECT ID AS [id(scanner)],
SUM(Scanner) AS Scanner,
SUM(Sap) AS Sap,
SUM(Scanner) - SUM(Sap) AS [Difference]
FROM (
SELECT ID,
Quantity AS Scanner,
0 AS Sap
FROM @Scanner

UNION ALL

SELECT ID,
0,
Quantity
FROM @Sap
) AS d
GROUP BY ID[/code]

E 12°55'05.25"
N 56°04'39.16"

Van
Constraint Violating Yak Guru

462 Posts

Posted - 2007-11-06 : 10:55:05

I don't see where the OP is asking about summing things or getting any totals. I only see where the OP is asking to put columns from 2 tables side by side where they match on ID and then take the difference between 2 columns (qty).

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-11-06 : 11:02:32

SUM is my trick to get UNION ALL to work.
Zero plus a value returns the value. Nothing more. Look closer to the code and you will see how it works.
Also, using sum make the code somewhat "future safe", when adding more complexity to the tables.

E 12°55'05.25"
N 56°04'39.16"

Van
Constraint Violating Yak Guru

462 Posts

Posted - 2007-11-06 : 11:27:04

I see.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-11-06 : 11:27:30

quote:
Originally posted by Van

I only see where the OP is asking to put columns from 2 tables side by side where they match on ID and then take the difference between 2 columns (qty).

And you forgot the FULL JOIN functionality

select		coalesce(sci.id, sai.id),
		coalesce(sci.quantity, 0),
		coalesce(sai.id, sci.id),
		coalesce(sai.quantity, 0),
		coalesce(sci.quantity, 0) - coalesce(sai.quantity, 0) as 'Difference'
from		scanner_input as sci
full join	sap_input as sai on sai.id = sci.id

E 12°55'05.25"
N 56°04'39.16"

Van
Constraint Violating Yak Guru

462 Posts

Posted - 2007-11-06 : 11:28:54

Yep, that I did. But I was just trying to give the OP something to go on really.

bismarkcount
Starting Member

15 Posts

Posted - 2007-11-06 : 11:48:35

yep, full outer join worked fine. thnx a lot van and peso

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-11-06 : 12:06:06