Closed Thread Icon

Topic awaiting preservation: MySQL question... Pages that link to <a href="https://ozoneasylum.com/backlink?for=12603" title="Pages that link to Topic awaiting preservation: MySQL question..." rel="nofollow" >Topic awaiting preservation: MySQL question...\

 
Author Thread
DL-44
Maniac (V) Inmate

From: under the bed
Insane since: Feb 2000

posted posted 02-02-2003 03:54

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 -

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).]

Emperor
Maniac (V) Mad Scientist with Finglongers

From: Cell 53, East Wing
Insane since: Jul 2001

posted posted 02-02-2003 04:22

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:

code:
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:

code:
SELECT table1.column1, table2.column1
FROM table1, table2
WHERE table1.column1 = '$variable' OR
table2.column1 = '$variable'



that might work better]
___________________
Emps

FAQs: Emperor

butcher
Paranoid (IV) Inmate

From: New Jersey, USA
Insane since: Oct 2000

posted posted 02-02-2003 05:07

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-

DL-44
Maniac (V) Inmate

From: under the bed
Insane since: Feb 2000

posted posted 02-02-2003 05:54

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 =)



« BackwardsOnwards »

Show Forum Drop Down Menu