troubleshooting Question

MySQL query with multiple JOIN syntax...

Avatar of ullenulle
ullenulleFlag for United States of America asked on
DatabasesMySQL Server
7 Comments1 Solution123 ViewsLast Modified:
Hi there.

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

Table_main:
IDa    var1    var2    var3    var3    IDb    IDc    IDd

And the related table:

Table_rel_x:
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)
FROM 
((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
GROUP BY IDa

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

Ulrich
Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 1 Answer and 7 Comments.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 7 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros