Mysql Left join index

Question

I used the following query

select a.*,b.* from firsttable a left join secondtable b on a.id=b.item_id ORDER BY a.id DESC LIMIT 0,10

To display items from two tables, where the id of the first table is the item_id of the second. My question is , when I try to display this in php , if I want to display a.id i can try with:

while($row=$go->fetch_assoc()){
echo $row['id'];
}

or

while($row=$go->fetch_assoc()){
echo $row['a.id'];
}

since both tables have id,on the first example displays only if there are matching rows from first and second table and displays the id of second table, I want the id of first

and on the second it says undefined index. Can you explain why is this please?

Edit: Adding tables for example

id
info
username

id
item.id
username

run your query in phpmyadmin and see what the result is. every column has ti have its own name and this you can adress — nbk, Feb 29 '20 at 10:10
Don't use `SELECT *`. Instead name, and qualify (and, where necessary, alias) only those columns you actually want returned — Strawberry, Feb 29 '20 at 10:11
I have addet additional info that both tables have id column and I want the info from first, but with php it returns row[id] of the second — Exc, Feb 29 '20 at 10:13
try your query in dbfiddle, they have much mire stringent rules, without knowing the result of your query, it is impossible to say what php expects. If you want to solve it on your own get rid of the * in your query and give every column a unique name — nbk, Feb 29 '20 at 10:42
even if I specify the column names, it still gives the same result. I think the issue is that table1 has id and table2 has id, and php returns only id of table2, and I want id value of table1 — Exc, Feb 29 '20 at 10:50

GMB · Accepted Answer · 2020-02-29T10:56:05.063

0

Both tables have a column that has the same name, so, when using select *, it is ambiguous to which column id relates to.

The only way to remove the ambiguity is to explicitly list all the columns you want to select, using aliases for homonyms:

select 
    a.id, 
    b.id b_id, -- alias for b.id
    b.item_id
    -- more columns here as needed
from firsttable a 
left join secondtable b on a.id=b.item_id 
order by a.id desc 
limit 0,10

This is one of the many reasons why select * is generally considered a bad practice in SQL.

Recommend reading: What is the reason not to use select *?

edited Feb 29 '20 at 10:56

answered Feb 29 '20 at 10:50

GMB

216,147
25
84
135

@Exc: you need to *alias* the ambiguous column, as shown in my answer... – GMB Feb 29 '20 at 10:57
Other user had suggested alias, but to be honest I did not know what exactly is he suggesting to do, and with this example it works. – Exc Feb 29 '20 at 10:59

Mysql Left join index

1 Answers1