WHERE clause with multiple ANDs

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

WHERE clause with multiple ANDs

Author

Topic

aytri
Starting Member

3 Posts

Posted - 2015-04-02 : 05:12:16

Hi all,

I have a table (tblAttributes) that looks like this:

ID|ReferenceNumber|AttrID|AttrVal
1|1|90|7
1|1|91|8
1|1|92|9
2|1|90|10
2|1|91|11
2|1|92|12

I need to return all the distinct IDs where the combination of AttrID and AttrVal matches multiple criteria for that specific ID.

I have the following:

SELECT DISTINCT(ID)
FROM
((SELECT a.ID
FROM tblAttributes AS a
WHERE a.AttrID = 90 AND a.AttrVal = 7) AS x
INNER JOIN
(SELECT a.ID
FROM tblAttributes AS a
WHERE a.AttrID = 91 AND a.AttrVal = 8) AS y
ON x.ID = y.ID)

Is there a better way of doing this?

Thanks in advance.

huangchen
Starting Member

37 Posts

Posted - 2015-04-02 : 05:50:59

unspammed

aytri
Starting Member

3 Posts

Posted - 2015-04-02 : 10:37:25

Bump

bitsmed
Aged Yak Warrior

545 Posts

Posted - 2015-04-02 : 10:51:15

Something like:

select id
  from tblattributes
 where (attrid=90 and  attrval=7)
    or (attrid=91 and  attrval=8)
 group by id
 having sign(sum(case when attrid=90 and attrval=7 then 1 else 0 end))
       +sign(sum(case when attrid=91 and attrval=8 then 1 else 0 end))
       =2

aytri
Starting Member

3 Posts

Posted - 2015-04-02 : 11:15:51

Is there any way of doing this in Access?

bitsmed
Aged Yak Warrior

545 Posts

Posted - 2015-04-02 : 14:21:19

quote:
Originally posted by aytri

Is there any way of doing this in Access?

Have you tried the query I provided?
Did you get an error?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources