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 |
|
MyronCope
Starting Member
46 Posts |
 Posted - 2011-04-07 : 08:43:46
|
| using sql server 2005I have the following tables:ORDERS: main table that stores one record for each order, PK is orderNumITEMS: contains all the items that are shipped with each order, can reference each record with orderNumSPEC_ITEMS: contains special items that are also contained in each order, can reference each record with orderNumWhat I need to do is the following: I need one query that will return a record for each ITEM belonging to the order and also each SPEC_ITEM that belongs to each order. In the first query below I am getting each ITEM record and then in the second query I joined to the SPEC_ITEM table but I need to figure out how to also return each SPEC_ITEM record that belongs to each record.I have the following query that will return all items from the "ITEMS" table: <SQL>SELECT ITEMS.ItemDesc As ItmDesc FROM dbo.ORDERSleft outer join ITEMS on ORDERS.orderNum = ITEMS.orderNum WHERE ORDERS.ordernum='1223'--query below joins on SPEC_ITEMSSELECT ITEMS.ItemDesc FROM dbo.ORDERSleft outer join ITEMS on ORDERS.orderNum = ITEMS.orderNum left outer join SPEC_ITEMS on ORDERS.orderNum = SPEC_ITEMS.orderNum WHERE ORDERS.ordernum='1223'</SQL> Please advise on what I need to do, I think that my join for the SPEC_ITEMS table might not be right. so this returns all Items for this order from the ITEMS table ok, for this order there are 5 items.However I also need to return the special items that are in the SPEC_ITEMS table. I need 2 things:1. The ItmDesc field should pull from either ITEMS if the record is a regular item and from SPEC_ITEMS if it is a special item but more importantly2. I need to display also a record for each SPEC_ITEM that matches the order.I added the join to the SPEC_ITEMS table too but this only brings back the 5 items from the ITEMS table, there is an additional special item from the SPEC_ITEMStable that should also displaythanksMC |
|
|
jimf
Master Smack Fu Yak Hacker
2875 Posts |
Posted - 2011-04-07 : 08:59:33
|
| Could you provide sample data for each table and what you want the output to look like?Thanks,JimEveryday I learn something that somebody else already knew |
 |
|
|
MyronCope
Starting Member
46 Posts |
Posted - 2011-04-07 : 09:07:27
|
quote:
Originally posted by jimf
Could you provide sample data for each table and what you want the output to look like?Thanks,JimEveryday I learn something that somebody else already knew
Sure, Sample data is like for ItemDesc field that is something like this:ITEMS.ItemDesc: "Standard Drain", OR "Plastic Trench"SPEC_ITEMS: "Customized Drain" OR "Assembled Item"The most important thing is that I want to return a record for the SPEC_ITEMS rows that match the orderNum, I can play around with the other fields that I"m going to add after that.AT the moment it returns the ITEMS records which is a good first step but I also need to return the SPEC_ITEMS records too. Also I need itmDesc to be filled with ITEMS.itemDesc for normal item or itmDesc will pull from SPEC_ITEMS.ItemDesc for special items.after that I'll add in fields like "Item_Code" that exists in both the ITEMS table and SPEC_ITEMS table but thats somethign I can figure out, I need to get the record count good first (meaning return all records from both ITEMS and SPEC_ITEMS for the given order).sorry if I'm repeating, just trying to be clear.thanksMC |
|
|
|
jimf
Master Smack Fu Yak Hacker
2875 Posts |
|
|
MyronCope
Starting Member
46 Posts |
Posted - 2011-04-07 : 11:39:44
|
thanks, I'll try to follow that format next time.I realized that I just needed a simple join between ITEMS and the SPEC_ITEMS table, that did the trick.Is it friday yet?  MC |
|
|
|
|
|
|
|
|
query join problem