selecting records - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

selecting records

Author

Topic

KimJ
Starting Member

38 Posts

Posted - 2003-10-03 : 10:24:08

Hi-

I need to select a group of records from a table where they exist only once (I'm sure I'm not explaining correctly)

I have a products table and I want to select the records that have a certain product, and only that product.

So, get me all the records that have (product1) and that's the only product for that record - identified by the (iOwnerID)

???

Thanks

drymchaser
Aged Yak Warrior

552 Posts

Posted - 2003-10-03 : 10:36:08

Not having data structure (ie. CREATE TABLE statements), sample data (INSERT INTO... statements), and desired results I can only guess you need something like this.

SELECT	a.*
FROM	mytable a JOIN
	(
	SELECT	<product>
	FROM	mytable
	WHERE	<product = desired product>
	GROUP BY <product>
	HAVING COUNT(*) = 1
	) b ON a.<product> = <product>

If this is not what you need, then you will need to provide the above for help.

KimJ
Starting Member

38 Posts

Posted - 2003-10-03 : 11:34:56

CREATE TABLE dbo.CustomerProducts
(
iProductID int NOT NULL ,
iOwnerID int NOT NULL ,
chProductNumber char(25) NOT NULL
)

Insert Into dbo.CustomerProducts
(
iProductID,
iOwnerID,
chProductNumber
)
Values
(
1,
67890,
'Product3'
)
Insert Into dbo.CustomerProducts
(
iProductID,
iOwnerID,
chProductNumber
)
Values
(
1,
12345,
'Product1'
)
Insert Into dbo.CustomerProducts
(
iProductID,
iOwnerID,
chProductNumber
)
Values
(
1,
12345,
'Product2'
)
Insert Into dbo.CustomerProducts
(
iProductID,
iOwnerID,
chProductNumber
)
Values
(
1,
12345,
'Product3'
)

Insert Into dbo.CustomerProducts
(
iProductID,
iOwnerID,
chProductNumber
)
Values
(
1,
54321,
'Product3'
)

results would bring back only iOwnerIDs 54321 and 67890 because they have Product3 and no other product - they're in the table just once

raymondpeacock
Constraint Violating Yak Guru

367 Posts

Posted - 2003-10-03 : 11:50:04

How about

select iOwnerID
from CustomerProducts
where iOwnerID in (select iownerid from customerproducts group by iOwnerID having count(*)=1)

Raymond

X002548
Not Just a Number

15586 Posts

Posted - 2003-10-03 : 11:51:49

[code]
USE Northwind
GO

CREATE TABLE dbo.CustomerProducts (
iProductID int NOT NULL
, iOwnerID int NOT NULL
, chProductNumber char(25) NOT NULL
)
GO

INSERT INTO dbo.CustomerProducts (
iProductID
, iOwnerID
, chProductNumber
)
SELECT 1, 67890, 'Product3' UNION ALL
SELECT 1, 12345, 'Product1' UNION ALL
SELECT 1, 12345, 'Product2' UNION ALL
SELECT 1, 12345, 'Product3' UNION ALL
SELECT 1, 54321, 'Product3'
GO

SELECT *
FROM CustomerProducts o
WHERE EXISTS (SELECT 1
FROM CustomerProducts i
WHERE o.iOwnerId = i.iOwnerId
GROUP BY iOwnerId
HAVING COUNT(*) = 1
)
GO

DROP TABLE CustomerProducts
GO
[/code]

Brett

8-)

SELECT @@POST FROM Brain ORDER BY NewId()

That's correct! It's an AlphaNumeric!

KimJ
Starting Member

38 Posts

Posted - 2003-10-03 : 11:56:40

nice...

thank you!

drymchaser
Aged Yak Warrior

552 Posts

Posted - 2003-10-03 : 13:45:28

Excellent job with repost. KimJ

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources