How to create a nested SQL Query in MySQL?


Problem :

When writing the below query, it complains that the "so.id" column is unknown! The below query works fine in MS SQL but not in MySQL? How to write a nested select statement in MySQL?

What's the equivalent for it?

    select so.*  
from smart_objects as so
    inner join (
        SELECT  st.objectid, st.issueid
        FROM smart_targets as st            
        WHERE st.issueid != 0
            AND
            (   (st.objectid = so.id)
                OR 
                (               
                st.objectid in (
                    SELECT sor.parent 
                    FROM smart_objectrelations as sor       
                    WHERE sor.child = so.id)
                )
            )) as soi
    on soi.objectid = so.id
where so.id < 100;

I'd need to inner join a nested Select SQL basically. My select statement is more complex than above, the above is just a simplified version to explain about nested tables.

I also tried the other suggestion:

select so.*  
    from smart_objects as so
        inner join (
            SELECT  st.objectid, st.issueid
            FROM smart_targets as st, smart_objects as so           
            WHERE st.issueid != 0
                AND
                (   (st.objectid = so.id)
                    OR 
                    (               
                    st.objectid in (
                        SELECT sor.parent 
                        FROM smart_objectrelations as sor       
                        WHERE sor.child = so.id)
                    )
                )) as soi
        on soi.objectid = so.id
    where so.id < 100;

The above code goes into a never-ending loop whereas I simply want the nested sql to run for every row of the parent.

UPDATED with ANSWER:

How'd you simplify the below query?

select so.* 
from smart_objects as so
    inner join (
        SELECT  st.objectid, st.issueid
        FROM smart_targets as st, smart_objects as so           
        WHERE st.objectid = so.id
            and st.issueid != 0
            AND
            (   (st.objectid = so.id)
                OR 
                (               
                st.objectid in (
                    SELECT sor.parent 
                    FROM smart_objectrelations as sor, smart_objects as so      
                    WHERE sor.child = so.id)
                )
            )
        ) as soi
    on soi.objectid = so.id


Solution :

Try this : I guess this should work

 select so.*  
    from smart_objects as so
    inner join (SELECT  st.objectid, st.issueid FROM smart_targets as st,smart_objects as so          
        WHERE st.objectid = so.id) as soi
    on soi.objectid = so.id

Mysql Tutorials

Mysql Howto..

MySQL: How to list all with counting function and joining table?

How to make a normal user input of time and date to get it added in mysql as timestamp?

how to display data via php and MySQL?

How to find a table is used in any procedure or function or view

How to achieve this sorting described using mySQL and PHP?

Spring3, Hibernate, MySQL - How do I add transactions control?

Comet-style messaging: How to implement server part without polling?

How to: MySQL order by user_id (RAND) with pagination

How to backup and restore a large MySQL database in Hibernate?

MySQL - How to use (LEFT) JOIN to join two tables via one table together?

Java to MySQL: How to update row with a value from variable?

How do I generate a SQL script from my diagram in MySQL Workbench?

How can I find the next/previous row in a database?

Django / MySQL: How to do Autonomous Transactions (commit only sub-set of queries)?

How to alter the auto_increment of a mysql-table using Doctrine

How to get the latest set of data available out of a MySQL table (data sets are stored daily)?

How to perform MySQL query to get all the rows from different subscriptions sorted by id?

How does PHP interacts with MySQL

How to group mysql rows with same column value into one row?

How to specify the quote to use for strings in a python list for mysql?

How to save DIV tag to MySQL with PHP and jQuery

How to get result from mysql when using Count(*) As

How to print results in php using AVG and CHAR_LENGTH in MySQL Query

How to edit, delete and add in php mysql without jquery or java script?

How to turn off MySQL strict mode in Rails

How can I embed a MySQL database server inside a C# application? [duplicate]

How to remove tabs at start and end of varchar field in MySQL?

How to implement a portable MySQL and connect to it from Ruby as a non-root under Linux

How to build a dependency that calls a function of another dependency depending on the current project?

How to select from DB only 5 records. Use MySQL