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:

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

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

Ulrich
ullenulleAsked:
Who is Participating?
 
OMC2000Commented:
j1 after Table_main was incorrect. Query should be like the following

SELECT IDa, var1, var2, var3, IDb, MaxB, IDc, MaxC, IDd, MaxD
FROM 
Table_main 
LEFT JOIN (select IDb, MAX(var2x) MaxB FROM Table_rel_x GROUP BY IDb) j1 ON Table_main.IDb=j1.IDb
LEFT JOIN (select IDb, MAX(var2x) MaxC FROM Table_rel_x GROUP BY IDb) j2 ON Table_main.IDc=j2.IDb
LEFT JOIN (select IDb, MAX(var2x) MaxD FROM Table_rel_x GROUP BY IDb) j3 ON Table_main.IDd=j3.IDb

Open in new window


If it still does not work try to check it for selected values, take a row from table_main and check query parts with values from this row.
Assume this row has the following values
IDa = 1
IDb = 2
IDc = 3
IDd = 4

then you could check results for the following subqueries

SELECT IDa, var1, var2, var3, IDb,  IDc, IDd
FROM 
Table_main 
where IDa = 1

Open in new window


select IDb, MAX(var2x) MaxB FROM Table_rel_x WHERE IDb = 2 GROUP BY IDb

Open in new window

select IDb, MAX(var2x) MaxB FROM Table_rel_x WHERE IDb = 3 GROUP BY IDb

Open in new window

select IDb, MAX(var2x) MaxB FROM Table_rel_x WHERE IDb = 4 GROUP BY IDb

Open in new window


The next step could be join of these queries with these filters.

SELECT IDa, var1, var2, var3, IDb, MaxB, IDc, MaxC, IDd, MaxD
FROM 
Table_main 
LEFT JOIN (select IDb, MAX(var2x) MaxB FROM Table_rel_x WHERE IDb = 2 GROUP BY IDb) j1 ON Table_main.IDb=j1.IDb
LEFT JOIN (select IDb, MAX(var2x) MaxC FROM Table_rel_x WHERE IDb = 3 GROUP BY IDb) j2 ON Table_main.IDc=j2.IDb
LEFT JOIN (select IDb, MAX(var2x) MaxD FROM Table_rel_x WHERE IDb = 4 GROUP BY IDb) j3 ON Table_main.IDd=j3.IDb
WHERE IDa = 1

Open in new window


If these debugging steps won't help let me know
0
 
OMC2000Commented:
try this

SELECT IDa, var1, var2, var3, IDb, MaxB, IDc, MaxC, IDd, MaxD
FROM 
Table_main j1 
LEFT JOIN (select IDb, MAX(var2x) MaxB FROM Table_rel_x GROUP BY IDa) j1 ON Table_main.IDb=j1.IDb
LEFT JOIN (select IDb, MAX(var2x) MaxC FROM Table_rel_x GROUP BY IDa) j2 ON Table_main.IDc=j2.IDb
LEFT JOIN (select IDb, MAX(var2x) MaxD FROM Table_rel_x GROUP BY IDa) j3 ON Table_main.IDd=j3.IDb

Open in new window

0
 
OMC2000Commented:
Ups, changed IDa to IDb

SELECT IDa, var1, var2, var3, IDb, MaxB, IDc, MaxC, IDd, MaxD
FROM 
Table_main j1 
LEFT JOIN (select IDb, MAX(var2x) MaxB FROM Table_rel_x GROUP BY IDb) j1 ON Table_main.IDb=j1.IDb
LEFT JOIN (select IDb, MAX(var2x) MaxC FROM Table_rel_x GROUP BY IDb) j2 ON Table_main.IDc=j2.IDb
LEFT JOIN (select IDb, MAX(var2x) MaxD FROM Table_rel_x GROUP BY IDb) j3 ON Table_main.IDd=j3.IDb

Open in new window

0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
ullenulleAuthor Commented:
Hi.

You wrote "j1" after Table_main. The first JOIN is also j1. Isn't that a problem?

Best regards

Ulrich
0
 
ullenulleAuthor Commented:
Anyways... I tried your setup, but it still gives me the same result as I had with my codes. It is as if the JOINS doesn't pull the  correct values from the individual JOIN queries...
0
 
ullenulleAuthor Commented:
Hi.

Yes, I started doing the same. I just got back to the project and did the same as you suggested. Earlier I had the whole thing in a view, but now I had to put the query into a Stored Procedure, because WorkBench complained that there was subqueries in the script. So I get the result in a table now via a Stored Procedure.
Thank you for your help. :-)  I still can't understand though why the original setup didn't work... I gotta work more on that part.

Best regards

Ulrich
0
 
ullenulleAuthor Commented:
Thank you. :-)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.