Re: SQL assistance

[Boof]

wtf has this got to do with aus.dvd ????

Sounds like someone has left their homework to the day before it's due in.


"Codswallop" <chunkylover53@aol.com> wrote in message
news:Xns9468A02A9F308codswallopcom@139.132.1.4...
> Wondering if someone can help.
>
> I've got a few SQL queries which select multiple records from tables
> using an INNER JOIN.
>
> Now, I want to show records from Table "A" where there are no related
> records in Table "B". For instance, Table "A" could be customers, and
> table "B" could be orders. Trying to select customers who have made no
> orders. I've been able to do this using a LEFT OUTER JOIN.
>
> However, I need to select records from those aforementioned tables using
> the INNER JOIN. I've tried, with no luck, putting both into the SQL
> query.
>
> Can you use an INNER JOIN and an OUTER JOIN in the same query? If not,
> what do I need to do? Two INNER JOINs? Two OUTER JOINs?
>
> The query I am using for the first statement is (selects all companies):
> SELECT company_details.company_id, company_details.company,
> supervisors.supername FROM company_details INNER JOIN supervisors ON
> supervisors.supervisor_id = company_details.supervisor_id
>
> For the OUTER JOIN, I am using (selects all companies with no related
> records in the employee_details table):
> SELECT company_details.company_id, company_details.company,
> supervisors.supername FROM company_details LEFT OUTER JOIN
> employee_details employee_details.company_id =
> company_details.company_id WHERE employee_details.company_id IS NULL
>
> I've tried combining the two but am not quite sure how to. I have done
> multiple INNER JOINs before, but this is the first time I've used OUTER
> JOIN.
>
> --
> - Cods
>
> pbqf_at@ubgznvy.pbz
> (un ROT-13 to email)

[Codswallop]

On Tue, 06 Jan 2004 04:58:57 GMT, Boof wrote in aus.dvd:

Someone posted it to the wrong group...

--
- Cods

pbqf_at@ubgznvy.pbz
(un ROT-13 to email)

[Rod Williams]

You can mix inner and outer joins. Use the (+) in Oracle on the right hand
column in the outer join, e.g.

a.key_column = b.key_column and
a.key_column = c.key_column (+)

You can also try the MINUS operator (in Oracle SQL):

select etc
from table1, table2...
where etc
MINUS
select etc
from table1, table2...
where etc

You may need to have select the same column in both queries, but it will
return rows that differ in each select. Is this what you want? You can also
use sub-selects to exclude rows, but that is beyond the scope of this
session. :D

RodW

--

Personal website: Movies - Aussie Censorship - Journal - Junk Food
www.cosmos.net.au/~hologram/


"Codswallop" <chunkylover53@aol.com> wrote in message
news:Xns9468A02A9F308codswallopcom@139.132.1.4...
> Wondering if someone can help.
>
> I've got a few SQL queries which select multiple records from tables
> using an INNER JOIN.
>
> Now, I want to show records from Table "A" where there are no related
> records in Table "B". For instance, Table "A" could be customers, and
> table "B" could be orders. Trying to select customers who have made no
> orders. I've been able to do this using a LEFT OUTER JOIN.
>
> However, I need to select records from those aforementioned tables using
> the INNER JOIN. I've tried, with no luck, putting both into the SQL
> query.
>
> Can you use an INNER JOIN and an OUTER JOIN in the same query? If not,
> what do I need to do? Two INNER JOINs? Two OUTER JOINs?
>
> The query I am using for the first statement is (selects all companies):
> SELECT company_details.company_id, company_details.company,
> supervisors.supername FROM company_details INNER JOIN supervisors ON
> supervisors.supervisor_id = company_details.supervisor_id
>
> For the OUTER JOIN, I am using (selects all companies with no related
> records in the employee_details table):
> SELECT company_details.company_id, company_details.company,
> supervisors.supername FROM company_details LEFT OUTER JOIN
> employee_details employee_details.company_id =
> company_details.company_id WHERE employee_details.company_id IS NULL
>
> I've tried combining the two but am not quite sure how to. I have done
> multiple INNER JOINs before, but this is the first time I've used OUTER
> JOIN.
>
> --
> - Cods
>
> pbqf_at@ubgznvy.pbz
> (un ROT-13 to email)