Page 1 of 1

Trouble with Lookup Table

Posted: Sat May 09, 2009 11:56 am
by joshmaker
Ok, I am dealing with three tables, lets call them TableA, TableB, and LookUp. The website is going to list TableA and TableB based on the date that they were added to LookUp

LookUp looks something like this:

Code: Select all

 
ID | SourceID | Type | DateAdded
------------------------------------
1  |       12 | A    | 2009-04-28
2  |        4 | A    | 2009-05-05
3  |       27 | B    | 2009-05-05
4  |       12 | B    | 2009-05-06
5  |       45 | A    | 2009-05-08
Now, here is how I am trying to make my selection:

Code: Select all

SELECT *
 
FROM `LookUp` 
 
JOIN `TableA` 
ON IF(`LookUp`.`Type`='A', `SourceID`, NULL) = `TableA`.`ID`
 
JOIN `TableB` 
ON IF(`LookUp`.`Type`='B', `SourceID`, NULL) = `TableB`.`ID`
 
ORDER BY `DATE` DESC 
 
LIMIT 20
The problem is, 0 rows are returned when I try this. Any suggestions?

Re: Trouble with Lookup Table

Posted: Sat May 09, 2009 12:16 pm
by jayshields
I don't think MySQL supports conditional JOIN operations. In fact I don't see why any RDBMS would support this. Why do you actually need a LookUp table? Are the fields in TableA and TableB different?

You could re-think this table structure and just use a query like:

Code: Select all

SELECT * FROM `TableA`, `TableB`
Probably unrelated - you've got your ORDER BY on a non-existant DATE field (which is also a reserved word).

Re: Trouble with Lookup Table

Posted: Sat May 09, 2009 1:05 pm
by joshmaker
Sorry about the `Date` typo, that should be `DateAdded`. Unfortunately, I didn't design the database and don't have much leeway in changing it.

The conditionals JOIN statements do seem to work if I use them independently. In other words, if I use "JOIN `TableA` ON IF(`LookUp`.`Type`='A', `SourceID`, NULL) = `TableA`.`ID`" by itself then I get the results I want from TableA, and if I use "JOIN `TableB` ON IF(`LookUp`.`Type`='B', `SourceID`, NULL) = `TableB`.`ID`" by itself I get the results I want from TableB. What I need is an easy way to get the combination of the two.

Let me give a less abstract example for the sort of thing that I am trying to accomplish. Instead of TableA and TableB lets say I have "Movies" and "Books" which are used lots of different places on the website in lots of different ways along side many different products. However, now I want to have a section of the website that is called "Editor's Picks" represented by the table EditorPicks.

When an editor wants to highlight a DVD or Book it gets added to the EditorPicks table. I need the list of Books and Movies to be organized by the date that they were added to the EditorPicks table. A book or movie might be picked by an editor on more than one occasion, and the table might eventually have additional data in it (such as who selected the movie or book as there pick) or additional products might chosen (such as Music) which will also be included in this table.