| Author |
 Topic  |
|
|
Goalie35
Yak Posting Veteran
73 Posts |
 Posted - 08/01/2012 : 12:29:06
|
Hello. I'm hoping I explain my issue clearly enough, as it's a bit complicated but I would GREATLY appreciate any help on this!
I need to build a query to return all Shipment data pertaining to a single barcode. The problem is, this barcode can be a child of a "parent" barcode. The remaining details below is just a long winded explanation of my problem (again, REALLY appreciate any help) but long story short, I need to know how to pull child data from a table that only links by the parent data.
I work in a shipping company that has an application to track the barcodes of packages. A lot of times, we ship out a single package (known as "parent item") that contains a bunch of individual items within it (known is "child items").
The package (i.e. parent item) has its own barcode as do the individual items within (i.e. child items). So, when shipping, a guy in the warehouse scans all the barcodes of the child items, places them into a parent package, then scans the barcode of the parent package, and finally ships it out the door.
To do this, we have 3 tables:
1) Shipments
2) ShipmentDetails
3) Items
The Items table contains ALL barcodes (parent items & child items).
Shipments link to ShipmentDetails (shipID), which links to the parent item within the Items table (ItemID). ShipmentDetails NEVER links to a child item within the Items table.
Here's my basic table structure:
Shipments:
ShipID | ShipTo | Address | City | State | Zip
----------------------------------------------------
1 | John | 123 Main St | My Town | NY | 11101
2 | Amy | 456 Pine Ave | Smithburg | WA | 39393
3 | Tom | 789 West Blvd | Greenville | NC | 11920
ShipmentDetails:
ShipDetailID | ShipID | ItemID | ShipDate | TrackingNumber | Cost
-----------------------------------------------------------------
1 | 1 | 1 | 1/1/2012 | 123456789 | 3.24
2 | 1 | 4 | 1/1/2012 | 213421444 | 4.55
3 | 1 | 1 | 4/18/2012 | 547677546 | 1.99
4 | 1 | 6 | 4/18/2012 | 564356463 | 8.25
5 | 1 | 4 | 6/12/2012 | 798900777 | 1.50
And finally, the Items table (items with NULL ParentItemID's are the parents. All others are the children):
Items:
ItemID | ParentItemID | ItemCode | ItemDescription | Barcode
--------------------------------------------------------------
1 | NULL | 3939-1 | Parent Item A | AE00202838338
2 | 1 | 3967-2 | Child Item A1 | HC393911HC001
3 | 1 | 3999-6 | Child Item A2 | IE88201GGD100
4 | NULL | 9928-1 | Parent Item B | 18C9981100CC0
5 | 4 | 8829-3 | Child Item B1 | OP29229911911
6 | NULL | 9199-2 | Parent Item C | VV2292911911C
7 | 6 | 292921 | Child Item C1 | 9FDDKD1110108
8 | 6 | 29229-1 | Child Item C2 | 91DHDDWWIQ101
9 | 6 | 19191-9 | Child Item C3 | QX292911228FF
So, my question is, how do I get all data from the "Shipments" table, based on a single barcode? I can find it when searching for the parent barcode, but not sure how to do this when searching for a child barcode.
For example, something like this:
SELECT
s.*
FROM
Shipments s
INNER JOIN ShipDetails sd ON sd.shipID = s.shipID
INNER JOIN Items i ON i.ItemID = sd.ItemID
WHERE
i.Barcode = @barcode
This will return results if @barcode is a parent barcode, but not a child. Any idea how I can do this?
Thanks. |
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47121 Posts |
|
|
Goalie35
Yak Posting Veteran
73 Posts |
Posted - 08/01/2012 : 16:34:27
|
That did it.
Thanks so much! |
 |
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47121 Posts |
Posted - 08/01/2012 : 16:38:42
|
welcome
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
|
Barcode
Starting Member
11 Posts |
Posted - 11/21/2012 : 21:11:11
|
I guess you need to encode these information in one code which support encoding supplementary information in 2/5 digits on. Like UPC, EAN, or ISSN barcodes. They can encode extra data or details in the supplementary data. Hope this helps.
barcode@www.aspper.com |
|
|
|
Thomassunshine
Starting Member
5 Posts |
|
| |
Topic |
|
Query to find records AND child records?
