I have a structure of a table have many fields
| table_id | name_table | name_filed | PK | FK |
|---|---|---|---|---|
| 1 | person | id | pk | |
| 1 | person | name | ||
| 1 | person | age | ||
| 2 | dog | id | pk | |
| 2 | dog | name | ||
| 2 | dog | owner | fk | |
| 3 | phone | id | pk | |
| 3 | phone | name | ||
| 3 | phone | owner | fk |
How to get id(s) of parent tables which haven't any foreign key with another table in this case expected result is 1
I've tried
select distinct table_id from tables_structure where fk!=''
Also I've tried with group by
select table_id from tables_structure where fk!=''
group by table_id
having fk!=''
CodePudding user response：
The first issue is that you are comparing to an empty string, generally we expect the empty value to be represented by a null value, so try comparing using IS NULL
select distinct table_id from tables_structure where fk IS NULL
But that isn't likely to help you here, your data represents an UNPIVOT structure, your second attempt would work if you used a COUNT in your HAVING clause, here we don't even have to compare nulls because COUNT will exclude nulls for us!
select table_id
from tables_structure
group by table_id
having COUNT(fk) = 0
If the values really are empty strings, and not nulls, then we can still use count with nulls by treating '' as a null value using NULLIF:
select table_id
from tables_structure
group by table_id
having COUNT(NULLIF(fk,'')) = 0
We can't just filter by fk <> '' as that will modify the dataset and return ALL records.
You can use a SUM over a CASE statement that computes a 1 or 0 for each record, but now things are getting complicated:
select table_id
from tables_structure
group by table_id
having SUM(CASE fk WHEN '' THEN 0 ELSE 1)) = 0