Page 1 of 1

Problem selecting rows from 2 tables..

Posted: Sun Nov 06, 2011 8:01 pm
by orbdrums
Hey all,

I am trying to get rows from 2 tables in the same query. I have a common field in both tables called "member_id". The following error message is being returned when I run the query and I don't know why.

Invalid query: Column 'member_id' in where clause is ambiguous Whole query: SELECT * FROM login_hist,logout_hist WHERE `member_id` = '4' ORDER BY `login_date` DESC, `login_time` DESC

This is the php code that generates the error:

Code: Select all

$query = "SELECT * FROM login_hist,logout_hist WHERE `member_id` = '$mbr_id' ORDER BY `login_date` DESC, `login_time` DESC";

Thanks!!!

Re: Problem selecting rows from 2 tables..

Posted: Sun Nov 06, 2011 9:01 pm
by Celauran
First of all, don't SELECT *. Figure out which columns you need and select those.

If you've got two columns with the same name in two tables, just specify which table you're using.

Code: Select all

SELECT cola, colb, colc
FROM tablea, tableb
WHERE tablea.cola = 'some value'

Re: Problem selecting rows from 2 tables..

Posted: Sun Nov 06, 2011 9:11 pm
by orbdrums
Okay, so what if the field names are different in the tables? My first table (login_hist) has 'login_date' and 'login_time' and the second table (logout_hist) has 'logout_date' and 'logout_time'. I'm not sure whether this is a database structure problem or a php syntax problem.

Re: Problem selecting rows from 2 tables..

Posted: Sun Nov 06, 2011 9:14 pm
by Celauran
It's the member_id that's ambiguous because it exists in both tables. The other columns shouldn't give you any trouble. You can always specify table.column, though.

Re: Problem selecting rows from 2 tables..

Posted: Sun Nov 06, 2011 10:28 pm
by orbdrums
I don't understand what you're saying. Should I get something you're not saying?

Re: Problem selecting rows from 2 tables..

Posted: Mon Nov 07, 2011 10:27 am
by pickle
When a column is ambiguous, it means MySQL can't determine which column you mean, because it exists multiple times in the tables you're querying. If a column isn't ambiguous, it means it only exists once and MySQL knows which column you mean.

To make an ambiguous column unambiguous, you need to give MySQL more information about the column so it knows exactly which one you mean. So, modify your query like so:

Code: Select all

SELECT * FROM login_hist,logout_hist WHERE `login_hist`.`member_id` = '$mbr_id' ORDER BY `login_date` DESC, `login_time` DESC";
...or specify `logout_hist` if you need.

Also, I'm moving this to the "Databases" forum.

Re: Problem selecting rows from 2 tables..

Posted: Mon Nov 07, 2011 2:24 pm
by orbdrums
Getting closer. login_hist has the correct number of records but logout_hist has every record from every user because there is no filter. How do I include only those records from logout_hist that are the same as login_hist. That is, equal to the current member id. Thanks again for your help.

Re: Problem selecting rows from 2 tables..

Posted: Mon Nov 07, 2011 2:34 pm
by pickle
That'd be in the WHERE clause. Set a condition that filters rows from `logout_hist`.

Re: Problem selecting rows from 2 tables..

Posted: Mon Nov 07, 2011 10:32 pm
by orbdrums
Everything seems to be working correctly now. Thanks for the help!