Re: [ajug-members]: inner/outer join question

[Phelix <yokogechi@yahoo.com>]

If my understanding is correct, you need subquery instead inner/outer join.

 

SELECT A.field1, A.field2 

FROM A, AB

WHERE A.Aid not in

(SELECT Aid from AB WHERE AB.Cid = SP.Cid)

AND AB.Aid=A.Aid

AND AB.Cid=SP.Cid

AND AB.Bid<>SP.Bid ;

 

-Phelix

 

"Jason Kretzer/STAR BASE Consulting Inc." <JKretzer@starbaseinc.com> wrote:

> Slightly off topic but at least this query is being used in a java app. 
>
> I have three tables,  A   B  and AB. 
> I have two values which are PK in A B respectively,  Aid, Bid 
> I have a value from another table Cid 
> Cid is also in A 
> Aid Bid and Cid are in AB and are the PK together 
> Bid is a specified value referred to below as SP.Bid 
> Cid is a specified value referred to below as SP.Cid 
>
> I would like to get all records in A that meet the following criteria.   
>
> records not
>  present in AB whose AB.Cid=SP.Cid 
> combined with(UNION) 
> records present in AB whose AB.Aid=A.Aid and AB.Cid=SP.Cid and AB.Bid<>SP.Bid 
>
>
> I am having problems with the syntax, I can't seem to get it to come out right. 
>
> I know people have done these sort of joins before but I am more of a dabbler in SQL.  So, any help would be appreciated. 
>
> Thanks, 
>
> -Jason

---

Do you Yahoo!?
Yahoo! Small Business $15K Web Design Giveaway - Enter today