CODEWITHSUNDEEP
mysqlsql
user1032531
user1032531

Reputation: 26341

select self join if only one resulting row

Is it possible/economical to perform a SELF JOIN of a table (for this example, my table called myTable has two columns pk and fk), and return a record if there is only one resulting record? I am thinking of something like the following, however, only_one_row() is a fictional function that would need to be replaced with something real:

SELECT fk
FROM myTable as t1
INNER JOIN myTable AS t2 ON t2.fk=t1.fk
WHERE t1.pk=1
AND only_one_row();

For instance, if myTable(id,fk) had the following records, only one record is produced, and I which to select the record:

1 1
2 1
3 2

However, if myTable(id,fk) had the following records, two '1' records are produced, and the select should not return any rows:

1 1
2 1
3 2
4 1

I could use PHP to do so, but would rather just use SQL if feasible.

Upvotes: 0

Views: 935

Answers (2)

Aziz Shaikh
Aziz Shaikh

Reputation: 16544

How about this:

SELECT fk
FROM myTable as t1
INNER JOIN myTable AS t2 ON t2.fk=t1.fk
WHERE t1.pk=1
GROUP BY fk
HAVING COUNT(fk) = 1

Upvotes: 2

Barmar
Barmar

Reputation: 782785

Use a HAVING clause that counts the results.

SELECT fk
FROM myTable as t1
INNER JOIN myTable AS t2 ON t2.fk=t1.fk
WHERE t1.pk=1
HAVING COUNT(*) = 1

Upvotes: 3

Related Questions