Help With Sub/Nested Query

[JDevOnline]

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 January 20, 2020 by JDevOnline

[Barand]

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 January 20, 2020 by Barand

[Barand]

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               |
+------------+--------------+-------------+-------+----------+------------+-----------+-----------------+

 

[JDevOnline]

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!

[JDevOnline]

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)

 

 

[Barand]

Are you trying to get to this ...

+-----------+---------------+--------------------+-------------------+-------------------+---------------------+---------------+
| data_id   | name          | email              | subject           | message           | time                |   ip_address  |
+-----------+---------------+--------------------+-------------------+-------------------+---------------------+---------------+
|     1     | John Dow      | John [email protected] | Hello             | I have no message | 2020-04-03 03:19:56 |      ::1      |
|     2     | Stephen       | [email protected]  | Hello             | I have no message | 2020-04-03 03:28:33 |      ::1      |
+-----------+---------------+--------------------+-------------------+-------------------+---------------------+---------------+

 

[JDevOnline]

Yes Sir, that is exactly what I want to do.

[Barand]

EAV models are always a PITA. Here's how I would do it.

(I would actually write a function which accepts an array of attributes you want and generate the query in the function)

[JDevOnline]

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.

 

 

[JDevOnline]

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)

 

 

 

[JDevOnline]

Source Table Structure

[JDevOnline]

Target Table Structure

[Barand]

2 hours ago, JDevOnline said:

can you please help me with loading future records from the source table into the table already created by your query

With which bit do you need help ?

[JDevOnline]

I would like to use the second one, the modified version of your query. What I want to is to load only the new records from the source table without overwriting the ones already loaded in the target table, thanks.

[Barand]

Timestamp the source data records. Then you know when they were added. If it was after the last time you extracted data then they're new ones.

[JDevOnline]

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.

 

[JDevOnline]

Structure and data of table "wp_vxcf_leads_detail", the source table.

 

Edited April 4, 2020 by JDevOnline

[JDevOnline]

Target table, created and populated by the modified query.

[Barand]

How does wp_vxcf_leads relate to the source table?

[JDevOnline]

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 April 4, 2020 by JDevOnline

[Barand]

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 ()

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.

[JDevOnline]

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! 😃

[Barand]

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)

 

[JDevOnline]

Hi Barand,

That last query worked perfectly and successfully concluded my issue. I am soooooo thankful to you for you kind assistance. 😁😁😁

Kind regards.