RE: [cinjug-users] SQL syntax question

["Bandy, Charles" <CBandy@xxxxxxxx>]

I am not suggesting that I am a SQL expert, but based on your description below, it appears that you really have 4 tables:

 

A contains Aid and Cid

 

B contains Bid

 

AB contains Aid, Bid and Cid as a single PK ( you stated that these are the PK together, so I am assuming that this is a single entry not three entries, right? )

 

SP contains Bid and Cid

 

Now you are looking for records in A not present in AB that are equal to AB.Cid ( which does not exist because you have stated that the PK is all  three so you cannot just get to Cid but rather you would be looking for the primary key which would be Aid+Bid+Cid) but if they were individual fields that you were trying to select on your saying that you looking for data in AB that would not exist therefore you could not get a match.

 

The second statement confuses me also, but it appears that you are trying to get a select statement that combines what is not present in AB with what is present in AB.  

 

Sorry, I was not able to help you out more as I am definitely not an SQL expert but I am hoping that 1) someone can explain the problem more so that I can understand it or 2) you may have a logical problem trying to get to the data you need and I helped to present this.  Either way, I wish you the best of luck in getting your results.

 

cjb

-----Original Message-----
From: Jason Kretzer/STAR BASE Consulting Inc. [mailto:JKretzer@xxxxxxxxxxxxxxx]
Sent: Wednesday, April 07, 2004 11:31 AM
To: users@xxxxxxxxxx
Subject: [cinjug-users] SQL syntax question

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

---

CONFIDENTIALITY NOTICE: The information contained in this e-mail and attached document(s) may contain confidential information that is intended only for the addressee(s). If you are not the intended recipient, you are hereby advised that any disclosure, copying, distribution or the taking of any action in reliance upon the information is prohibited. If you have received this e-mail in error, please immediately notify the sender and delete it from your system.