Embarassingly simple SQL question - what am I missing?! 
Author Message
 Embarassingly simple SQL question - what am I missing?!

Hi all,

I cannot figure out why this will not work.  These outer joins appear to be
functioning like inner joins, can anyone help me please?

Thanks!

-m

SELECT
    persons.name,
    persons.personID,
    info_1.val,
    info_2.val,
    info.val
FROM
    ((info RIGHT JOIN persons ON info.personID = persons.personID)
    LEFT JOIN info AS info_1 ON persons.personID = info_1.personID)
    LEFT JOIN info AS info_2 ON persons.personID = info_2.personID
WHERE
    (((info_1.type)="email")
    AND ((info_2.type)="fax")
    AND ((info.type)="work"));



Sun, 16 Nov 2003 22:25:07 GMT
 Embarassingly simple SQL question - what am I missing?!

This is because you use columns from the outer tables in the WHERE clause.
Move this into the FROM clause like this:

SELECT
    persons.name,
    persons.personID,
    info_1.val,
    info_2.val,
    info.val
FROM
    ((info RIGHT JOIN persons ON info.personID = persons.personID)
    LEFT JOIN info AS info_1 ON persons.personID = info_1.personID
        AND info_1.type="email")
    LEFT JOIN info AS info_2 ON persons.personID = info_2.personID
        AND info_2.type="fax"
WHERE info.type="work";


Quote:
> Hi all,

> I cannot figure out why this will not work.  These outer joins appear to
be
> functioning like inner joins, can anyone help me please?

> Thanks!

> -m

> SELECT
>     persons.name,
>     persons.personID,
>     info_1.val,
>     info_2.val,
>     info.val
> FROM
>     ((info RIGHT JOIN persons ON info.personID = persons.personID)
>     LEFT JOIN info AS info_1 ON persons.personID = info_1.personID)
>     LEFT JOIN info AS info_2 ON persons.personID = info_2.personID
> WHERE
>     (((info_1.type)="email")
>     AND ((info_2.type)="fax")
>     AND ((info.type)="work"));



Mon, 17 Nov 2003 07:57:21 GMT
 
 [ 2 post ] 

 Relevant Pages 

1. Simple set variable, what am I missing

2. Simple query fails - what am I missing?

3. Simple question..I am running a stored procedure from SQL PLUS

4. Simple question..I am running a stored procedure from SQL PLUS

5. Simple Question - I am sure

6. Help with SQL Query - what am I missing?

7. SQL7, SIMPLE SIMPLE SIMPLE question

8. Why am I missing data? (SS 7.0 + ASP)

9. What am I missing...?

10. Integrated Security: What am I missing here?

11. What am I missing....?

12. what am I missing?


 
Powered by phpBB® Forum Software