Jump to content
JDevOnline

Help With Sub/Nested Query

Recommended Posts

Hi,

I am seeking help with the following scenario:

I have a mysql db with a table to store students info with following fields:

id, sname, ctclass, shift, siblingof, elderof, ctstudent (sname=Student's name, ctstudent=Y/N, siblingof/elder of=id of sibling or elder in the same table.)

Every child who has a brother or sister either has a sibling or elder therefore siblingof or elderof store ids of either sibling or elder in the same table.

What I want to do is to run a query to select only current children with elders AND also want to find out the status of elders, whether they are current student of have left. This info is stored in "Y or N" in the column name called "ctstudent".

I've created the following code but is not showing any result.

SELECT id, sname, ctclass, shift ctstudent FROM nroll 
WHERE siblingof <> '' and ctstudent IN (SELECT ctstudent as ct FROM nroll where id=siblingof)

 

Edited by JDevOnline

Share this post


Link to post
Share on other sites

And if a child has a brother and a sister (or two or more of any gender)?

[edit]

There's this method, but it doesn't show explicitly that there is also a relationship between Curly and Mo too.

SELECT * FROM jdev_nroll;

+----+--------+---------+-------+-----------+---------+-----------+
| id | sname  | ctclass | shift | siblingof | elderof | ctstudent |
+----+--------+---------+-------+-----------+---------+-----------+
|  1 | Curly  |       1 |     0 |         2 |         | Y         |
|  2 | Larry  |       1 |     0 |         3 |       1 | N         |
|  3 | Mo     |       1 |     0 |           |       2 | Y         |
|  4 | Peter  |       1 |     0 |           |       5 | N         |
|  5 | Paul   |       1 |     0 |         4 |         | Y         |
|  6 | Mary   |       1 |     0 |           |         | N         |
|  7 | Jane   |       1 |     0 |           |         | Y         |
|  8 | John   |       1 |     0 |         9 |         | N         |
|  9 | George |       1 |     0 |        10 |       8 | Y         |
| 10 | Ringo  |       1 |     0 |           |       9 | N         |
+----+--------+---------+-------+-----------+---------+-----------+

SELECT a.id
     , a.sname
     , a.ctclass
     , a.shift
     , a.ctstudent
     , b.id as bid
     , b.sname as bname
     , b.ctstudent as bstudent
FROM jdev_nroll a 
     JOIN
     jdev_nroll b ON a.siblingof = b.id;

+----+--------+---------+-------+-----------+----+--------+-----------+
| id | sname  | ctclass | shift | ctstudent |bid | bname  |  bstudent |
+----+--------+---------+-------+-----------+----+--------+-----------+
|  1 | Curly  |       1 |     0 | Y         |  2 | Larry  | N         |
|  2 | Larry  |       1 |     0 | N         |  3 | Mo     | Y         |
|  5 | Paul   |       1 |     0 | Y         |  4 | Peter  | N         |
|  8 | John   |       1 |     0 | N         |  9 | George | Y         |
|  9 | George |       1 |     0 | Y         | 10 | Ringo  | N         |
+----+--------+---------+-------+-----------+----+--------+-----------+

 

Edited by Barand
  • Great Answer 1

Share this post


Link to post
Share on other sites

Alternative model which allows multiple siblings

jdev_nroll;                                                         jdev_sibling;
+----+--------+---------+-------+-----------+------------+          +------------+----------+
| id | sname  | ctclass | shift | ctstudent | dob        |          | sibling_id | elder_id |
+----+--------+---------+-------+-----------+------------+          +------------+----------+
|  1 | Curly  |       1 |     0 | N         | 2007-01-20 |          |          2 |        1 |
|  2 | Larry  |       1 |     0 | Y         | 2010-12-21 |          |          3 |        1 |
|  3 | Mo     |       1 |     0 | Y         | 2011-02-22 |          |          3 |        2 |
|  4 | Peter  |       1 |     0 | N         | 2009-01-03 |          |          4 |        5 |
|  5 | Paul   |       1 |     0 | N         | 2006-12-21 |          |          9 |        8 |
|  6 | Mary   |       1 |     0 | Y         | 2010-09-20 |          |          9 |       10 |
|  7 | Jane   |       1 |     0 | N         | 2008-03-08 |          |         10 |        8 |
|  8 | John   |       1 |     0 | N         | 2006-10-04 |          +------------+----------+
|  9 | George |       1 |     0 | Y         | 2010-10-26 |
| 10 | Ringo  |       1 |     0 | Y         | 2009-11-15 |
+----+--------+---------+-------+-----------+------------+

SELECT a.id as sibling_id
     , a.sname as sibling_name
     , TIMESTAMPDIFF(YEAR,a.dob,curdate()) as sibling_age
     , a.ctclass as class
     , b.id as elder_id
     , b.sname as elder_name
     , TIMESTAMPDIFF(YEAR,b.dob,curdate()) as elder_age
     , b.ctstudent as elder_ctstudent
FROM jdev_nroll a 
     JOIN
     jdev_sibling s ON a.id = s.sibling_id
     JOIN
     jdev_nroll b ON s.elder_id = b.id
WHERE a.ctstudent = 'Y'    
ORDER BY a.id

+------------+--------------+-------------+-------+----------+------------+-----------+-----------------+
| sibling_id | sibling_name | sibling_age | class | elder_id | elder_name | elder_age | elder_ctstudent |
+------------+--------------+-------------+-------+----------+------------+-----------+-----------------+
|          2 | Larry        |           9 |     1 |        1 | Curly      |        13 | N               |
|          3 | Mo           |           8 |     1 |        1 | Curly      |        13 | N               |
|          3 | Mo           |           8 |     1 |        2 | Larry      |         9 | Y               |
|          9 | George       |           9 |     1 |        8 | John       |        13 | N               |
|          9 | George       |           9 |     1 |       10 | Ringo      |        10 | Y               |
|         10 | Ringo        |          10 |     1 |        8 | John       |        13 | N               |
+------------+--------------+-------------+-------+----------+------------+-----------+-----------------+

 

  • Great Answer 2

Share this post


Link to post
Share on other sites
On 1/20/2020 at 11:10 AM, Barand said:

And if a child has a brother and a sister (or two or more of any gender)?

[edit]

There's this method, but it doesn't show explicitly that there is also a relationship between Curly and Mo too.


SELECT * FROM jdev_nroll;

+----+--------+---------+-------+-----------+---------+-----------+
| id | sname  | ctclass | shift | siblingof | elderof | ctstudent |
+----+--------+---------+-------+-----------+---------+-----------+
|  1 | Curly  |       1 |     0 |         2 |         | Y         |
|  2 | Larry  |       1 |     0 |         3 |       1 | N         |
|  3 | Mo     |       1 |     0 |           |       2 | Y         |
|  4 | Peter  |       1 |     0 |           |       5 | N         |
|  5 | Paul   |       1 |     0 |         4 |         | Y         |
|  6 | Mary   |       1 |     0 |           |         | N         |
|  7 | Jane   |       1 |     0 |           |         | Y         |
|  8 | John   |       1 |     0 |         9 |         | N         |
|  9 | George |       1 |     0 |        10 |       8 | Y         |
| 10 | Ringo  |       1 |     0 |           |       9 | N         |
+----+--------+---------+-------+-----------+---------+-----------+

SELECT a.id
     , a.sname
     , a.ctclass
     , a.shift
     , a.ctstudent
     , b.id as bid
     , b.sname as bname
     , b.ctstudent as bstudent
FROM jdev_nroll a 
     JOIN
     jdev_nroll b ON a.siblingof = b.id;

+----+--------+---------+-------+-----------+----+--------+-----------+
| id | sname  | ctclass | shift | ctstudent |bid | bname  |  bstudent |
+----+--------+---------+-------+-----------+----+--------+-----------+
|  1 | Curly  |       1 |     0 | Y         |  2 | Larry  | N         |
|  2 | Larry  |       1 |     0 | N         |  3 | Mo     | Y         |
|  5 | Paul   |       1 |     0 | Y         |  4 | Peter  | N         |
|  8 | John   |       1 |     0 | N         |  9 | George | Y         |
|  9 | George |       1 |     0 | Y         | 10 | Ringo  | N         |
+----+--------+---------+-------+-----------+----+--------+-----------+

 

Dear Barand this perfectly solves my problem. I will consider adopting the suggested alternative model which will allow multiple siblings.

 

Thank you very much!

Share this post


Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.


×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.