Select WHERE value IN - 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

Select WHERE value IN

Author

Topic

jjlax328
Starting Member

1 Post

Posted - 2007-03-27 : 12:56:59

Hey guys and thanks in advance for taking a look at this!

I have a table with 2 columns:
First column is an POST_ID
Second column is an ATTRIBUTE_ID

What I want to do is find all 'POST_IDs' that have "multiple" ATTRIBUTE_ID's but it must have ALL OF THEM

For example what I'm doing now:

SELECT POST_ID 
FROM TBL_POST_ATTRIBUTES 
WHERE POST_ID > 0 AND ATTRIBUTE_ID in(3380,3412,3348)

That is giving me all POST_IDs that have 1 of the three ATTRIBUTE ID's which is not what I want.

I want ONLY those POST_IDs that have ALL THREE of those ATTRIBUTE ID's

Anyone know how I can make this happen? I thought I could add 'AND' b/w the values but thats not cutting!

Please help!

THANKS GUYS!

X002548
Not Just a Number

15586 Posts

Posted - 2007-03-27 : 13:03:56

Not tested
[doh]


SELECT DISTINCT a.POST_ID a
  FROM TBL_POST_ATTRIBUTES
  JOIN (SELECT POST_ID, ATTRIBUTE_ID FROM TBL_POST_ATTRIBUTES WHERE ATTRIBUTE_ID =3380) b
    ON a.POST_ID = b.POST_ID
  JOIN (SELECT POST_ID, ATTRIBUTE_ID FROM TBL_POST_ATTRIBUTES WHERE ATTRIBUTE_ID =3412) c
    ON a.POST_ID = b.POST_ID
  JOIN (SELECT POST_ID, ATTRIBUTE_ID FROM TBL_POST_ATTRIBUTES WHERE ATTRIBUTE_ID =3348) d
    ON a.POST_ID = b.POST_ID

[/doh]

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2007-03-27 : 13:04:26

You could do it this way

SELECT POST_ID
FROM TBL_POST_ATTRIBUTES
WHERE POST_ID > 0 AND ATTRIBUTE_ID in(3380,3412,3348)
GROUP BY POST_ID
~~HAVING count(*) = 3~~
HAVING count(distinct ATTRIBUTE_ID) = 3

~~You'll need to know how many attributes are in the list for the check in the HAVING, and there must not be any duplicates (although you could handle that with a subquery if necessary).~~

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-27 : 13:15:39

HAVING COUNT(DISTINCT Attribute_ID) = 3

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-27 : 13:20:17

[code]SELECT Post_ID
FROM (
SELECT 0 AS c
Post_ID
FROM tbl_Post_Attributes
WHERE Attribute_ID = 3380

UNION

SELECT 1,
Post_ID
FROM tbl_Post_Attributes
WHERE Attribute_ID = 3412

UNION

SELECT 2,
Post_ID
FROM tbl_Post_Attributes
WHERE Attribute_ID = 3348
) AS x
GROUP BY Post_ID
HAVING COUNT(*) = 3[/code]

Peter Larsson
Helsingborg, Sweden

tadin
Yak Posting Veteran

63 Posts

Posted - 2007-03-27 : 20:24:23

SELECT POST_ID, ATTRIBUTE_ID
FROM TBL_POST_ATTRIBUTES
WHERE ATTRIBUTE_ID in(3380,3412,3348)

i think i would do like this.

or

SELECT POST_ID, ATTRIBUTE_ID
FROM TBL_POST_ATTRIBUTES
WHERE ATTRIBUTE_ID = 3380
or ATTRIBUTE_ID=3412
or ATTRIBUTE_ID =3348

i just made some changes...

I don't understand one this....are the three values in one column...

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-28 : 00:39:08

Why would you like to do that?

Peter Larsson
Helsingborg, Sweden

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2007-03-28 : 10:29:03

quote:

Originally posted by Peso

SELECT		Post_ID
FROM		(
			SELECT	0 AS c
				Post_ID
			FROM	tbl_Post_Attributes
			WHERE	Attribute_ID = 3380

			UNION

			SELECT	1,
				Post_ID
			FROM	tbl_Post_Attributes
			WHERE	Attribute_ID  = 3412

			UNION

			SELECT	2,
				Post_ID
			FROM	tbl_Post_Attributes
			WHERE	Attribute_ID = 3348
		) AS x
GROUP BY	Post_ID
HAVING		COUNT(*) = 3

Peter Larsson
Helsingborg, Sweden

Peter, what benefit does this give over my query (updated with your distinct fix), which is much simpler?

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2007-03-28 : 10:32:43

quote:
Originally posted by tadin

SELECT POST_ID, ATTRIBUTE_ID
FROM TBL_POST_ATTRIBUTES
WHERE ATTRIBUTE_ID in(3380,3412,3348)

i think i would do like this.

or

SELECT POST_ID, ATTRIBUTE_ID
FROM TBL_POST_ATTRIBUTES
WHERE ATTRIBUTE_ID = 3380
or ATTRIBUTE_ID=3412
or ATTRIBUTE_ID =3348

i just made some changes...

I don't understand one this....are the three values in one column...

But that doesn't work because you are just looking for any row that meets the criteria, but you want to take a group of rows and make sure that taken together, they meet your criteria. So use either of the queries we gave you, which do the grouping that you need.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-28 : 10:50:59

Nothing more than show an alternative way to do this.

Peter Larsson
Helsingborg, Sweden

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2007-03-28 : 11:04:18

Cool, thanks

tadin
Yak Posting Veteran

63 Posts

Posted - 2007-03-28 : 16:34:43

I still don't understand this question. Is the post_id unique.
post_id attribute
0 2
0 3

why would you have a table that doesn't have a primary key.

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2007-03-28 : 16:43:25

quote:
Originally posted by tadin

I still don't understand this question. Is the post_id unique.
post_id attribute
0 2
0 3

why would you have a table that doesn't have a primary key.

Who said anything about a primary key, the query is finding those POST_IDs that have all three of the given ATTRIBUTE_IDs. The primary key could be another column altogether, or it could be a compound primary key of the POST_ID and the ATTRIBUTE_ID. You're assuming that the POST_ID is the primary key, but we never said that.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources