CODEWITHSUNDEEP
mysqljoinsubquery
Hydra IO
Hydra IO

Reputation: 1557

MySQL Subquery (or join?) to Select value of another tables ID

I have taken a look at the following answer: Select rows from a table where row in another table with same id has a particular value in another column

Which did an OK job in confusing me as far as sub-queries.

My table looks like this:

Field   Type
------  ------------
id      int(11)
parent  int(11)
title   varchar(255)
uri     varchar(255)
perms   varchar(255)

What I am trying to do, is select all my records and show parent and parent_name (if parent exists.

So an example output, If i had two rows

foo id of 1 & bar id of 2 and child of 1

foo null bar foo 1

My sql looks like this:

SELECT id,parent,title,uri,perms 
FROM ls_menu_manager
WHERE parent IN (
SELECT title AS parent_name
FROM ls_menu_manager
WHERE parent=id
)

But does not work intended. I think I have gone and confused myself any help would be appreciated.

Upvotes: 0

Views: 661

Answers (1)

crowne
crowne

Reputation: 8534

It looks to me like you should try using a LEFT JOIN

SELECT ch.id, ch.parent, ch.title, ch.uri, ch.perms, pr.title as parent_name
  FROM ls_menu_manager ch
  left join ls_menu_manager pr
    on pr.id = ch.parent

This will display all rows in ls_menu_manager, and their parent name when one is present.
If no parent is defined for a particular row, then the parent_name will show as null.
If you want to do this only for rows that have a parent, then change the left join to a natural join, by removing the left keyword.

SELECT ch.id, ch.parent, ch.title, ch.uri, ch.perms, pr.title as parent_name
  FROM ls_menu_manager ch
  join ls_menu_manager pr
    on pr.id = ch.parent

Upvotes: 1

Related Questions