Page 1 of 1

Select data from one table using other table field?

Posted: Fri May 14, 2010 1:57 pm
by gertrudis
Hi to all.
I really don't know how to explain what i want, but I'll try:

I have two table:
the first one with the data I want to select example: Name, lastname, phone, state
the second with the option i want to select from the first one, db_option:
id state client
100 NY N
100 NJ Y
100 GA N
100 FL N
101 TX Y
101 LA Y
101 AL N
102 AZ Y
102 NM Y
102 CO N
103 TX Y
103 WA Y
this is the select i'm using: "SELECT * FROM clients WHERE client='Y' and (state='CA' OR state='WA' OR state='CO') ", but i have to change all cities any time i need new state and what i want is to change only the id option to use in the sqlselect

But what I need is to store on array or anything the second table, so i don't have to change the code, only the table data with new id and option:

idtouse=100
myarray=tablefields: that will hold the table like: (state='CA' OR state='WA' OR state='CO)


"SELECT * FROM clients WHERE client='Y' and (myarray) "

I need help or point me to right direction please, thanks

Re: Select data from one table using other table field?

Posted: Fri May 14, 2010 2:54 pm
by mikosiko
one easy alternative

Code: Select all

SELECT * FROM clients WHERE client='Y' 
  AND state IN (SELECT state FROM db_option WHERE id = $id)
$id is your parameter

Re: Select data from one table using other table field?

Posted: Fri May 14, 2010 3:18 pm
by Eran
Better to use a join and not a dependent subquery -

Code: Select all

SELECT clients.* FROM clients
INNER JOIN db_options ON db_options.state=clients.state
WHERE clients.client='Y' AND db_options.id=$id

Re: Select data from one table using other table field?

Posted: Tue May 18, 2010 8:08 pm
by gertrudis
thanks a lot