Solved

SQL 2000 Joins and NULL values

Posted on 2011-02-15
11
936 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
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure 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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

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

Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
I have a large data set and a SSIS package. How can I load this file in multi threading?
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

778 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