CODEWITHSUNDEEP
mysqlsql
Royston Yinkore
Royston Yinkore

Reputation: 591

Select TRUE or FALSE where a relationship exists or not

table1 

    id  name

    1   john
    2   mary
    3   bill
    4   carrie

table2 

    id  thing

    1   ball
    2   socks

tableJoin 

    id  name_fk    thing_fk

    1     1           1
    2     4           2

I want a query that selects ALL the names in table1 and a 'relationship exists in table2' column from tableJoin as follows:

tableResult:

columnName     columnRelationshipExists

 john            YES
 mary            NO
 bill            NO
 carrie          YES

Upvotes: 1

Views: 1708

Answers (2)

Mathew
Mathew

Reputation: 8279

Another alternative:

SELECT t1.name, tj.id IS NOT NULL
FROM table1 t1
LEFT JOIN tableJoin tj ON tj.name_fk = t1.id
GROUP BY t1.name

I've not tested, but my feeling is that IS NOT NULL will prove faster than EXISTS(SELECT) and COUNT

Upvotes: 0

eggyal
eggyal

Reputation: 125865

One could use a correlated subquery:

SELECT   name, EXISTS(SELECT * FROM tableJoin WHERE tableJoin.name_fk = table1.id)
FROM     table1;

Or else a grouped outer join:

SELECT   table1.name, COUNT(tableJoin.name_fk) > 0
FROM     table1 LEFT JOIN tableJoin ON tableJoin.name_fk = table1.id
GROUP BY table1.name;

See them on sqlfiddle.

Upvotes: 2

Related Questions