Query construction

[azzazzello]

Greetings,

I have a very specific question. I have 2 tables. One of them has an ID column and, say, 2 other data columns. The other table has ID as a foreign key that relates to first table's ID, except that it is not unique, and multiple entries can have the same ID in the second table. Basically,

Table 1
ID | Name | Age

1 | John | Doe
2 | Alex | Smith

Table 2
ID | job

1 | programmer
1 | manager
1 | director
2 | programmer
2 | manager
2 | salesman

I want to make a query that will give me all rows from table 1 and 2, where a person is NOT a director. I do NOT want to see ANY information on the person if one of his responsibilities is being a director. Basically, in the above example, I want 3 rows returned (joins are fine) based on the excluding directors from the resultset:

Alex Smith programmer
Alex Smith manager
Alexa Smith salesman

I am having trouble constructing such a query without subqueries (nested selects) as my version of Mysql does NOT support subselects (yet). Can anyone help?

 

[dalchri]

I would use a CREATE TEMPORARY TABLE to emulate a subquery. This will require more than one query though:

CREATE TEMPORARY TABLE tblDirector
SELECT ID
FROM Table2
WHERE job = 'director';

SELECT
   Table1.ID,
   Name,
   Age,
   job
FROM (Table1
INNER JOIN Table2
USING (ID))
LEFT JOIN tblDirector
USING (ID)
WHERE tblDirector.ID Is Null;

DROP TABLE tblDirector;

 

[azzazzello]

The temporary table does not seem like an efficient method to me, though certainly it does the job. Also, I have had efficiency problems with JOINs: a join query rewritten as a straight select seems to do significantly better. Is there, perhaps, another way to do this? I do appreciate the help

 

[r937]

select p.Name
     , p.Age
     , j1.job
  from Table1 as p
left outer
  join Table2 as j1
    on p.ID = j1.ID
left outer
  join Table2 as j2
    on p.ID = j2.ID
group
    by p.Name
     , p.Age
     , j1.job
having sum(
        case when j2.job = 'director'
             then 1 else 0 end
          ) = 0

rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (next course starts March 6 2005)

 

[azzazzello]

In the end, I was able to upgrade the Mysql version and do nested select. The quesry is much more readable and shorter too that way. But thank you for the help anyway