Avatar of ullenulle
Flag for United States of America

asked on 

MySQL query with multiple JOIN syntax...

Hi there.

I have a MySQL database. There is some tables with related content, and they have related id's like this:

IDa    var1    var2    var3    var3    IDb    IDc    IDd

And the related table:

IDx    var1x    var2x    var3x

The IDx holds multiple values in IDb,  IDc  and  IDd from the main table.

I want to create a query with info from the tables like this:

IDa    var1    var2    var3    IDb    MAX(var2x)     IDc     MAX(var2x)    IDd   MAX(var2x)  

where MAX(var2x) must be for the ID on the left hand only. Of course I use an alias to keep them separated.

I tried the concept like:

SELECT IDa, var1, var2, var3, Table_rel_x.IDb, MAX(j1.var2x), Table_rel_x.IDc, MAX(j2.var2x), Table_rel_x.IDd, MAX(j3.var2x)
((Table_main j1 LEFT JOIN Table_rel_x j1 ON Table_main.IDb=j1.IDb)
LEFT JOIN Table_rel_x j2 ON Table_main.IDc=j2.IDb)
LEFT JOIN Table_rel_x j3 ON Table_main.IDd=j3.IDb

Open in new window

But something goes wrong with the result. When a row hold values for 2 or  more of MAX(j1.var2x) and MAX(j2.var2x) and MAX(j3.var2x), then they're the same, but when I check the source, they're not necessarily the same.
What am I doing wrong in the JOIN/structure?
I hope you can help me out. :-)

Best regards

DatabasesMySQL Server

Avatar of undefined
Last Comment

8/22/2022 - Mon