join in SQL

From: Gerard Le Comte <lecomte_at_sns.fr>
Date: 1997/11/06
Message-ID: <346182F1.1700@sns.fr>#1/1

Hi,

        I have a question on table joins :

Consider 3 tables : A, B and C.
A contains a reference to C and a value :

        a.c_id = c.id
B only contains a reference to A and C :

        b.c_id = c.id and b.a_id = a.id
C only contains a value

Consider now, that I want to know all group (a.value;c.value) and that B is empty.

---

select a.value AV, c.value, AC from a, b, c where
((a.c_id = c.id)
 OR (b.c_id = c.id and b.a_id = a.id)
);

---

the result is always "no row selected" even if this request don't return "no row.." :

---

select a.value AV, c.value, AC from a, c where
a.c_id = c.id;

---

I known that a good request could be :

select a.value AV, c.value, AC from a, b, c where
b.c_id = c.id and b.a_id = a.id;
UNION ALL
select a.value AV, c.value, AC from a, c where a.c_id = c.id;
---

But, it is not a simply way to build a more complex request.

1. How could we build this request as the first request without "UNION ALL" ?
2. Why the following request returns "no row selected" : ?
   --
   select * from a, b; -- a is not empty, b is empty
   --
   

        thanks for help.

                        Gerard Le Comte Received on Thu Nov 06 1997 - 00:00:00 CST