SQL query - Selecting multiple records which equal a certain identifier

Im not sure how to explain this and im not use to SQL..this is my query which isnt working

select * from ao_addrcodes 
JOIN a1_addrs on a1_addrid = ao_addrid left Join a2_name on a2_addrid = ao_addrid 
where ao_codeid = 'DJ' and 'E' and 'A'

Trying to get a list of people who are in the table ao_addrid and their a开发者_如何学JAVAo_codeid is 'DJ' and 'E' and 'A'

So Fred would be in the table 3 times. His a2_addrid would be the same but would his ao_codeid would be different

Each person has multiple records if they have multiple ao_codeid 's

help?

EDIT

ao_codeid , a2_addrid

'DJ', '525'
'E', '525'
'A', '525'
'DJ', '52'
'A', '25'
'E', '25'

So from the above data i would only want to see the user 525 appear. Not 52 or 25.

I am using MySQL

If you're using SQL Server, then your WHERE statement is incorrect:

SELECT    *
FROM      ao_addrcodes
JOIN      a1_addrs ON a1_addrid = ao_addrid
LEFT JOIN a2_name ON a2_addrid = ao_addrid 
WHERE     ao_codeid IN('DJ', 'E', 'A')

Or you could do it like this

WHERE     ao_codeid = 'DJ'
OR        ao_codeid = 'E'
OR        ao_codeid = 'A'

EDIT 1

I would do something like this:

--Create a CTE that counts eligible records
WITH CodeCount AS
(
    SELECT    ID, --this represents your 525 data
              COUNT(*) AS CodeCount
    FROM      [your table]
    WHERE     code IN('DJ', 'E', 'A')
    GROUP BY  ID
)

SELECT        *
FROM          [your table]
INNER JOIN    CodeCount ON [your table].ID = CodeCount.ID
WHERE         CodeCount = 3

EDIT 2

Since you edited your post to say you're using MySQL, this would be my solution:

SELECT    *
FROM      ao_addrcodes
JOIN      a1_addrs ON a1_addrid = ao_addrid
LEFT JOIN a2_name ON a2_addrid = ao_addrid
INNER JOIN (
                SELECT       ID,
                COUNT(*) AS  EligibleCount
                FROM         ao_addrcodes
                WHERE        ao_codeid IN('DJ', 'E', 'A')
                GROUP BY     ID
            ) a ON a.ID = ao_addrcodes.ID
WHERE         a.EligibleCount = 3

It's a little hard to follow which fields you have in which tables, but I'll give it a try. The problem is most people think that you want to are looking for a textbook answer on how to use the IN operator, but you're after something a little more complex.

I think the easiest way of doing what you want is:

SELECT * 
FROM a2_name
WHERE EXISTS (
  SELECT COUNT(DISTINCT ao_codeid) FROM ao_addrcodes 
    JOIN a1_addrs on a1_addrid = ao_addrid
    LEFT JOIN a2_name on a2_addrid = ao_addrid 
  WHERE ao_codeid IN ('DJ', 'E', 'A') AND a2_addrid = ao_addrid
  HAVING COUNT(DISTINCT ao_codeid) = 3
)

Or to be less complex and still doing what you want (but I'm not sure of the fields you want returned, just replace a2_name.name with the list of all the fields you want returned as your schema is not supplied:

SELECT a2_name.name 
FROM ao_addrcodes 
  JOIN a1_addrs on a1_addrid = ao_addrid
  LEFT JOIN a2_name on a2_addrid = ao_addrid 
WHERE ao_codeid IN ('DJ', 'E', 'A')
GROUP BY a2_name.name
HAVING COUNT(DISTINCT ao_codeid) = 3

where ao_codeid = 'DJ' OR ao_codeid = 'E' OR ao_codeid = 'A'

OR find the DJ records then check the other 2 exist ?

SELECT * FROM ao_addrcodes a 
JOIN a1_addrs ON a1_addrid = a.ao_addrid 
LEFT JOIN a2_name ON a2_addrid = a.ao_addrid 
 WHERE ao_codeid = 'DJ' 
 AND EXISTS (SELECT * FROM ao_addrcodes x WHERE ao_codeid = 'E' and x.ao_addrid= a.ao_addrid)  AND 
 AND EXISTS (SELECT * FROM ao_addrcodes x WHERE ao_codeid = 'A' and x.ao_addrid= a.ao_addrid)

NOTE: The other count based solutions will ONLY work if there is a unique constraint on ao_addrid & ao_codeid.

Trying to get a list of people who are in the table ao_addrid and their ao_codeid is 'DJ' and 'E' and 'A'

Your where clause uses AND where it should be using OR, on IN, if I've understood your question, which isn't very clearly worded.

              where ao_codeid in ('DJ','E','A')

When working with SQL you have to pay very close attention to your ANDs and your ORs when you are trying to define the problem in everyday English (or whatever natural language you speak) before translating that into a query.