select - 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 2005 Forums

Transact-SQL (2005)

select

Author

Topic

arkiboys
Master Smack Fu Yak Hacker

1433 Posts

Posted - 2010-03-17 : 06:03:45

Hello,

tblMain has several fields i.e.

ID, Code, number
1, 'xxx', 5
2, 'xxx', 6
3, 'xxx', 8
4, 'yyy', 3
5, 'yyy', 4
6, 'yyy', 8
...
...
...

I would like to find the codes which have both number ((5 or 6) AND (7 or 8))
How can I do this in sql please?

The result for the above sample should show:
'xxx'

Thanks

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-03-17 : 06:19:14

One way:
select distinct code from tblMain t1
where (number = 5 and exists (select * from tblMain t2 where t2.code = t1.code and t2.number = 6))
or (number = 7 and exists (select * from tblMain t2 where t2.code = t1.code and t2.number = 8))

No, you're never too old to Yak'n'Roll if you're too young to die.

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-03-17 : 06:30:44

Another way.

1) Assuming that there can be no multiple rows of CODE and NUMBER (it's a key). For example you can't have 2 rows both 'xxx', 5 then.


SELECT
	code
FROM
	tblMain
WHERE
	number IN (5,6)
GROUP BY
	code
HAVING
	COUNT(code) = 2

If you can have 2 rows with the same code and number then this:


SELECT
	d.code
FROM
	(
	SELECT DISTINCT
		code AS code
		, number AS number
	FROM
		tblMain
	WHERE
		number IN (5,6)
	)
	d
GROUP BY
	d.code
HAVING
	COUNT(d.code) = 2

I'm not sure which solution is faster (Webfred's EXISTS or this GROUP BY / HAVING)

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

arkiboys
Master Smack Fu Yak Hacker

1433 Posts

Posted - 2010-03-17 : 06:37:06

Thank you all.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources