LEFT JOIN subquery

Hi,

The following is a simplified version of a complex query i wrote.

SELECT table2.x, table2.y
FROM table1
LEFT JOIN (SELECT table3.x, max(table3.y)
                FROM table3
                 GROUP BY table3.x) AS table2
         ON table1.x = table2.x


and it works... but it is slower since the subquery fetches whole table again and again.
Is there any way to make this faster by adding a condition inside subquery...
I want to do something like this.

SELECT table2.x, table2.y
FROM table1
LEFT JOIN (SELECT table3.x, max(table3.y)
                FROM table3
                 WHERE  table3.x = table1.x    <<<<<<------------- does not work // Unknown table 'table1' in where clause
                 GROUP BY table3.x) AS table2
         ON table1.x = table2.x

Any alternative query is welcome.

Thanks
LVL 12
venkateshwarrAsked:
Who is Participating?
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.

Aneesh RetnakaranDatabase AdministratorCommented:
I dont find any erorrs there, check whether 'Table1' already exists on your database
0
HuyBDCommented:
Hi
0
HuyBDCommented:
Hi venkateshwarr!
To avoid use sub query, try this:

SELECT table3.x, max(table3.y)
from table3
left join table1 on table3.x=table1.x
GROUP BY table3.x
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
Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

venkateshwarrAuthor Commented:
Hi aneesh,

I am not sure about other databases, but it does not work in mysql.

HuyBD,

As I said before, this is a pretty complex query and I cant do what u said.
0
LowsanCommented:
Yes use of table1 in subquery is suported for example in oracle but in mysql won't work

SELECT table2.x, table2.y
FROM table1
LEFT JOIN (SELECT table3.x, max(table3.y)
               FROM table3
                 inner join table1 using (x)
                 GROUP BY table3.x) AS table2
         ON table1.x = table2.x

wouldn't this speedup yur query if table 3 is really big and use the same where clouse in sub query and outside it?
0
todd_farmerCommented:
Have you tried this:

SELECT (SELECT table3.x, max(table3.y)
               FROM table3, table1
                 WHERE  table3.x = table1.x
                 AND table1.x=table2.x
                 GROUP BY table3.x)
FROM table1;

0
LowsanCommented:
Same result will produce:

SELECT table3.x, max(table3.y)
               FROM table3
                 inner join table1 using (x)
                 GROUP BY table3.x

but meabye you were looking for something like this:

SELECT table2.x, table2.y
FROM table1
LEFT JOIN (SELECT table3.x, max(table3.y)
               FROM table3
                 WHERE table3.x IN (SELECT table1.x FROM table1)
                 GROUP BY table3.x) AS table2
         ON table1.x = table2.x

or to not call queries on table1 twice you could create temporary table with records from table1 you want to use

create temporary temp_table1
      SELECT table1.x FROM table1
       WHERE..........

then just:

SELECT table2.x, table2.y
FROM temp_table1
LEFT JOIN (SELECT table3.x, max(table3.y)
               FROM table3
                 WHERE table3.x IN (SELECT temp_table1.x FROM temp_table1)
                 GROUP BY table3.x) AS table2
         ON temp_table1.x = table2.x

Will this be quicker enough?
0
venkateshwarrAuthor Commented:
Thanks for all ur suggestions.. I am increasing to 500 pts.
0
venkateshwarrAuthor Commented:
LEFT JOIN with subquery is slow...
I am accepting HuyBD as it is faster compared to other solutions.
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
MySQL Server

From novice to tech pro — start learning today.

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.