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