[RESOLVED] remove single value from query

Hey all
I have 2 tables A & B
Table A

id	name	suffixID
0	him	0
1	her	1
2	it	2

Table B

id	name	isActive
0	Mr.	1
1	Mrs.	1
2	other	0

and this query...
SELECT A.name as Name,B.name as Suffix
FROM A
LEFT JOIN B ON A.suffixID = B.id

should return a table like this (unless my syntax is off which is quite possible)...
Table C

Name	Suffix
him	Mr.
her	Mrs.
it	other

What I need to do is eliminate the value for B.name whereever B.isActive=0. I don't want to remove the row, just the value in the row to have something like this...
Table C

Name	Suffix
him	Mr.
her	Mrs.
it

Can anyone tell me how to adjust the sql to achieve that?
Thanks
kevin

just add it to the join:

Code:

---

SELECT A.name as Name,B.name as Suffix
FROM A
LEFT JOIN B ON A.suffixID = B.id and B.isActive = 1

---

Now the join will only include those that are marked isActive.

-tg

you make it seem so simple.
thanks tg
kevin

Quote:

---

Originally Posted by kebo

you make it seem so simple.
thanks tg
kevin

---

It's a curse...


-tg