Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – SQL query within the same table

I’m use to simple SQL queries and can’t get this one.
I have a members table that is set like so:

member_id | member_number | member_fname | member_name | member_parent_id | member_status

member_parent_id is a reference to member_id

  • I need to get all the members where the member_parent_id is not null
  • Get the member_number as an extra column for the parent for each members

So far I got results but can’t figure how to add the extra column for the parent member_number

SELECT members.member_id, member_id_parent, member_nbr, member_fname, member_name, member_note
    FROM members
    LEFT JOIN .... other table
    LEFT JOIN .... other table
    WHERE member_id_parent IS NOT NULL
    AND member_status = 1
;

I have tried some example with WITH RECURSIVE but could figure out the logic and do the proper corrections to make it work.

Thanks,

Tags:

2

Answers


  1. 0

    Use name aliases for the table. Here I use m for the member and p for the parent:

    SELECT m.member_id, m.member_id_parent, m.member_nbr, 
       m.member_fname, m.member_name, m.member_note,
       p.member_fname as parent_fname,
       p.member_name  as parent_name,
       p.member_note  as parent_note
    FROM members m
    LEFT JOIN members p ON p.member_id = m.member_id_parent
    LEFT JOIN .... other table
    LEFT JOIN .... other table
    WHERE m.member_id_parent IS NOT NULL
    AND m.member_status = 1
;
    Login or Signup to reply.
  2. 0

    Here are the steps you can use to solve this.

    1. You can create a CTE to get each member_id and member_number for all members in your table.

    2. The next step is to use the created CTE to join with the query used to fetch the parent_member_number.

    Here is a quick demo on fiddble

    WITH ParentMembers AS (
    SELECT 
        member_id, 
        member_number AS parent_member_number
    FROM 
        members
)
SELECT 
    m.member_id, 
    m.member_parent_id, 
    m.member_number, 
    m.member_fname, 
    m.member_name, 
    p.parent_member_number -- another other parent member_number
FROM 
    members m
LEFT JOIN 
    ParentMembers p ON m.member_parent_id = p.member_id
WHERE 
    m.member_parent_id IS NOT NULL
    AND m.member_status = 1;
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search