Get the one set of values from the same table

Question

Post reply

Get the one set of values from the same table

Gunjan

Hall of Fame

Points: 3226
More actions
April 4, 2013 at 9:39 am

#273139

Hi,
I have a table which has following columns
ShipmentID, ProductID, ProductType,Qty
Now I want to select only those records which has only productTypeID = 2
for example
ShipmentID ProductID ProductType Qty
1 1 2 10
1 2 2 5
1 3 3 1
2 1 2 10
2 2 2 5
The query should return only ShipmentID = 2, because that set only has producttypid = 2. I want to avoid those rows if it has at least one producttypeid = 3.
I tried different queries but it's not giving me the perfect result.
Can anybody help me out please?
Thanks.
Thanks.
Gunjan.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Sean Lange SSC Guru Points: 286590 More actions · Answer 1

like this:

select ShipmentID, ProductID, ProductType,Qty

from YourTable

where ShipmentID not in

(

select ShipmentID

from YourTable

where ProductType <> 2

)

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Gunjan Hall of Fame Points: 3226 More actions · Answer 2

Thanks for your quick reply Sean.

It is still giving me wrong results. it should give me the shipments which only and only has product type = 2. that means if any shipment id has product type id = 2 and also 3 then avoid those rows. there should not be product type = 3 in any of the other rows for that shipmentID.

as I said in my example

for example

ShipmentID ProductID ProductType Qty

1 1 2 10

1 2 2 5

1 3 3 1

2 1 2 10

2 2 2 5

The query should return only ShipmentID = 2, because that set only has producttypid = 2. I want to avoid those rows if it has at least one producttypeid = 3.

It should only return highlighted rows. As you can see product type = 2 is available in ShipmentID = 1 too but as it also has product type = 3 , I want to ignore that shipmentid

Thanks.

Gunjan.

Sean Lange SSC Guru Points: 286590 More actions · Answer 3

This is almost identical to another thread.

http://www.sqlservercentral.com/Forums/Topic1438875-391-1.aspx

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Gunjan Hall of Fame Points: 3226 More actions · Answer 4

Following query worked for me:

with prod as

(

select distinct ShipmentID From Shipment

where Producttypeid <> 2

)

select DISTINCT ShipmentID From Shipment h

LEFT OUTER join prod p on h.ShipmentID = p.ShipmentID

where P.ShipmentID IS NULL

Thanks.

Gunjan.