Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Get the one set of values from the same table Expand / Collapse
Author
Message
Posted Thursday, April 4, 2013 9:39 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: 2 days ago @ 12:41 PM
Points: 78, Visits: 283
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.
Post #1438866
Posted Thursday, April 4, 2013 9:59 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 3:35 PM
Points: 13,253, Visits: 12,087
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 Moden's 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)
Post #1438883
Posted Thursday, April 4, 2013 10:11 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: 2 days ago @ 12:41 PM
Points: 78, Visits: 283
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.
Post #1438891
Posted Thursday, April 4, 2013 10:26 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 3:35 PM
Points: 13,253, Visits: 12,087
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 Moden's 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)
Post #1438899
Posted Thursday, April 4, 2013 11:16 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: 2 days ago @ 12:41 PM
Points: 78, Visits: 283

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.
Post #1438921
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse