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
Firebase Cloud Functions: PubSub, "res.on is not a function"
TypeError: Cannot read properties of undefined (reading 'createMessageComponentCollector')
I have the following query for getting categories in json format with subcategories up to two levels from standard Opencart tables
I want to run this SQL:
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
I have database AI issue this command against it: