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

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

See answer

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