How would you write this simple query?

[TheIceman5]

i have a table named "aaaa" with 2 columns, NAME and ID

NAME.|....ID

a......|......1
b......|......2
c .....|......3
b......|......4
e......|......2
f ......|......6
b......|......7
h .....|......8
i.......|.....2

NOTE, the dots'.' in this table are spaces, they mean nothing.

i need to write query that selects the name where the name has an id of 2 and 7
so the query in this case would return the name of b as it has an id of 2 and 7.
how do u write a query like this? its got me stuck beleive it or not.
i tried but it doesnt work.
select * from aaaa where ID = 2 and ID = 7
this returns other names that i dont want, i only want to return b as it has the two id's i want together.

[laserlight]

You need to normalise your database.

[Lars_Berg]

select name from aaaa
where id in (2,7)
group by name
having count(*) = 2

laserligth:

What?

[TheIceman5]

Originally posted by Lars Berg

select name from aaaa
where id in (2,7)
group by name
having count(*) = 2

[/B]

thanks that one worked, i just needed to change it to >= 2.

Originally posted by laserlight
You need to normalise your database.

no, database is normalised, it cant be done any further as this table is a transaction table, its gonna have more than one name and more than one ID, i need to match them up to see who has got certain items.

[laserlight]

hmm... yeah, I suspected as much after thinking further about how to actually answer your question, and Lars Berg gave a solution.

Maybe provide a better name than 'aaaa'?