SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Query to find records AND child records?
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Goalie35
Yak Posting Veteran

78 Posts

Posted - 08/01/2012 :  12:29:06  Show Profile  Reply with Quote
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
52325 Posts

Posted - 08/01/2012 :  13:01:32  Show Profile  Reply with Quote
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

78 Posts

Posted - 08/01/2012 :  16:34:27  Show Profile  Reply with Quote
That did it.
Thanks so much!
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 08/01/2012 :  16:38:42  Show Profile  Reply with Quote
welcome

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

Go to Top of Page

Barcode
Starting Member

11 Posts

Posted - 11/21/2012 :  21:11:11  Show Profile  Reply with Quote
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 - 02/28/2013 :  04:07:45  Show Profile  Reply with Quote
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

USA
9 Posts

Posted - 05/26/2014 :  23:49:25  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.12 seconds. Powered By: Snitz Forums 2000