Solved

SQL 2000 Joins and NULL values

Posted on 2011-02-15
11
933 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
 

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
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
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

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

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

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
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.

867 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now