phpmyadmin Question posted in
The official documentation can be found here.

SQL LEFT JOIN is not filling a column with values from table 2 – Phpmyadmin

SELECT zmogaus_id, hobio_id, hobio_pav2
FROM zmogaus_hobiai 
LEFT JOIN hobiai 
ON zmogaus_hobiai.hobio_pav2=hobiai.hobio_pav
WHERE zmogaus_id=3

Using this I want to get

zomgaus_id =3 . hobio_id =1 . hobio_pav2 =name1 (from hobio_pav)
zomgaus_id =3 . hobio_id =2 . hobio_pav2 =name2 (from hobio_pav)

But hobio_pav2 stays as an empty column. I tried not having hobio_pav2 column but the problem persists of not adding hobio_pav values to corresponding IDs to hobio_id.
hobio_pav2 column is empty

EDIT:

sample data:

table hobiai: id = 1, hobio_pav = name1;id = 2, hobio_pav = name2;id = 3, hobio_pav = name3;

    table zmogaus_hobiai: 
id =1, zmogaus_id=2, hobio_id=2, hobio_pav2 = null;
id =2, zmogaus_id=2, hobio_id=3, hobio_pav2 = null;
id =4, zmogaus_id=3, hobio_id=1, hobio_pav2 = null;
id =5, zmogaus_id=3, hobio_id=3, hobio_pav2 = null;

Desired result:

generated table: 
zmogaus_id=3, hobio_id=1, hobio_pav2=name1;
zmogaus_id=3, hobio_id=3, hobio_pav2=name3;

Got result:

generated table: 
zmogaus_id=3, hobio_id=1, hobio_pav2= ;
zmogaus_id=3, hobio_id=3, hobio_pav2= ;
Tags:

3

Answers


  1. 0

    Are you looking for coalesce()?

    SELECT zh.zmogaus_id, coalesce(h.hobio_id, zh.hobio_pav2)
FROM zmogaus_hobiai zh LEFT JOIN
     hobiai h
     ON zh.hobio_pav2 = h.hobio_pav
WHERE zh.zmogaus_id = 3;
    Login or Signup to reply.
  2. 0

    If you want to update

    zmogaus_hobiai 

    UPDATE zmogaus_hobiai zh INNER JOIN hobiai h
    ON zh.hobio_id=h.id
    SET zh.hobio_pav2 = h.hobio_pav
    WHERE zmogaus_id=3;

    And your SELECT would look like this

    SELECT zmogaus_id, h.hobio_id, hobio_pav
FROM zmogaus_hobiai  zh
INNER JOIN hobiai h
ON zh.hobio_id=h.id
WHERE zmogaus_id=3:
    Login or Signup to reply.
  3. 0

    From your sample data, it looks like you just need to fix your join condition – you should be joining on ids rather than on those other string columns:

    SELECT z.zmogaus_id, z.hobio_id, h.hobio_pav2
FROM zmogaus_hobiai z
LEFT JOIN hobiai h ON h.id = z.zmogaus_id
WHERE z.zmogaus_id = 3

    Note that I added table aliases and prefixed all columns with the table they belong to. This makes the query easier to read, and avoids ambiguity when different tables have homonym columns.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search