specific records from a table - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

specific records from a table

Author

Topic

a.ashabi
Posting Yak Master

117 Posts

Posted - 2008-07-14 : 06:59:00

I have a query which shows all the open orders from tbl_orders.some order_id has more than one products that I show them on tbl_order_details.and there is a tbl_product with product_inventory field ,when the product_inventory =3 it means it is in the stock.
Now from tbl_order_details I want to grab those records which has more than one order_id_details and we have at least one product in the inventory.for instance I don’t want to show 2 records on tbl_order_details which both of them have product_inventory=3. for at least one record product_inventory should be 3 and for the second one should be 5.
I really stuck and don’t know what to do.
The joints are correct but the results are not.This one doesn’t work correctly:

SELECT TOP 100 PERCENT
FROM dbo.tbl_order_details a INNER JOIN
dbo.tbl_product b ON a.order_details_product_id = b.product_id INNER JOIN
dbo.tbl_supplier s ON b.product_supplier = s.supplier_id INNER JOIN
dbo.tbl_inventory ON b.product_inventory = dbo.tbl_inventory.inventory_id
WHERE (a.order_details_order_id =@orderID) AND (
b.product_inventory = 5)

please help.thank u.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-07-14 : 07:06:24

Use DISTINCT. Or (if you are using SQL Server 2005) try the new ROW_NUMBER() function.

http://weblogs.sqlteam.com/jeffs/archive/2008/05/13/question-needed-not-answer.aspx
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

E 12°55'05.25"
N 56°04'39.16"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-14 : 07:09:13

Show some sample data from tables and explain what you want

a.ashabi
Posting Yak Master

117 Posts

Posted - 2008-07-14 : 07:45:28

thanks.this is tbl_order which shows all the orders:

this is tbl_order_details:

now from tbl_order_details I need a query which gives me some results like this:

as you can see I have 2 records for one order_details_order_id which
for one of them the product_inventory is 3.

I did the 3rd query manualy to show the result.how can I grab those order_details_order_id that they are more than on record and for at least one of them the product_inventory is 3.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-07-14 : 07:59:52

We can't see images stored on your local harddrive c:

E 12°55'05.25"
N 56°04'39.16"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-14 : 08:03:23

post it in some shared server.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources