Re: Trying to 'join' either of 2 tables to a third

I've know how to build an outer join & I've considered it, I'm just having trouble figuring out how to use it here without listing all customers. If I do an outer join on just tran_table (cust_table.cust_id=tran_table.cust_id(+)) then wouldn't I miss those who have info trans but not financial trans? And if I do an outer join on just financial_table then isn't it just the opposite? And if I do an outer join on both then wouldn't I get all customers, including those who have neither financial or info trans?

In other words, I agree that it looks like some sort of outer join should do it, but I can't figure out which? I'm still trying the various options, though.

Thanks.

"Daniel A. Morgan" wrote:

> Depending on exactly what you are trying to do you could use an "outer join"
> (the most likely solution) or a "union" or "union all" or some combination
> of the above. But I definitely think you need to look at how to build an
> outer join as a first step.
>
> Daniel A. Morgan
>
> Calvin Crumrine wrote:
>
> > Let's see how to explain this. I have customer info in one table & 2
> > different types of transaction info in 2 other tables (call it financial
> > & informational transactions). All tables carry the customer ID.
> >
> > I need to retrieve customer & transaction info if customer has either
> > specific financial transactions or has specific informational
> > transactions. I can do either separately, but can't figure out how to do
> > them both. If I join all 3 tables then I get info on customers that have
> > both financial & informational transactions.
> >
> > Select cust_info, financial_info, tran_info
> > from cust_table, financial_table, tran_table
> > where cust_table.cust_id=financial_table.cust_id
> > and cust_table.cust_id=tran_table.tran_id
> >
> > I've got to put an or in there somewhere, but I'm not sure where. Tried
> >
> > Select cust_info, financial_info, tran_info
> > from cust_table, financial_table, tran_table
> > where cust_table.cust_id=financial_table.cust_id
> > or cust_table.cust_id=tran_table.tran_id
> >
> > and haven't gotten results back after 15 minutes. Either SQL*Plus locked
> > up or it's joining everything to everything (cust_table only has 5000
> > records but financial_table has 1.2 million & tran_table has over
> > 150,000).
  Received on Fri Apr 27 2001 - 11:51:04 CDT