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
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Query to find records AND child records?

Author  Topic 

Goalie35
Yak Posting Veteran

81 Posts

Posted - 2012-08-01 : 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

52326 Posts

Posted - 2012-08-01 : 13:01:32
yep...use recursive ctes

see

http://msdn.microsoft.com/en-us/library/ms186243(v=sql.105).aspx

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

Goalie35
Yak Posting Veteran

81 Posts

Posted - 2012-08-01 : 16:34:27
That did it.
Thanks so much!
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-01 : 16:38:42
welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

Barcode
Starting Member

11 Posts

Posted - 2012-11-21 : 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
Go to Top of Page

Thomassunshine
Starting Member

5 Posts

Posted - 2013-02-28 : 04:07:45
Maybe you can refer to [url=http://www.businessrefinery.com/products/barcode_net/net_barcode_generator.html]Barcode Generator for .NET[/url].
It is compatible with C#, Visual Basic .NET, managed C++ and Delphi .NET and other development environment in .NET Framework.
Hope it helps!
Go to Top of Page

arronlee
Starting Member

12 Posts

Posted - 2014-05-26 : 23:49:25
Hi, Thomassunshine.
I am also testing about the related
barcode readers and barcode generators these days. Do you have any ideas about it? Or any good suggestion? Thanks in advance.



Best regards,
Arron
Go to Top of Page
   

- Advertisement -