SQL Apprentice Question
10 - Monday
20 - Tuesday
30 - Wed..
40 - etc
id2 - name
5 - blue
25 - red
33 - yellow
How do I use SQL to join the tables so that the "id1" columns join on
the largest "id2" value smaller than "id1"
Monday - blue
Tuesday - blue
Wed - red
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
= (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?"
teacher: "Billy, what is 6 times 7?"
teacher: "Johnny, what is 6 times 7?"
teacher: "Johnny, tell the class how you go that answer."
student: "I divided Red by Thursday!"