[SOLVED] Would like help with a query

[jim.davidson]

I'm using mySQL 4.1.21 php and dreamweaver 8

 

I have two tables, I need to get a recordset that has all the records from one table and one record from the other.

 

How do I query it so that I get the state_id and state_name from Table B for a individual customer_id and all the state_id's and state_names from Table A (Table A sorted on country ascending)

 

Table A Table B

 

state_id    int(10)             state_id    int(10)

state_name  varchar(50) state_name  varchar(50)

country    varchar(50) customer_id  int(10)

 

Example:

 

401  Hemshire (from Table B)

001  Alabama (the rest from Table A)

002  Alaska

003  Arkansas

[Wildbug]

(Please use code tags, especially when posting monospaced text like above.)

 

SELECT state_id,state_name FROM table_b WHERE customer_id=??? UNION SELECT state_id,state_name FROM table_a

[jim.davidson]

Thanks, that helps some...but I still need to have table A ordered by country and state_name.

 

I tried this and get an error...Unknown table 'states' in order clause

 

SELECT state_id,state_name FROM other_states WHERE customer_id=46 UNION SELECT state_id,state_name FROM states ORDER BY states.country, states.state_name

 

[Illusion]

No need to specify in ORDER BY clause as states.country, states.state_name, simply write as ORDER BY country, state_name

[jim.davidson]

Thanks for the help this works

 

SELECT state_id, state_name, country

FROM other_states

WHERE customer_id =46

UNION SELECT state_id, state_name, country

FROM states

ORDER BY country, state_name