Topic awaiting preservation: MySQL question...

Having a little trouble figuring out if I can do what I am trying to do with a single query -

I have two tables with a common column. We'll call them table1, table2, and column1.

I want to select the results of table1.column1 based on a variable. I want to also select table2.column1 if it is equal to table1.column1.

This is what I have been trying for my code -

SELECT table1.column1, table2.column1

   FROM table1, table2

 WHERE table1.column1 = '$variable' AND 

             table2.column1 = table1.column1

The problem, of course, is that I only get results when all three (table1, table2, and the variable) all match.

I want table1's result when it matches the variable, and if table 2 also matches, I'll take that too. I don't want to exclude the results from table1 if they don't match table2 though.

I can do this with 2 queries easily enough, but am thinking there has to be a way with a single query. I'm still pretty new to working with SQL, so I could very easily be missing something that's obvious to someone who knows what they're doing =)

Hopefully that makes sense...

Any help greatly appreciated.



[This message has been edited by DL-44 (edited 02-02-2003).]

[Emp edit: To clean up SQL and remove HSBoD]

[This message has been edited by Emperor (edited 02-02-2003).]

DL-44: My feeling, from what you describe is that it needs 2 queries but I'll have a think on it.

[edit: Hmmmmmm I suspect you could just an OR here - like:

SELECT table1.column1, table2.column1

   FROM table1, table2

 WHERE table1.column1 = '$variable' OR

             table2.column1 = table1.column1

see this page for some good info:
www.juicystudio.com/tutorial/mysql/select.html

there may also be other ways - I'll have a dig and see what I come up with See if that works though ]

[edit2: Ahhhh but you don't need a join (as far as I can tell) so try:

SELECT table1.column1, table2.column1

   FROM table1, table2

 WHERE table1.column1 = '$variable' OR

             table2.column1 =  '$variable'

that might work better]
___________________
Emps

FAQs: Emperor

Emps second edit was my thought in it as I was reading your post DL.

I was just wondering if there were times that table2.column1 would match even if table1.column1 didn't, and if you want the result from table2 anyway if table1 doesn't match. If so that OR isn't going to work for you, otherwise what Emps gave you should work fine.

-Butcher-

Well, after to talking to Emperor on ICQ, and clarifying some things a bit, it seems I was overthinking this, and two queries is in fact the best way to go.

The problems arise from a couple simple points -

1) table1 will *always* have a result.

2) table2 may have no results, or may have any given number of results.

So, mulitple queries is simple and it works - whereas the OR gave me some trouble...

Thanks for the responses =)