April 4, 2013 at 10:20 am
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.
April 4, 2013 at 10:53 am
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
April 4, 2013 at 11:24 am
Damn...This is magic. It worked John.
Thanks a lot...
Thanks.
Gunjan.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply