Jump to content

Help With Sub/Nested Query


JDevOnline

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
Link to comment
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
Link to comment
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
Link to comment
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!

Link to comment
Share on other sites

  • 2 months later...

Hello Barand,

I have one similar scenario. I have a table that stores form data into rows instead of columns. In the attached image, the column "cf7_id "is the form id and "data_id" is the for data submitted. I tried many variations but haven't been able to devise a query to fetch the data of each submission as a record set to be inserted into another table. I only want to extract comma separated values from the "value" column as a record set.

Following is the query I had written:

SELECT * FROM wp_cf7_vdata_entry WHERE data_id in (SELECT data_id FROM wp_cf7_vdata_entry GROUP BY data_id HAVING COUNT(data_id) > 1)

 

 

screenshot-localhost-2020_04.03-09_34_41.png.270b6917e39b1914154a3298d69b607f.png

Link to comment
Share on other sites

Are you trying to get to this ...

+-----------+---------------+--------------------+-------------------+-------------------+---------------------+---------------+
| data_id   | name          | email              | subject           | message           | time                |   ip_address  |
+-----------+---------------+--------------------+-------------------+-------------------+---------------------+---------------+
|     1     | John Dow      | John Dow@gmail.com | Hello             | I have no message | 2020-04-03 03:19:56 |      ::1      |
|     2     | Stephen       | Stephen@gmail.com  | Hello             | I have no message | 2020-04-03 03:28:33 |      ::1      |
+-----------+---------------+--------------------+-------------------+-------------------+---------------------+---------------+

 

Link to comment
Share on other sites

Hi Barand,

I am soooo grateful for your kind assistance 😀😀😀 , your query has completed the tasks table creation and loading data from source table.

Now, can you please help me with loading future records from the source table into the table already created by your query. I've added id (primary, unique, auto increment) column at the beginning of the table.

 

 

Link to comment
Share on other sites

Hi Barand,

FYI I've changed the names and structure of both the tables, now I am using the following modified version of your query and also added the id column in the target table "monthly_fee", as mentioned earlier.

CREATE TABLE monthly_fee 
SELECT DISTINCT 
lead_id, 
cname.value as cname,
chno.value as chno,
mode.value as mode,
email.value as email,
mobile.value as mobile,
amount.value as amount,
omode.value as omode,
tid.value as tid,
date.value as date,
comments.value as comments  

FROM wp_vxcf_leads_detail 
LEFT JOIN(SELECT lead_id, value FROM wp_vxcf_leads_detail WHERE name='cname') cname USING (lead_id) 
LEFT JOIN(SELECT lead_id, value FROM wp_vxcf_leads_detail WHERE name = 'chno') chno USING (lead_id) 
LEFT JOIN(SELECT lead_id, value FROM wp_vxcf_leads_detail WHERE name='mode') mode USING(lead_id) 
LEFT JOIN(SELECT lead_id, value FROM wp_vxcf_leads_detail WHERE name='email') email USING (lead_id)
LEFT JOIN(SELECT lead_id, value FROM wp_vxcf_leads_detail WHERE name='mobile') mobile USING(lead_id)
LEFT JOIN(SELECT lead_id, value FROM wp_vxcf_leads_detail WHERE name='amount') amount USING(lead_id)
LEFT JOIN(SELECT lead_id, value FROM wp_vxcf_leads_detail WHERE name='omode') omode USING(lead_id)
LEFT JOIN(SELECT lead_id, value FROM wp_vxcf_leads_detail WHERE name='tid') tid USING(lead_id)
LEFT JOIN(SELECT lead_id, value FROM wp_vxcf_leads_detail WHERE name='date') date USING(lead_id)
LEFT JOIN(SELECT lead_id, value FROM wp_vxcf_leads_detail WHERE name='comments') comments USING(lead_id)

 

 

 

Link to comment
Share on other sites

Barand I thought lead_id could be used to determine the order of entries, however, it would be great if we could get the timestamp which is stored in another table called "wp_vxcf_leads". Following is the structures and data stored into this the second table.2096207950_structureofleadstable.thumb.png.bfa29ed9515c163b58dc27c7fe2cdd9a.png

 

screenshot-localhost-2020_04.05-01_22_15.thumb.png.7c6c924c826c1188fa9d7d515fa027db.png

Link to comment
Share on other sites

We are now dealing with following 2 tables:

1. Source Table: wp_vxcf_leads_detail
3. Additional Table: wp_vxcf_leads

These tables don't have a declared relationship however the "id" field of "Additional Table" is being used as the "lead_id" in source table.

