Exotic SQL Question

SQL Apprentice Question
Table 1:
id1 name
10 - Monday
20 - Tuesday
30 - Wed..
40 - etc

Table2:
id2 - name
5 - blue
25 - red
33 - yellow
77- gree


How do I use SQL to join the tables so that the "id1" columns join on
the largest "id2" value smaller than "id1"


Result:
Monday - blue
Tuesday - blue
Wed - red
etc


Thanks


Celko Answers
Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, data types, etc. in
your schema are.

CREATE TABLE Colors
(color_nbr INTEGER NOT NULL PRIMARY KEY,
color_name CHAR(10) NOT NULL);


CREATE TABLE Weekdays
(zeller_nbr INTEGER NOT NULL PRIMARY KEY,
weekday_name CHAR(10) NOT NULL);



>> How do I use SQL to join the tables so that the color_nbr columns join on the largest zeller_nbr value smaller than color_nbr <<


SELECT C1.color_nbr, W1.zeller_nbr, C1.color_name, W1.weekday_name
FROM Colors AS C1, Weekdays AS W1
WHERE zeller_number
= (SELECT MAX(zeller_nbr)
FROM Weekdays AS W2
WHERE W2.zeller_nbr < C1.color_nbr
AND W2.zeller_nbr = W1.zeller_nbr)

This sounds like the old joke about the teacher and the students:


teacher: "Mary, what is 6 times 7?"
student: "Red?"
teacher: "Billy, what is 6 times 7?"
student: "Thursday!"
teacher: "Johnny, what is 6 times 7?"
student: "42!"
teacher: "Johnny, tell the class how you go that answer."
student: "I divided Red by Thursday!"


untested.