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

Question

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= ;

Sample data and desired results, please.

Gordon Linoff
– Gordon Linoff

2020-04-25 16:36:02 +00:00
Commented Apr 25, 2020 at 16:36 — Gordon Linoff
– Gordon Linoff, Commented Apr 25, 2020 at 16:36

nbk · Accepted Answer · 2020-04-25 17:08:02Z

1

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:

answered Apr 25, 2020 at 17:08

nbk

49.6k8 gold badges32 silver badges53 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

webnub Over a year ago

I did not want to update the table, I wanted to filter just those hobio_id that have the same zmogaus_id also, and then use that hobio_id and hobio_pav to do some stuff in php, i thought it will be easyer if i just used left join, but now i think i should just give not just hobio_id to zmogaus_hobiai but also hobio_pav. Sorry im rambling, you gave me an easier solution than waht i was looking for haha

nbk Over a year ago

no in your case is better to use INNEr JOIN and you will se my SELECT query will give you the correct result.

Gordon Linoff · Accepted Answer · 2020-04-25 16:37:41Z

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;

GMB · Accepted Answer · 2020-04-25 17:22:34Z

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.

Collectives™ on Stack Overflow

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

3 Answers 3

2 Comments

Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

Comments

Related