Repeating matches - 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

General SQL Server Forums

New to SQL Server Programming

Repeating matches

Author

Topic

cloud23
Starting Member

8 Posts

Posted - 2007-05-18 : 05:31:49

Hi. I am new to SQL.I hope you veteran out there to help me solve the simple problem i met.

CREATE TABLE BASKET(
B# NUMBER(6) NOT NULL,
ITEM VARCHAR(6) NOT NULL,
CONSTRAINT BASKET_PKEY PRIMARY KEY(B#, ITEM) );

My statement

SELECT DISTINCT L1.ITEM, L2.ITEM,COUNT(L1.B#)
FROM BASKET L1 ,BASKET L2
WHERE L1.B# = L2.B#
AND L1.ITEM <> L2.ITEM
GROUP BY L1.ITEM,L2.ITEM;

the result is

ITEM ITEM COUNT(L1.B#)
------ ------ ------------
BEER MILK 5
BEER BREAD 4
BEER BUTTER 2
MILK BEER 5
MILK BREAD 6
MILK BUTTER 5
BREAD BEER 4
BREAD MILK 6
BREAD BUTTER 5
BUTTER BEER 2
BUTTER MILK 5
BUTTER BREAD 5

The problem is how to get rid those repeating group like (BEER,MILK) and (MILK,BREAD)?

nr
SQLTeam MVY

12543 Posts

Posted - 2007-05-18 : 11:29:47

do you mean (BEER,MILK) and (MILK,BEER)?

SELECT DISTINCT L1.ITEM, L2.ITEM,COUNT(L1.B#)
FROM BASKET L1 ,BASKET L2
WHERE L1.B# = L2.B#
AND L1.ITEM < L2.ITEM
GROUP BY L1.ITEM,L2.ITEM;

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

cloud23
Starting Member

8 Posts

Posted - 2007-05-18 : 12:30:05

yea that is my mistake sorry about it

cloud23
Starting Member

8 Posts

Posted - 2007-05-18 : 12:32:57

So my question is how to get rid those repeating group like (BEER,MILK) and (MILK,BEER)? Summary, I only need (BEER,MILK) and i dun need (MILK,BEER).

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources