By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,056 Members | 1,320 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,056 IT Pros & Developers. It's quick & easy.

Select records that appear in any one or more of three tables

P: 52

I'm so stuck and this seems like it should be so simple.

I have three tables, called Auction Items, Party #1 and Party #2. A bidder number may appear in one, two or all three of them. I'm trying to write a query that will select distinct bidder numbers that appear in one (or more) of the tables. I've found several pieces of code and tried to alter them, to no avail. The Winning Bidder Number field is the only thing I need from each table.

The code says error in From Clause, but I can't find anything wrong from what I copied from Microsoft's web site. When I created an exact duplicate of their example, it still said that. I also haven't tried to add in the Party #2 table since I couldn't get it to work with just the first one. Here it is:
Expand|Select|Wrap|Line Numbers
  1. SELECT DISTINCT [Auction Items].[Winning Bidder Number], [Party #1].[Winning Bidder Number]
  3. FROM {oj Auction Items LEFT OUTER JOIN ON [Auction Items].[Winning Bidder Number] = [Party #1].[Winning Bidder Number]}
  5. UNION SELECT DISTINCT [Auction Items].[Winning Bidder Number],
  7. FROM {oj Auction Items LEFT OUTER JOIN [Party #1] ON [Auction Items].[Winning Bidder Number] =[Party #1].[Winning Bidder Number]}
Any help would be most appreciated.
Jan 3 '08 #1
Share this Question
Share on Google+
5 Replies

Expert Mod 10K+
P: 12,366
Please use code tags.

I don't know if you can use { } curly brackets to group a clause. Try using parentheses.

If you have an object name with spaces, you must enclose it with [ ] square brackets.

I don't think your union query will work as it has different amounts of columns. I believe union queries require the same amount of columns.
Jan 3 '08 #2

P: 52
Thanks for the reply. I apologize--but I don't know what code tags are. I see something on the reply guidelines--does that mean just to put
Expand|Select|Wrap|Line Numbers
  1.  at the beginning and 
at the end?

I changed the curly braces to parentheses, and now my error says syntax error in join operation.

I'd be more than glad to throw this code out if there is something that will work better. I have so little experience I just didn't know where to start.
Jan 3 '08 #3

Expert Mod 10K+
P: 12,366
Yes that is indeed the code tags.

I think you're overcomplicating things. Try:
Expand|Select|Wrap|Line Numbers
  1. SELECT DISTINCT [Winning Bidder Number]
  2. FROM [Auction Items]
  3. UNION
  4. SELECT DISTINCT [Winning Bidder Number]
  5. FROM [Party #1]
  6. UNION
  7. SELECT DISTINCT [Winning Bidder Number]
  8. FROM [Party #2];
Jan 3 '08 #4

P: 52
Thank you! That's it exactly. I had expected it to be simple, but my knowledge is so limited and I don't get that many chances to improve it with everything else I do, so I pretty much have to find an example somewhere and modify. Thanks again!
Jan 3 '08 #5

Expert Mod 10K+
P: 12,366
Not a problem, good luck.
Jan 3 '08 #6

Post your reply

Sign in to post your reply or Sign up for a free account.