Edited by JDevOnline
Link to comment
Share on other sites

So it looks like the "lead_id" in the target table will be unique (and could be the primary key). As you have an auto primary key, set a UNIQUE constraint on lead_id in the target table.

Change the "CREATE TABLE monthly_fee" to ...

INSERT IGNORE INTO monthly_fee (image.png.394a6421681d9f00368f3d46901e2058.png)

SELECT .....(as now)

See how that goes. I can't test as I have no data (too time consuming typing it all out again from your pictures) so you'll have to test for me.

Link to comment
Share on other sites

Hi Barand, once again your query has worked flawlessly and as desired!

If not very time consuming, can you please alter the following query to get the timestamp from the table "wp_vxcf_leads", the field name is "created".

 

INSERT IGNORE INTO monthly_fee
SELECT DISTINCT 
lead_id, 
cname.value as cname,
chno.value as chno,
mode.value as mode,
email.value as email,
mobile.value as mobile,
amount.value as amount,
omode.value as omode,
tid.value as tid,
date.value as date,
comments.value as comments  

FROM wp_vxcf_leads_detail 
LEFT JOIN(SELECT lead_id, value FROM wp_vxcf_leads_detail WHERE name='cname') cname USING (lead_id) 
LEFT JOIN(SELECT lead_id, value FROM wp_vxcf_leads_detail WHERE name = 'chno') chno USING (lead_id) 
LEFT JOIN(SELECT lead_id, value FROM wp_vxcf_leads_detail WHERE name='mode') mode USING(lead_id) 
LEFT JOIN(SELECT lead_id, value FROM wp_vxcf_leads_detail WHERE name='email') email USING (lead_id)
LEFT JOIN(SELECT lead_id, value FROM wp_vxcf_leads_detail WHERE name='mobile') mobile USING(lead_id)
LEFT JOIN(SELECT lead_id, value FROM wp_vxcf_leads_detail WHERE name='amount') amount USING(lead_id)
LEFT JOIN(SELECT lead_id, value FROM wp_vxcf_leads_detail WHERE name='omode') omode USING(lead_id)
LEFT JOIN(SELECT lead_id, value FROM wp_vxcf_leads_detail WHERE name='tid') tid USING(lead_id)
LEFT JOIN(SELECT lead_id, value FROM wp_vxcf_leads_detail WHERE name='date') date USING(lead_id)
LEFT JOIN(SELECT lead_id, value FROM wp_vxcf_leads_detail WHERE name='comments') comments USING(lead_id)

 

Thanks a lot! 😃

Link to comment
Share on other sites

Try this

INSERT IGNORE INTO monthly_fee
SELECT DISTINCT 
lead_id, 
cname.value as cname,
chno.value as chno,
mode.value as mode,
email.value as email,
mobile.value as mobile,
amount.value as amount,
omode.value as omode,
tid.value as tid,
date.value as date,
comments.value as comments  
wp_vxcf_leads.created                                                                                                 -- added

FROM wp_vxcf_leads_detail
JOIN wp_vxcf_leads ON wp_vxcf_leads_detail.lead_id = wp_vxcf_leads.id                                                 -- added
LEFT JOIN(SELECT lead_id, value FROM wp_vxcf_leads_detail WHERE name='cname') cname USING (lead_id) 
LEFT JOIN(SELECT lead_id, value FROM wp_vxcf_leads_detail WHERE name = 'chno') chno USING (lead_id) 
LEFT JOIN(SELECT lead_id, value FROM wp_vxcf_leads_detail WHERE name='mode') mode USING(lead_id) 
LEFT JOIN(SELECT lead_id, value FROM wp_vxcf_leads_detail WHERE name='email') email USING (lead_id)
LEFT JOIN(SELECT lead_id, value FROM wp_vxcf_leads_detail WHERE name='mobile') mobile USING(lead_id)
LEFT JOIN(SELECT lead_id, value FROM wp_vxcf_leads_detail WHERE name='amount') amount USING(lead_id)
LEFT JOIN(SELECT lead_id, value FROM wp_vxcf_leads_detail WHERE name='omode') omode USING(lead_id)
LEFT JOIN(SELECT lead_id, value FROM wp_vxcf_leads_detail WHERE name='tid') tid USING(lead_id)
LEFT JOIN(SELECT lead_id, value FROM wp_vxcf_leads_detail WHERE name='date') date USING(lead_id)
LEFT JOIN(SELECT lead_id, value FROM wp_vxcf_leads_detail WHERE name='comments') comments USING(lead_id)

 

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

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.