Inner/Outer Join Help Revised - 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

Inner/Outer Join Help Revised

Author

Topic

DavidChel
Constraint Violating Yak Guru

474 Posts

Posted - 2008-05-16 : 10:28:25

I need to compare our late sales orders to our late purchase orders. The query for the sales orders would be this:

SELECT S1.*,
S2.*,
S3.*
FROM somast S1
INNER JOIN soitem S2
ON S1.fsono = S2.fsono
INNER JOIN sorels S3
ON S2.fsono + S2.finumber = S3.fsono + S3.finumber

The PO table query is like this:

SELECT P1.*,
P2.*
FROM pomast P1
INNER JOIN poitem P2
ON P1.fpono = P2.fpono
WHERE NOT(P2.fmultirls = 'Y')
AND P2.frelsno = 0

somast is the sales order master table, soitem is the items table, and sorels is the release. pomast is the Purchase Order Master and poitem is the purchase order items table. I don't intend to leave the *'s and will clean up the query afterwards with the specific fields I need.

The poitem table as a field called fsokey which contains the sales order number if the PO and the SO are related.

So, here is my question. I want all the sales orders and only the purchase order information pulled for which the fsokey corresponds. Would I do this with a subquery or is there some very complex (for me) inner and outer join query that I would use.

I greatly appreciate some help with this.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-16 : 10:32:33

[code]SELECT *
FROM
(
SELECT S1.*,
S2.*,
S3.*
FROM somast S1
INNER JOIN soitem S2
ON S1.fsono = S2.fsono
INNER JOIN sorels S3
ON S2.fsono + S2.finumber = S3.fsono + S3.finumber)t1
LEFT JOIN
(
SELECT P1.*,
P2.*
FROM pomast P1
INNER JOIN poitem P2
ON P1.fpono = P2.fpono
WHERE NOT(P2.fmultirls = 'Y')
AND P2.frelsno = 0)t2
ON t2.fsokey=t1.salesordernumberfieldhere[/code]

This will give the required output. i dont know whats the field name that contains sales order number. substitute it in last line

DavidChel
Constraint Violating Yak Guru

474 Posts

Posted - 2008-05-16 : 14:23:54

Thanks so much for your help visakh16. One more thing however. When I run this:

SELECT *
FROM
(
SELECT S1.*,
S2.*,
S3.*
FROM somast S1
INNER JOIN soitem S2
ON S1.fsono = S2.fsono
INNER JOIN sorels S3
ON S2.fsono + S2.finumber = S3.fsono + S3.finumber)t1
LEFT JOIN
(
SELECT P1.*,
P2.*
FROM pomast P1
INNER JOIN poitem P2
ON P1.fpono = P2.fpono
WHERE NOT(P2.fmultirls = 'Y')
AND P2.frelsno = 0)t2
ON t2.fsokey=t1.fsono

I get the following error:
Server: Msg 8156, Level 16, State 1, Line 1
The column 'fsono' was specified multiple times for 't1'.

This is because the field fsono is found in all three of those sales order tables. I'd like to pull the S2.fsono.

When I try that, I get:

Server: Msg 107, Level 16, State 2, Line 1
The column prefix 'S2' does not match with a table name or alias name used in the query.

When I try T1.S2.fsono I get:

Server: Msg 107, Level 16, State 2, Line 1
The column prefix 'T1S2' does not match with a table name or alias name used in the query.

How can I resolve this?

Thanks again for your help.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-16 : 14:28:10

quote:
Originally posted by DavidChel

Thanks so much for your help visakh16. One more thing however. When I run this:

SELECT *
FROM
(
SELECT S1.*,
S2.*,
S3.*
FROM somast S1
INNER JOIN soitem S2
ON S1.fsono = S2.fsono
INNER JOIN sorels S3
ON S2.fsono + S2.finumber = S3.fsono + S3.finumber)t1
LEFT JOIN
(
SELECT P1.*,
P2.*
FROM pomast P1
INNER JOIN poitem P2
ON P1.fpono = P2.fpono
WHERE NOT(P2.fmultirls = 'Y')
AND P2.frelsno = 0)t2
ON t2.fsokey=t1.fsono

I get the following error:
Server: Msg 8156, Level 16, State 1, Line 1
The column 'fsono' was specified multiple times for 't1'.

This is because the field fsono is found in all three of those sales order tables. I'd like to pull the S2.fsono.

When I try that, I get:

Server: Msg 107, Level 16, State 2, Line 1
The column prefix 'S2' does not match with a table name or alias name used in the query.

When I try T1.S2.fsono I get:

Server: Msg 107, Level 16, State 2, Line 1
The column prefix 'T1S2' does not match with a table name or alias name used in the query.

How can I resolve this?

Thanks again for your help.

replace *'s with whatever columns you want to retrieve.
And if you want same column names from more than one table remember to provide an alias in inner query like if you want fsono from S1 and S2 make aliases on them
S1.fsono AS s1fsono,
s2.fsono as s2fsono,
....

and use this in outer query t1.s1fsono,t1.s2fsono,..

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources