4 messages in com.mysql.lists.mysqlRe: is this query possible?
FromSent OnAttachments
Erik Price14 Jun 2002 12:54 
Luc Foisy14 Jun 2002 13:04 
Jay Blanchard14 Jun 2002 13:12 
Erik Price14 Jun 2002 13:20 
Subject:Re: is this query possible?
From:Erik Price (pri@hhbrown.com)
Date:06/14/2002 01:20:15 PM
List:com.mysql.lists.mysql

Hey, that's exactly what I needed! And I can still use WHERE clauses to further limit my results. That's great, thank you!

I have one question though. In this LEFT JOIN syntax, you used the following format:

LEFT JOIN secondary_table ON primary_table.col = secondary_table.col

Is this optimized? In the case of WHERE clauses, for instance, I always put the main (most-limiting) criteria on the right side of the equals sign and the uncertain (least-limiting) criteria on the left side of the equals sign.

Since I've never used LEFT JOIN before, I am unsure of the best way to do it.

Erik

On Friday, June 14, 2002, at 04:05 PM, Luc Foisy wrote:

How bout

SELECT main.id, sub1.other, sub2.other FROM main LEFT JOIN sub1 ON main.sub1fk = sub1.id LEFT JOIN sub2 ON main.sub2fk = sub2.id

Luc mysql,sql

-----Original Message----- From: Erik Price [mailto:pri@hhbrown.com] Sent: Friday, June 14, 2002 3:54 PM To: mys@lists.mysql.com Subject: is this query possible?

I have a query that I have in mind, but am not sure of how I can actually write it. It might not even be possible. I was hoping someone could tell me if I will have to use two queries instead, or if this will actually work:

(In simplified form:)

+--------+ +-------+ | main | | sub1 | +--------+ +-------+ +-------+ | id | | sub2 | | id |---<| sub1fk | +-------+ | other | | sub2fk |>---| id | +-------+ +--------+ | other | +-------+

As you can see from the simple diagram, I have a main table with its own primary key (id) but with two foreign key columns. The first one (sub1fk) points to the primary key of the table "sub1". The second one (sub2fk) points to the primary ky of the table "sub2".

The query I'm trying to build would look something like this:

SELECT main.id, IF(main.sub1fk,sub1.other,NULL) AS sub1other, IF(main.sub2fk,sub2.other,NULL) AS sub2other FROM main, sub1, sub2 WHERE main.id = some_criteria_or_other AND sub1.id = main.sub1fk AND sub2.id = main.sub2fk;

The above SQL, of course, won't work -- because there are no situations where all of the WHERE clauses are true. Rather, I'm trying to get a result set that would look like this (again, this is in theory):

+----+-----------+-----------+ | id | sub1other | sub2other | +----+-----------+-----------+ | 1 | 2 | NULL | | 2 | NULL | 5 | | 3 | NULL | 17 | | 4 | 8 | NULL | | .. | ...etc | ...etc | +----+-----------+-----------+

Later, in my application, I can test each column for NULL and I will know that the other column is the one to use (for instance, if the value of the "sub1other" column is NULL in one record, then I'll use the value of sub2other to do what I want to do, and vice versa).

But this just doesn't seem possible. I can always do it with two separate queries if need be, but it would be elegant to do it with one. Any advice?

Thanks very much,

Erik

----

Erik Price Web Developer Temp Media Lab, H.H. Brown pri@hhbrown.com>

----

Erik Price Web Developer Temp Media Lab, H.H. Brown pri@hhbrown.com