View Single Post
MattSharp

 
Member Since: Oct 24, 2006
Posts: 95
MattSharp is an unknown at this point

MattSharp is offline
I have two tables, Table A and Table B. Table B has a foreign key to Table A. Table B has one other column, it's a single character. There can be multiple rows in Table B tied to Table A

So you get something like this:

Code:
TABLE A
----------
ID  NAME
----------
1   Bill
2   Tom
3   Steve

TABLE B
---------
ID STATUS
---------
1  Y
2  Y
2  M
2  N
3  N
3  N
3  Y
I want to end up getting just 'Y' but not 'M'. So my query should return Bill and Steve.

I thought I could do something like this:

Code:
SELECT NAME FROM TABLEA
INNER JOIN TABLEB ON TABLEA.ID=TABLEB.ID 
GROUP BY NAME
HAVING NOT STATUS='M'
That doesn't work though. I am thinking there is a way to do this with the HAVING but I can't figure it out. Any ideas?
QUOTE Thanks