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

[CindySue]

Hello,

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:

SELECT DISTINCT [Auction Items].[Winning Bidder Number], [Party #1].[Winning Bidder Number]
 
FROM {oj Auction Items LEFT OUTER JOIN ON [Auction Items].[Winning Bidder Number] = [Party #1].[Winning Bidder Number]}
 
UNION SELECT DISTINCT [Auction Items].[Winning Bidder Number],
 
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.

[Rabbit]

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.

[CindySue]

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

 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.

[Rabbit]

Yes that is indeed the code tags.

I think you're overcomplicating things. Try:

SELECT DISTINCT [Winning Bidder Number]
FROM [Auction Items]
UNION
SELECT DISTINCT [Winning Bidder Number]
FROM [Party #1]
UNION
SELECT DISTINCT [Winning Bidder Number]
FROM [Party #2];
 

[CindySue]

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!

[Rabbit]

Not a problem, good luck.