Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

Here is my problem, I have a query looking like:

INSERT INFO table (f1, f2, f3)
    SELECT t1.f1bis, t2.f2bis, t3.f3bis
    FROM   t1, t2, t3
    WHERE  t1.f1 = 'toto' AND
           t2.f2 = 'tata' AND
           t3.f3 ILIKE 'titi';

The problem is that if t3 doesn't contains 'titi', then the SELECT would return no lines at all.

What I would want is that when t3 doesn't contains TITI, the SELECT returns f3bis as NULL

I was thinking about the following query instead (forget about the INSERT):

SELECT t1.f1bis, t2.f2bis, t3.f3bis
FROM   t1, t2,
       ((SELECT 'titi' AS f3) AS dummytable)
           LEFT OUTER JOIN
       t3
           ON dummy_table.f3 = t3.f3
WHERE  t1.f1 = 'toto' AND
       t2.f2 = 'tata';

That would make sure that if 'titi' doesn't exist in t3, then I would get a NULL. But the query doesn't seem to work.

Can you help me?

Thanks.

share|improve this question
add comment

1 Answer

I found the answer on Wikipedia

The effect of outer joins can also be obtained using correlated subqueries. For example

SELECT employee.LastName, employee.DepartmentID, department.DepartmentName 
FROM   employee LEFT OUTER JOIN department 
          ON employee.DepartmentID = department.DepartmentID

can also be written as

SELECT employee.LastName, employee.DepartmentID,
  (SELECT department.DepartmentName 
    FROM department
   WHERE employee.DepartmentID = department.DepartmentID ) AS DepartmentName
FROM   employee

So the answer to my question is the query:

SELECT t1.f1bis, t2.f2bis,
       (SELECT t3.f3bis
        FROM   t3
        WHERE  t3.f3 = 'titi'
       ) AS f3bis
FROM   t1, t2
WHERE  t1.f1 = 'toto' AND
       t2.f2 = 'tata';
share|improve this answer
add comment

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.