Get row from inner select qnd by group -MYSQL

177
January 28, 2018, at 05:29 AM

Students table with 500,000- 1,000,000 rows

    |-----------------|------------------|------------------|
    |    id           |     name         |    ref_id        |
    |-----------------|------------------|------------------|
    |         1       |        test      |    NULL          |
    |-----------------|------------------|------------------|       
    |          2      |        test      |    1             |
    |-----------------|------------------|------------------|         
    |           3     |         test3    |    1             |
    |-----------------|------------------|------------------|
    |           4     |         test4    |    NULL          |

How do I query the following case: return the rows that id in that row does not exist as any ref_id of the table and also only if the name appears more than 1

if in the table row id = 2 does not appear as any ref_id in another row ("2" does not appear as ref_id I should choose him and also the second condition is to took his name and check id the name is unique or not)

so in the above example, the rows to return is rows with id 2 . because it does not appear as a ref_if and also the name is more than 1.

row 3 is not good because the name test3 appears only 1

I tried:

SELECT st.id FROM students at
WHERE at.id NOT IN (SELECT stt.ref_id FROM students stt)
Answer 1

First get the list of id, which is not present in ref_id

SELECT st.id FROM students at
WHERE NOT EXIST (SELECT * FROM students stt where stt.ref_id=at.id)

Then get the list of id where name is repeated multiple times

SELECT id, name FROM student n1
inner join student n2 on n2.name=n1.name where n1.id <> n2.id

Now, join these two queries together

SELECT t1.id FROM
(SELECT st.id FROM students at
WHERE NOT EXIST (SELECT * FROM students stt where stt.ref_id=at.id)) t1
INNER JOIN
(SELECT id, name FROM student n1
inner join student n2 on n2.name=n1.name where n1.id <> n2.id
) t2 on t1.id=t2.id

Please note, I haven't ran this query, but idea remain same. You might have to correct something

Rent Charter Buses Company
READ ALSO
Mysql query for all subcategories in json format - Opencart tables

Mysql query for all subcategories in json format - Opencart tables

I have the following query for getting categories in json format with subcategories up to two levels from standard Opencart tables

241
Call Servlet and invoke Java code from JavaScript along with parameters

Call Servlet and invoke Java code from JavaScript along with parameters

I have session key that is a JavaScript variable which I got from a REST API callI need to call my Java code in a servlet and pass that key as a parameter

185