MYSQL outputting nested data

Question

Hi I am having trouble getting nested results in my output table.

SELECT *, users.Name FROM person WHERE person.User IN
( 
    SELECT Id FROM users WHERE users.Id = "0014082f-5e17-4eaa-aebc-a22800c59ccc"
)

In this example i want to select next to the data from person the Username of the nested table users. this piece of code gives the following error

 #1054 - Unknown column 'users.Name' in 'field list'

but what can i do to fix it?

EDIT:

i tried to simplify things but it only made my question more vague This is the original code with the JOIN tip from the awnsers

SELECT * FROM behandelaars LEFT JOIN
( 
    SELECT Id, Name FROM users WHERE users.Id 
    IN(
        SELECT User FROM usersinrole WHERE Role IN (
            SELECT Id FROM roles WHERE Name = "BeloningHintingSysteem"
        )
    )
    AND users.Id NOT IN(
        SELECT User FROM usersinrole WHERE Role IN (
            SELECT Id FROM roles WHERE Name = "UitgelslotenVanOnderzoek"
        )
    )
)
ON behandelaars.User = users.Id

thx Matthy

Try using join queries in stead of filtering with IN(/* subquery */). It takes a bit of adjustment in the way you think but in the long run it's very useful. — Mihai Stancu
– Mihai Stancu, Commented Apr 24, 2014 at 14:58
It simply means that Name column doesn't exist in users table — Parag Tyagi
– Parag Tyagi, Commented Apr 24, 2014 at 15:08
@ParagTyagi more likely it means that the users table name is not visible in the main query. — Mihai Stancu
– Mihai Stancu, Commented Apr 24, 2014 at 15:15
thanks all for the comments the main problem why i am using the IN parameter now is because i think the nesting is going to get deeper. i migth have over simplified the example. i will try to improve the example — matthy
– matthy, Commented Apr 24, 2014 at 15:17
@Matthy: If you want fields from both the table, use JOIN. Check my answer if it works — Parag Tyagi
– Parag Tyagi, Commented Apr 24, 2014 at 15:17

Parag Tyagi · Accepted Answer · 2014-04-24 15:03:53Z

1

Try JOIN -

SELECT person.*, users.Name FROM person 
INNER JOIN users ON person.User = users.Id
WHERE users.Id = "0014082f-5e17-4eaa-aebc-a22800c59ccc"

answered Apr 24, 2014 at 15:03

Parag Tyagi

9,0003 gold badges45 silver badges50 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

matthy Over a year ago

i tried it but it give an error #1248 - Every derived table must have its own alias i am probarly doing something very wrong i added the code in my original post

Mihai Stancu · Accepted Answer · 2014-04-24 15:09:22Z

1

I'm pretty sure sub-queries with IN / ANY / SOME don't allow using the sub-queries tables outside of the sub-query.

In your particular case the sub-query is very simple and can easily be joined directly to the main query:

SELECT person.*, users.Name
    FROM person 
    LEFT JOIN users
      ON person.User = users.Id
    WHERE users.Id = "0014082f-5e17-4eaa-aebc-a22800c59ccc"

If the sub-query gets more complicated or requires grouping / aggregates that the main query doesn't require you can join a subquery:

SELECT person.*, a_new_alias_for_users.Name
    FROM person 
    LEFT JOIN (
        SELECT Name
            FROM users
            WHERE users.Id = "0014082f-5e17-4eaa-aebc-a22800c59ccc"

    ) AS a_new_alias_for_users
    WHERE person.some_column <> 2;

answered Apr 24, 2014 at 15:09

Mihai Stancu

16.2k2 gold badges35 silver badges51 bronze badges

3 Comments

matthy Over a year ago

thank you for the comments the main problem why i am using the IN parameter now is because i think the nesting is going to get deeper. i migth have over simplified the example

Mihai Stancu Over a year ago

Joins are better at this than sub-queries. All SQL engies have been optimizing joins for the past few decades -- sub-query optimizations came later.

Mihai Stancu Over a year ago

You are thinking in a very procedural way, try learning more about how joins work and it will change how you approach the problem.

yankov92 · Accepted Answer · 2014-04-24 14:58:32Z

0

Your last query returns ID, and you are comparing Role with ID.

answered Apr 24, 2014 at 14:58

yankov92

12 bronze badges

Collectives™ on Stack Overflow

MYSQL outputting nested data

3 Answers 3

1 Comment

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related