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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
ullenulleAuthor Commented:
Hi.

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

Best regards

Ulrich
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Databases

From novice to tech pro — start learning today.