Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

SQL 2000 Joins and NULL values

Posted on 2011-02-15
11
937 Views
Last Modified: 2012-05-11
I have 3 tables similar to the following...

Parts
Part Number
Condition
Status

Shipments
Tray #
Lid #
Dept
To Location
From Location

Receipts
Part Number
Rec From
Rec To
Notes

I'd like to create a view in SQL 2000 that would join the shipment & receipts tables to the part table and insert NULLs where there are no records. For the Shipments table, the part number could show up in the Tray or Lid column.  I need something like the following...

Part#    Condition    Status      Dept       To Loc        From Loc         Rec From        Rec To         Notes
T1                  2              GOOD      SM           TAV              BC                    NULL           NULL              NULL
T1                  2              GOOD      CT            MIT              BC                    NULL           NULL              NULL
T1                  2              GOOD      NULL        NULL           NULL               TAV                BC                  a note about this receipt
T1                  2              GOOD      NULL        NULL           NULL               MIT                BC                  another note here



Instead, I'm getting this...

Part#    Condition    Status      Dept       To Loc        From Loc         Rec From        Rec To         Notes
T1                  2              GOOD      SM           TAV              BC                    TAV             BC              a note about this receipt
T1                  2              GOOD      CT            MIT              BC                    TAV             BC             a note about this receipt
T1                  2              GOOD      SM           TAV              BC                   MIT                BC           another note here
T1                  2              GOOD      CT            MIT              BC                   MIT                BC           another note here


What's the best way to join these tables to get the correct result?  

Thanks.
~bruno71







0
Comment
Question by:bruno71
  • 4
  • 3
  • 3
  • +1
11 Comments
 
LVL 32

Expert Comment

by:ewangoya
ID: 34898304

Use a LEFT JOIN
0
 
LVL 32

Expert Comment

by:ewangoya
ID: 34898332
Try this way
select * --add field you want
from Parts
left join Shipments on (Shipments.[Lid #] = Parts.[Part Number] or Shipments.[Tray #] = Parts.[Part Number])
left join Receipts on (Receipts.[Part Number] = Shipments.[Lid #] or Receipts.[Part Number] = Shipments.[Tray #])

Open in new window

0
 
LVL 51

Expert Comment

by:HainKurt
ID: 34898624
try

select
  r.[Part Number] [Part#], r.Condition, r.Status, s.Dept,
  case when s.[From Loc] is null then 'NULL' else s.[From Loc] end [From Loc],
  ... similar to above for all other columns that you want NULL...
from parts p left join Receipts r on p.[Part Number]=r.[Part Number]
left join Shipments s on (s.[Tray #]=p.[Part Number] or s.[Lid #]=p/[Part Number])
0
NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

 

Author Comment

by:bruno71
ID: 34899055
LEFT JOIN still produces the same results.  Here is what I have...
SELECT     {fields]
FROM         Parts
	left outer join Shipments on  (Shipments.Tray# = Parts.Part# OR Shipments.Lid# = Parts.Part#)
	left outer join Receipts on (Receipts.Part# = Parts.Part#)

Open in new window

0
 
LVL 51

Expert Comment

by:HainKurt
ID: 34899189
did you try mine? 34898624

0
 
LVL 32

Expert Comment

by:ewangoya
ID: 34899195
That should be correct, if you are getting data for all fields, it just means you have matching records for reciepits and shipment as well
0
 
LVL 51

Expert Comment

by:HainKurt
ID: 34899209
I guess I got the issue, lets join Shipments twice instead of "OR"

try this:

select
  r.[Part Number] [Part#], r.Condition, r.Status, s.Dept,
  case when s.[From Loc] is null then 'NULL' else s.[From Loc] end [From Loc],
  ... similar to above for all other columns that you want NULL...
from parts p
left join Receipts r on p.[Part Number]=r.[Part Number]
left join Shipments st on st.[Tray #]=p.[Part Number]
left join Shipments sl on sl.[Lid #]=p.[Part Number]
0
 

Author Comment

by:bruno71
ID: 34899356
HainKurt...yours didn't work.

ewangoya....There are matching records for shipments & receipts, but it's duplicating the data.  For example, I have one part with 2 shipment records and 2 receipt records.  In the results, I should have 4 records...all 4 records will have the 'Parts' table fields, 2 records should have the shipment info with NULL receipt fields, and 2 records should have the receipt info with NULL shipment fields.  
- Parts, Ship 1, NULL
- Parts, Ship 2, NULL
- Parts, NULL, Rec 1
- Parts, NULL, Rec 2


Instead, I get 4 records like so...
- Parts, Ship 1, Rec 1
- Parts, Ship 2, Rec 1
- Parts, Ship 1, Rec 2
- Parts, Ship 2, Rec 2
0
 
LVL 10

Accepted Solution

by:
PSSUser earned 500 total points
ID: 34899416
Isn't just a case of needing to use a UNION statement?

SELECT     P.[Part Number], P.[Condition], P.[Status], S.[Dept], S.[To Location], S.[From Location], NULL as [Rec From], NULL as [Rec To], NULL as [Notes]
FROM         Parts P LEFT OUTER JOIN Shipments S ON (S.[Tray #] = P.[Part Number] OR S.[Lid #] = P.[Part Number])
UNION
SELECT     P.[Part Number], P.[Condition], P.[Status], NULL as [Dept], NULL as [To Location], NULL as [From Location], R.[Rec From], R.[Rec To], R.[Notes]
FROM         Parts P LEFT OUTER JOIN Receipts R ON R.[Part Number] = P.[Part Number]
ORDER BY [Part Number], [To Location] DESC, [Rec From] DESC
--Sort order optional :)
0
 
LVL 51

Expert Comment

by:HainKurt
ID: 34899602
whats the result of

select *
from parts p
left join Receipts r on r.[Part Number]=p.[Part Number]
left join Shipments st on st.[Tray #]=p.[Part Number]
left join Shipments sl on sl.[Lid #]=p.[Part Number]

why this did not work?
0
 

Author Comment

by:bruno71
ID: 34900115
PSSUser...I think your solution worked.  I got an error that the ORDER BY items had to be in the SELECT statement (which they were).  I got around it by doing...

SELECT * FROM
(...SELECTS & UNION here...)
AS TmpTable
ORDER BY [Part Number]

Thanks for your help.

~bruno71
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

860 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question