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
SSC Eights!
SSC Eights! (804 reputation)SSC Eights! (804 reputation)SSC Eights! (804 reputation)SSC Eights! (804 reputation)SSC Eights! (804 reputation)SSC Eights! (804 reputation)SSC Eights! (804 reputation)SSC Eights! (804 reputation)

Group: General Forum Members
Points: 804 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.

Thanks.

Gunjan.
j.miner
j.miner
SSC-Addicted
SSC-Addicted (430 reputation)SSC-Addicted (430 reputation)SSC-Addicted (430 reputation)SSC-Addicted (430 reputation)SSC-Addicted (430 reputation)SSC-Addicted (430 reputation)SSC-Addicted (430 reputation)SSC-Addicted (430 reputation)

Group: General Forum Members
Points: 430 Visits: 358
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
Gunjan
Gunjan
SSC Eights!
SSC Eights! (804 reputation)SSC Eights! (804 reputation)SSC Eights! (804 reputation)SSC Eights! (804 reputation)SSC Eights! (804 reputation)SSC Eights! (804 reputation)SSC Eights! (804 reputation)SSC Eights! (804 reputation)

Group: General Forum Members
Points: 804 Visits: 309
Damn...This is magic. It worked John.

Thanks a lot...

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