Need help joining 3 tables - 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

Need help joining 3 tables

Author

Topic

sjackson330
Starting Member

4 Posts

Posted - 2015-01-15 : 12:42:42

I have 3 tables that I'm trying to joing together in an SQL statement. I've tried several different JOIN statements, but can't seem to get it to work correctly.

Scenario:
I want all records from TABLE_1
I want to LEFT JOIN TABLE_1 to TABLE_2, but I only want records from TABLE_2 that are an 'Active' STATUS. My problem is, the STATUS field is in TABLE_3.

I have to link TABLE_1 to TABLE_2 by ITEMNUM.
I have to link TABLE_2 to TABLE_3 by ORDERNUM since TABLE_3 does not have ITEMNUM, and pull across the STATUS.

To help understand a little bit. TABLE_1 is an ITEMID table. TABLE_2 is our ORDER_LINE table, and TABLE_3 is our ORDER_HEADER info. I want all items listed; then I'd like to see all items that are on an order (TABLE_2), but only if the ORDER_HEADER (TABLE_3) is an 'ACTIVE' STATUS.

I would be very grateful for any help on this problem.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2015-01-15 : 12:52:27

Show us what you have so far, and we'll help to fix it.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

sjackson330
Starting Member

4 Posts

Posted - 2015-01-15 : 14:16:07

This was one of my many failed attempts... {WPITEM is TABLE_1; PRLINE is TABLE_2; PR is TABLE_3:

select WPITEM.WONUM, WPITEM.SC_MATLCONDITION, WPITEM.ITEMNUM, PRLINE.ITEMNUM, PRLINE.PRNUM, PR.STATUS

FROM
(
(
MAXIMO.PRLINE PRLINE
LEFT JOIN MAXIMO.PR PR ON PRLINE.PRNUM = PR.PRNUM AND PR.STATUS IN ('APPR', 'WAPPR')
)
RIGHT JOIN MAXIMO.WPITEM WPITEM
ON WPITEM.ITEMNUM = PRLINE.ITEMNUM
)

where
WPITEM.WONUM = 'SNC617342'

***I've tried RIGHT JOINS and LEFT JOINS. I've also tried embedding the tables differntly, with no luck.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2015-01-15 : 14:20:24

Your query doesn't match the table names or column names of your original post, so I can't follow it.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2015-01-15 : 14:23:00

I am not sure if this is what you want, but here goes:


SELECT WPITEM.WONUM, WPITEM.SC_MATLCONDITION, WPITEM.ITEMNUM, PRLINE.ITEMNUM, PRLINE.PRNUM, PR.STATUS
FROM MAXIMO.PRLINE PRLINE
LEFT JOIN MAXIMO.WPITEM WPITEM
	ON WPITEM.ITEMNUM = PRLINE.ITEMNUM
LEFT JOIN MAXIMO.PR PR 
	ON PRLINE.PRNUM = PR.PRNUM AND PR.STATUS IN ('APPR', 'WAPPR')
WHERE WPITEM.WONUM = 'SNC617342'

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2015-01-15 : 14:23:19

You'll likely need to post your question like this for us to help: http://www.sqlservercentral.com/articles/Best+Practices/61537/

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

sjackson330
Starting Member

4 Posts

Posted - 2015-01-15 : 15:45:31

I really appreciate the help...thank you. Your query is returning the same information as I was getting before. I apologize for not being more clear in my initial (and follow-up) posts, so hopefully this will be better.

============================================
TABLE_1 is WPITEM. It contains 3 records where WPITEM.WONUM = 'SNC617342'

WONUM | ITEMID
SNC617342 | 1274437
SNC617342 | 627396
SNC617342 | 663333
============================================
TABLE_2 is PRLINE (which is our Purchasing Requisition Line table). I have to join WPITEM to PRLINE by using the ITEMID field. I only want Active PR's. In order to obtain Active PR's I have to look at the STATUS on the PR_HEADER table (which is TABLE_3). Currently, there are no "Active" PR's for the 3 ITEMID's listed in my above data output, so my output should be just 3 records and the PRNUM field should be blank.

However, this is what I'm currently getting:

WONUM | ITEMNUM | PRNUM | STATUS
SNC617342 | 1274437 | 15417 | CANCELLED
SNC617342 | 1274437 | 68914 | CLOSED
SNC617342 | 1274437 | 70962 | CLOSED
SNC617342 | 1274437 | 68909 | CLOSED
SNC617342 | 1274437 | 18350 | CLOSED
SNC617342 | 627396 | 37785 | CANCELLED
SNC617342 | 663333

I have 5 PR's in the PRLINE table, for ITEMID 1274437, but all of those PR's are either cancelled or closed and should not be showing up.

What I want the output file to look like:

WONUM | ITEMID | PRNUM
SNC617342 | 1274437 |
SNC617342 | 627396 |
SNC617342 | 663333 |

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2015-01-15 : 15:47:48

http://www.sqlservercentral.com/articles/Best+Practices/61537/

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

sjackson330
Starting Member

4 Posts

Posted - 2015-01-15 : 16:44:45

I've looked through the Forum Etiquette website several times but I'm an idiot and don't follow exactly what he's trying to say. I'm sure you'll read that and just think I'm being lazy, but I really don't understand how to display my posts to look like what the website suggests.

I've been looking through other posts on this forum to get an idea of how they are displaying their data. Hopefully I'll figure something out.

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-01-15 : 19:27:03

Just follow the steps one by one and substitute your tables for the sample ones.

enclose code in the special "code" tags. See the site FAQ: http://www.sqlteam.com/forums/faq.asp#format

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2015-01-15 : 19:31:40

Here's an example of one where I needed help a few years ago: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=110247

I got replies pretty quickly since I provided the info in the format that allows people to reproduce the issue on their own computers and can play with solutions there.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources