[RESOLVED] select statement to filter one table on another

[Muddy]

Suppose I have two tables ...

TABLE A
name type state city
john a tx houston
john b ms jackson
john a ny new york
bill a tx houston
bill b mi grand rapids
bill c ill chicago
al b ms jackson

TABLE B
name address
john 123 new st
bill 456 old st
al 789 mid st

and i want to query all of the table b info that meets certain criteria in table a.

examples:
1) all table b entries where person is assigned the city Houston would return:
name address
john 123 new st
bill 456 old st

1) all table b entries where person is assigned any type b would return:
name address
john 123 new st
bill 456 old s

etc etc for any combination of type city state values.

what would the select statement look like? At first I thought this would be trivial and IVe been playing with Joins but so far am not even close ... any help greatly appreciated!

[penagate]

select b.* from a, b where a.city = 'Houston'

I hope your real schematic identifiers are a little more descriptive than that.

Edit: don't try this one, try the one below.

[brucevde]

That query will return every record from B several times.

You must Join the two tables

select b.*
from b inner join a on a.name = b.name
where a.city = 'Houston'

[penagate]

Not only that, but it returns then all twice. Bleh.

[Muddy]

Thanks guys! Bruce your select statement gets me closer to what I need (thanks!!), but I still have a problem. If the tables are as below I get 2 identical records returned and I only want one. I understand why it is working like this, but I still cant seem to figure out how to fix it.

Any ideas on how to fix this?



TABLE A
name type state city
john a tx houston
john b tx houston
john b ms jackson
john a ny new york
bill a tx houston
bill b mi grand rapids
bill c ill chicago
al b ms jackson

TABLE B
name address
john 123 new st
bill 456 old st
al 789 mid st

[penagate]

select distinct?

[Muddy]

select distinct?

Thanks! that seems to work ...