An interesting SQL Search problem

[jonshutt]

Hi folks. maybe experts here can help me out here...

 

I have 2 tables in a database, both store different information about a user.

 

eg:

table1 -> userid | name | address | phone

table2 -> userid | gender | age

 

(in fact, it's a bit more complicated than that, but that'll do for the example)

 

What I need to do is a search allong the lines of

 

SELECT * from `table1`, `table2`  where `table1.name` = 'jon' AND `table2.gender` = 'male'

 

 

However, it doesn't work, I'm getting an error like:  'Unknown column 'table1.name' in 'where clause'.

Any help appreciated.

 

If possible, it would be great to get this search query into 1 thing becuase the CMS i'm updating passes the results through an XML ajax thing, and can only display the results for one search query at a time...

 

Thanks

[fooDigi]

the `ticks` in your sql query are wrapped around both the table name and field name... should be on each object like ...

 

`table1`.`name`

[aschk]

I'm not sure how MySQL treats natural joins these days but I recommend reformatting your SQL to look a bit more like this:

 

SELECT *
FROM table1 t1
JOIN table2 t2 ON t1.userid = t2.userid
WHERE t1.name = 'jon'
AND t2.gender = 'male'