Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have 3 tables:

TASK (id_task (PRIMARY KEY), name) 
PROJECT (id_project (PRIMARY KEY), name)
PROJECT_HAS_TASK (id_project, task_id)

PROJECT_HAS_TASK is the (n,m) relation between PROJECT and TASK.

I'd like to return all the TASK records and a boolean value indicating if the task_id also exists in PROJECT_HAS_TASK. Is that possible to do with a SQL query? Or should I use a stored procedure?

EDIT :
Yes it is possible to do it with a SQL query. Thanks to both a1ex07 and bummi's help I ended up with this solution :

SELECT a.id_task,  
Case WHEN b.id_task IS NULL then 0 else 1 end as HasProject 
from TASK a
LEFT join (SELECT DISTINCT id_task FROM PROJECT_HAS_TASK)b on (a.id_task = b.id_task);
share|improve this question

2 Answers 2

up vote 0 down vote accepted

If you need to check if there is any project that has task, use something like

SELECT a.id_task,  
Case WHEN b.id_task IS NULL then 0 else 1 end as HasProject 
from TASK a
LEFT join (SELECT DISTINCT id_task FROM PROJECT_HAS_TASK)b on (a.id_task = b.id_task)
;
-- if you need number of tasks for a project
Select  a.id_task,
COUNT(b.id_project) as num_projects
from TASK a
LEFT join PROJECT_HAS_TASK b on a.id_task = b.id_task
GROUP BY a.id_task;
share|improve this answer
    
Thanks very much ! Your help is much appreciated ! That works perfectly and It solved my problem. –  gallab Nov 29 '12 at 16:13

Your solution will cause performance problems if there will be larger number of records as there will be not index used in this JOIN statement:

LEFT join (SELECT DISTINCT id_task FROM PROJECT_HAS_TASK)b on (a.id_task = b.id_task);

Probably you should try something using the EXISTS statement like this

SELECT `TASK`.`id_task`,  
CASE WHEN EXISTS (SELECT * FROM `PROJECT_HAS_TASK` 
                  WHERE `TASK`.`id_task` = `PROJECT_HAS_TASK`.`id_task`) 
  THEN 1 
  ELSE 0 
END as HasProject 
FROM `TASK`;

or the simplified (for MySQL only):

SELECT t.id_task,  
       EXISTS (SELECT * FROM PROJECT_HAS_TASK AS pt 
               WHERE t.id_task = pt.id_task) 
         AS HasProject 
FROM TASK AS t;
share|improve this answer
    
Assuming PROJECT_HAS_TASK has foreign key to task it's impossible (for mysql innodb) not to have index on PROJECT_HAS_TASK.id_task. SELECT DISTINCT id_task FROM PROJECT_HAS_TASK must scan index, not table unless: 1 serious bug in optimizer; 2 id_task is a leading column in PROJECT_HAS_TASK primary key, so index scan is the same as table scan. To summarize : I'm not trying to say that your approach is worse (performance-wise) than mine; I guess they should generate the same execution plan –  a1ex07 Dec 1 '12 at 19:39
    
@a1ex07 It will use index on (SELECT DISTINCT id_task FROM PROJECT_HAS_TASK) but will not use index on the join statement. Try building those tables and checking EXPLAIN on both statements yours will require three operations and on one there will be no index used. –  onlineapplab.com Dec 1 '12 at 21:52
    
Test with empty/few records table doesn't prove or refute anything. Until the table grows up to certain limit, full scan will be the most efficient way... –  a1ex07 Dec 1 '12 at 22:10
    
@a1ex07 It proves that "Your solution may cause performance problems if there will be larger number of records" –  onlineapplab.com Dec 1 '12 at 22:29
    
I guess "may cause performance problems" can be applicable to any solution :) . Especially "may"... –  a1ex07 Dec 1 '12 at 22:42

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.