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.
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) Shipments2) ShipmentDetails3) ItemsThe 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 | 111012 | Amy | 456 Pine Ave | Smithburg | WA | 393933 | Tom | 789 West Blvd | Greenville | NC | 11920ShipmentDetails:ShipDetailID | ShipID | ItemID | ShipDate | TrackingNumber | Cost-----------------------------------------------------------------1 | 1 | 1 | 1/1/2012 | 123456789 | 3.242 | 1 | 4 | 1/1/2012 | 213421444 | 4.553 | 1 | 1 | 4/18/2012 | 547677546 | 1.994 | 1 | 6 | 4/18/2012 | 564356463 | 8.255 | 1 | 4 | 6/12/2012 | 798900777 | 1.50And 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 | AE002028383382 | 1 | 3967-2 | Child Item A1 | HC393911HC0013 | 1 | 3999-6 | Child Item A2 | IE88201GGD1004 | NULL | 9928-1 | Parent Item B | 18C9981100CC05 | 4 | 8829-3 | Child Item B1 | OP292299119116 | NULL | 9199-2 | Parent Item C | VV2292911911C7 | 6 | 292921 | Child Item C1 | 9FDDKD11101088 | 6 | 29229-1 | Child Item C2 | 91DHDDWWIQ1019 | 6 | 19191-9 | Child Item C3 | QX292911228FFSo, 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:SELECTs.*FROMShipments sINNER JOIN ShipDetails sd ON sd.shipID = s.shipIDINNER JOIN Items i ON i.ItemID = sd.ItemIDWHEREi.Barcode = @barcodeThis 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 ctesseehttp://msdn.microsoft.com/en-us/library/ms186243(v=sql.105).aspx------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
Goalie35
Yak Posting Veteran
81 Posts |
Posted - 2012-08-01 : 16:34:27
|
That did it.Thanks so much! |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-08-01 : 16:38:42
|
welcome------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
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 |
|
|
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! |
|
|
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 |
|
|
|
|
|
|
|