ID lost after left join on table

Question

Hey I tried this thread but it doesn't work and i can't figure out why...

here's my SQL:

SELECT * FROM gone_items
        LEFT JOIN items
        ON gone_items.item_ID=items.ID
        WHERE
        gone_items.aus_ID='$ID'
        ORDER BY items.name ASC

Now, I fetch that via PHP and have a $row and try another mysql to get the individual ID's of the gone_items table. But if i use $row['ID'] I get the ID of the items.ID not the one from gone_items.ID.

I tried setting the variable manually in the first query but it doesn't work.

I also tried this: MYSQL Left join A.table and b.table while retaining a.table id

Also didn't help me...

All I want is to retain the ID (Primary key) from the gone_items table..

Can anyone please tell me what I'm doing wrong ?

Love Gram

EDIT

        //Query for Joined infos
$sqlx="SELECT foto_res_ausgeliehene_geg.ID, foto_res_ausgeliehene_geg.aus_ID, foto_res_ausgeliehene_geg.geg_ID, foto_res_ausgeliehene_geg.zusaetzliches, foto_res_gegenstaende.ID, foto_res_gegenstaende.bezeichnung, foto_res_gegenstaende.seriennummer, foto_res_gegenstaende.interne_seriennummer, foto_res_gegenstaende.zusaetzliches FROM foto_res_ausgeliehene_geg
        LEFT JOIN foto_res_gegenstaende
        ON foto_res_ausgeliehene_geg.geg_ID=foto_res_gegenstaende.ID
        WHERE
        foto_res_ausgeliehene_geg.aus_ID='$ID'
        ORDER BY foto_res_gegenstaende.bezeichnung ASC
        ";
            $ergebnisx = mysqli_query($db,$sqlx);
            while ($zeilex = mysqli_fetch_assoc($ergebnisx))
            {
                //Query for individual infos
                $sqly="SELECT * FROM foto_res_ausgeliehene_geg
                        WHERE `geg_ID`='".$zeilex['ID']."'
                        AND `aus_ID`='$ID'
                        GROUP BY `geg_ID`
                        ";
                        $ergebnisy = mysqli_query($db,$sqly);
                        while ($zeiley = mysqli_fetch_assoc($ergebnisy))
                        {};

Now I did select all items individually. The foto_res_ausgeliehene_geg.ID still merges with the foto_res_gegenstanede.ID due to the LEFT JOIN.

So if i access $zeilex['ID'] im getting the ID of foto_res_gegenstaende.ID.

Would it help if I rename the ID field in one of the tables into lets say item_ID ?

Thanks alot.

Love Gram.

Don't use `select *`. Explicitly list the columns that you want to select. Voila! Your problem will be fixed. — Gordon Linoff, Oct 08 '15 at 20:51
I did doesn't work.... I don't know why. If i for example list everything but the gone_items.ID the fetch is simply empty... — Gramrock, Oct 08 '15 at 20:55
As stated in your comments, that if you explicitly select the id and it returns empty, i'm interested in seeing the two table schemes, then something must be wrong there or let's us see the code there selects it, something could also be wrong there. — mrhn, Oct 09 '15 at 10:36
I will post the php part asap, but it won't be translated it's partly german but u can see through it easily. sincerely gram. — Gramrock, Oct 09 '15 at 12:04

mrhn · Answer 1 · 2017-09-18T14:31:24.627

Instead of using select *, you should explicitly state what items you want to select. Else you can get conflicts with multiple id fields. In your case something like:

select gone_items.id, gone_items.column1, gone_items.column2, items.column1, items.column2

It is also considered good practice, to limit the amount of data there is being selected. But is meanwhile also a highly debateable what is the right way. Performance issue in using SELECT *?

score 0 · Answer 2 · edited Jun 20 '20 at 09:12

WORKS!

I simply renamed one of the Primary ID keys to something else, in this case, one of them got ID -> item_ID. The other one still is ID that way the left join won't merge them.

yolo

EDIT

WORKING CODE

    $sqlx="SELECT foto_res_ausgeliehene_geg.item_ID, foto_res_ausgeliehene_geg.aus_ID, foto_res_ausgeliehene_geg.geg_ID, foto_res_ausgeliehene_geg.zusaetzliches, foto_res_gegenstaende.ID, foto_res_gegenstaende.bezeichnung, foto_res_gegenstaende.seriennummer, foto_res_gegenstaende.interne_seriennummer, foto_res_gegenstaende.zusaetzliches FROM foto_res_ausgeliehene_geg
        LEFT JOIN foto_res_gegenstaende
        ON foto_res_ausgeliehene_geg.geg_ID=foto_res_gegenstaende.ID
        WHERE
        foto_res_ausgeliehene_geg.aus_ID='$ID'
        ORDER BY foto_res_gegenstaende.bezeichnung ASC
        ";
            $ergebnisx = mysqli_query($db,$sqlx);
            while ($zeilex = mysqli_fetch_assoc($ergebnisx))
            {
                //Query for individual infos
                $sqly="SELECT * FROM foto_res_ausgeliehene_geg
                        WHERE `item_ID`='".$zeilex['item_ID']."'
                        ";
                        $ergebnisy = mysqli_query($db,$sqly);
                        while ($zeiley = mysqli_fetch_assoc($ergebnisy))
                        {

Good you got it working just to clear things the dbo wont merge the columns but rather access it differently, as an example a join with two columns with the name id will be accessed as follows table1.id, table2.id. The merge is likely happening in the implementation somewhere, to improve the understanding, check http://www.codeproject.com/Articles/33052/Visual-Representation-of-SQL-Joins for joins and try to run the query directly on the db to see rows and columns returned, happy coding :) — mrhn, Oct 09 '15 at 21:35

ID lost after left join on table

2 Answers2

yolo