SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Get the one set of values from the same table


Get the one set of values from the same table

Author
Message
Gunjan
Gunjan
Right there with Babe
Right there with Babe (752 reputation)Right there with Babe (752 reputation)Right there with Babe (752 reputation)Right there with Babe (752 reputation)Right there with Babe (752 reputation)Right there with Babe (752 reputation)Right there with Babe (752 reputation)Right there with Babe (752 reputation)

Group: General Forum Members
Points: 752 Visits: 309
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.
Sean Lange
Sean Lange
SSC Guru
SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)

Group: General Forum Members
Points: 59828 Visits: 17947
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.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Gunjan
Gunjan
Right there with Babe
Right there with Babe (752 reputation)Right there with Babe (752 reputation)Right there with Babe (752 reputation)Right there with Babe (752 reputation)Right there with Babe (752 reputation)Right there with Babe (752 reputation)Right there with Babe (752 reputation)Right there with Babe (752 reputation)

Group: General Forum Members
Points: 752 Visits: 309
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
Sean Lange
SSC Guru
SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)

Group: General Forum Members
Points: 59828 Visits: 17947
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.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Gunjan
Gunjan
Right there with Babe
Right there with Babe (752 reputation)Right there with Babe (752 reputation)Right there with Babe (752 reputation)Right there with Babe (752 reputation)Right there with Babe (752 reputation)Right there with Babe (752 reputation)Right there with Babe (752 reputation)Right there with Babe (752 reputation)

Group: General Forum Members
Points: 752 Visits: 309
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search