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:

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

OMC2000

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

OMC2000

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

ullenulle

ASKER

Hi.

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

Best regards

Ulrich

ullenulle

ASKER

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...

ASKER CERTIFIED SOLUTION

OMC2000

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ullenulle

ASKER

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

ullenulle

ASKER

Thank you. :-)