Get row from inner select qnd by group -MYSQL
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)
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
-
05:30
-
04:00
-
00:00
-
10:30
-
9:10
-
07:40
Firebase Cloud Functions: PubSub, "res.on is not a function"
-
04:00
TypeError: Cannot read properties of undefined (reading 'createMessageComponentCollector')
-
9:20
-
7:40
- typescript: tsc is not recognized as an internal or external command, operable program or batch file 111570 visits
- search for elements in a list 111065 visits
- In Chrome 55, prevent showing Download button for HTML 5 video 98826 visits
- RxJS5 - error - TypeError: You provided an invalid object where a stream was expected 96332 visits
- Ionic 2 - how to make ion-button with icon and text on two lines? 92528 visits
- NetBeans IDE - ClassNotFoundException: net.ucanaccess.jdbc.UcanaccessDriver 62042 visits
- Adding methods to es6 child class 22376 visits
