Select from two tables?

Anon

Problem:

I wan´t to do a search in two tables that are in the same database.
I thought this would work:

SELECT * from table1,table2 where field1 LIKE '%" . $searchstring . "%'

...but it didn´t.
What´s wrong?

I should mention that I´m very new to this and that I´m not very good at english, so please try to keep it on a simle level...

Thanks Jessica

junebug

now in your query you have to ask yourself how the database is going to know what to pull out of which table. is field1 in one table or both tables? do you have a primary key to connect the two tables? go here and read about joins:

http://www.mysql.com/doc/J/O/JOIN.html

Anon

I forgot:
The two tables are related by the field memberID that excists in both tables.

Anon

Hi!

I was completing my question with some of that information at the same time as you where asking...

In both tables I have a field that´s named "memberID". In table1 that is my keyfield, not in table2.

I´ve tried "join" but I just can´t get it right.

This is how I wrote:

SELECT table1.name,table2.description FROM table1 INNER JOIN table2 ON table1.memberID=table2.memberID WHERE name LIKE '%" . $searchstring . "%' OR email LIKE '%" . $searchstring . "%'";

So, that dosn´t work. How do I do that?

junebug

well then look into left joins to join the two tables:

select * from t1 left join t2 on t1.id = t2.id where t1.field = 'something';

junebug

are you using mysql? that's really all the experience i have with databases so i don't know if this query will work on other sql databases but i think it should:

select table1.name, table2.description from table1 left join table2 on table1.memberID = table2.memberID where name like '%something%' || email like '%someemail%';

Anon

My problemes are solved with the answer you sent 14:50:55.

Thank you (and yes, I´m using MySQL)!