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 10:20 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Wednesday, August 27, 2014 2:37 PM
Points: 78, Visits: 284
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.


Thanks.

Gunjan.
Post #1438895
Posted Thursday, April 4, 2013 10:53 AM


SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Friday, September 19, 2014 1:08 PM
Points: 80, Visits: 351
Hi Gunjan,

There are at least two ways to do this.

The script below creates a sample database named TEST with a table called MY_SHIPPING.

I loaded the table with your sample data.

First solution is to use a sub query to find shipments that have other products as a list. Then pick only shipments that do not appear in the list.

Second solution uses a common table expression to get a list of shipments that have other products. Left join this table against the MY_SHIPPING and find records that do not match. IE - just have the product type in question.

I hope this helps out.

Follow me on twitter to find out more about my blog posts and PASS events.

Cheers

John




--
-- Create a test database
--

-- Which database to use?
USE [master];
GO

-- Delete existing databases.
IF EXISTS (SELECT name FROM sys.databases WHERE name = N'TEST')
DROP DATABASE [TEST]
GO

-- Create a test database
CREATE DATABASE [TEST];
GO


--
-- Create the master table
--

-- Which database to use?
USE [TEST];
GO

-- Delete existing table
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[MY_SHIPPING]') AND type in (N'U'))
DROP TABLE [dbo].[MY_SHIPPING]
GO

-- Create a new table
CREATE TABLE MY_SHIPPING
(
SHIPPING_ID INT NOT NULL,
PRODUCT_ID INT NOT NULL,
PRODUCT_TYPE INT,
QUANTITY INT
);

-- Surrogate key
ALTER TABLE [dbo].[MY_SHIPPING]
ADD CONSTRAINT PK_SHIPPING_ID PRIMARY KEY CLUSTERED (SHIPPING_ID, PRODUCT_ID);
GO


--
-- Load the shipping table with data
--

INSERT INTO [dbo].[MY_SHIPPING] VALUES
(1, 1, 2, 10),
(1, 2, 2, 5),
(1, 3, 3, 1),
(2, 1, 2, 10),
(2, 2, 2, 5);


-- Solution 1 = IN CLAUSE
DECLARE @MY_TYPE1 INT = 2;
SELECT
DISTINCT SHIPPING_ID
FROM
[dbo].[MY_SHIPPING] A
WHERE
PRODUCT_TYPE = @MY_TYPE1 AND
A.SHIPPING_ID NOT IN
(SELECT SHIPPING_ID FROM [dbo].[MY_SHIPPING] WHERE PRODUCT_TYPE <> @MY_TYPE1);


-- Solution 2 = CTE
DECLARE @MY_TYPE2 INT = 2;
WITH CTE_NO_OTHER_TYPES AS
(
SELECT DISTINCT SHIPPING_ID FROM [dbo].[MY_SHIPPING]
WHERE PRODUCT_TYPE <> @MY_TYPE2
)
SELECT
DISTINCT A.SHIPPING_ID
FROM
[dbo].[MY_SHIPPING] A LEFT JOIN CTE_NO_OTHER_TYPES C
ON
A.SHIPPING_ID = C.SHIPPING_ID
WHERE
C.SHIPPING_ID IS NULL;


John Miner
Crafty DBA
www.craftydba.com
Post #1438908
Posted Thursday, April 4, 2013 11:24 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Wednesday, August 27, 2014 2:37 PM
Points: 78, Visits: 284
Damn...This is magic. It worked John.

Thanks a lot...


Thanks.

Gunjan.
Post #1438929
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse