Need help joining 2 tables on one [duplicate]

Question

So I got an admin dashboard which are not linked by ID but by name. So developer.name is the core name, dateName and absentName are meant to join this developer.name value.

What I got so far is this

db.query("SELECT id, name, absentday, date, slack_id, selected 
            FROM developers, absent, date 
            WHERE date.dateName=developers.name 
            AND absent.absentName=developers.name",

I certainly know this is wrong, anyone have suggestions on how I can make 2 tables join 1 table using only one value?

Developers= id DevelopersName selected

Absent= absent_id absent_name absentdays

Date= date_id date_name date

I have an admin page, and on this page people are able to create an absence day for when they're not present. Which is why I have the table Absence. They only have to put in their name, so lets say Tom Hiddle, and the day they are absent. This will be inserted into the database, This is the same story for the Date table.

Now I have a BOT, Basically, the code and query I've written is meant to exclude the people who are absent on certain days, AND those who are on holidays. I am able to make the Absent table work, correctly excluding the absent people from the query. HOWEVER, I now meet the problem where I have to join DateName and AbsentName to DevelopersName value. Why don't I use IDs for this instance? It's simple, when records are deleted the ID selection will mess up, and since I'm creating this system for a company I work for It cannot have room for error, hence I use identical names to link tables between each other.

So In short, I don't use IDs for multiple reasons, Instead I use identical names between tables. DateName and AbsentName are meant to join with DevelopersName, If I join one of them I get results, If I join both I get none.

// I have a CRUD Dashboard where I can insert absentdays for Developers.
// So lets say Developer Tom Riddle is not present on monday, I'll put his absentday on 1
// Monday to Friday (1-5)
absent = [
  { id: 1, absentName: 'Tom Riddle', absentday: 1},
  { id: 2, absentName: 'Hank Some', absentday: 2},
  { id: 3, absentName: 'Family Man', absentday: 3}
]

// Date is not the same as Absent, Date is a long term deposit of holidays,
// lets say Hank Some Is going on vacation for 3 days!
date = [
  { id: 1, dateName: 'Tom Riddle', date: '2022-05-13'},
  { id: 2, dateName: 'Hank Some', date: '2022-07-14'},
  { id: 3, dateName: 'Hank Some', date: '2022-07-15'},
  { id: 4, dateName: 'Hank Some', date: '2022-07-16'},
  { id: 5, dateName: 'Family Man', date: '2022-06-15'}
]

// This is the core information of the developers. These are NOT the only columns, there are many more, but for sample data I only noted down the most important one's.
developers = [
  { id: 51, developersName: 'Tom Riddle'},
  { id: 52, developersName: 'Hank Some'},
  { id: 53, developersName: 'Family Man'}
]

// Say I run this query
SELECT id, developersName, absentName, absentday FROM developers, absent WHERE absent.absentName=developers.developersName;

// The output will be this
developers = [
  { id: 51, developersName: 'Tom Riddle', absentName: 'Tom Riddle', absentdays: 1},
  { id: 52, developersName: 'Hank Some',  absentName: 'Hank Some',  absentdays: 2},
  { id: 53, developersName: 'Family Man', absentName: 'Family Man', absentdays: 3}
]

// I now have ABSENT joined with DEVELOPERS.
// With MORE code deeper into the file I can exclude absent people from the query
// Let's say It's monday, It would then look like this. 

developers = [
  { id: 52, developersName: 'Hank Some',  absentdays: 2},
  { id: 53, developersName: 'Family Man', absentdays: 3}
]

// This WORKS, my issue at the moment is combining DATE and ABSENT on Developers
// My WANTED result is this
developers = [
  { id: 51, developersName: 'Tom Riddle', absentdays: 1,  date: '2022-05-13'},
  { id: 52, developersName: 'Hank Some',  absentdays: 2,  date: '2022-07-14'},
  { id: 52, developersName: 'Hank Some',  absentdays: 2,  date: '2022-07-15'},
  { id: 52, developersName: 'Hank Some',  absentdays: 2,  date: '2022-07-16'},
  { id: 53, developersName: 'Family Man', absentdays: 3,  date: '2022-06-15'}
]

// After researching myself, Using this query will give these results above ^^
SELECT id, developersName, absentName, absentday FROM developers, absent WHERE absent.absentName=developers.developersName AND date.dateName=developers.developersName;

// With my problem of joining tables being fixed, I run into a new issue.
// Whenever I execute the query above, it will only display people with a DATE
// Let's look at these sample data here. 

absent = [
  { id: 1, absentName: 'Tom Riddle', absentday: 1},
  { id: 2, absentName: 'Hank Some', absentday: 2},
  { id: 3, absentName: 'Family Man', absentday: 3},
  { id: 4, absentName: 'Buddy Friend', absentday: 4}
]
// Hank Some no longer has records in these table since his holidays have passed
// Buddy Friend has no holidays at all.

date = [
  { id: 1, dateName: 'Tom Riddle', date: '2022-05-13'},
  { id: 2, dateName: 'Family Man', date: '2022-06-15'}
]

// If I execute the query
SELECT id, developersName, absentday FROM developers, absent WHERE absent.absentName=developers.developersName AND date.dateName=developers.developersName;

// MY results are this
developers = [
  { id: 51, developersName: 'Tom Riddle',  absentdays: 1,  date: '2022-05-13'},
  { id: 53, developersName: 'Family Man',  absentdays: 3,  date: '2022-06-15'},
]

// It doesn't display the people who have NO holidays.
// Buddy Friend and Hank Some are MISSING!

So to the question: I need help getting Buddy Friend and Hank Some back in my results. And another question, is there a cleaner way of joining these tables together? Instead of using WHERE, maybe inner joins?

Answer 1

You are using a join syntax that was already out-dated when MySQL was developed. It is weird seeing you using it. Maybe you mistakenly took a book from the 1980s to learn SQL? Better quit this. MySQL still supports this old syntax (as it is still allowed), but we are not using it any longer, because it is less readable than explicit joins, much more prone to errors and doesn't support outer joins.

This is what your query looks like in the syntax we have been using in the last three decades:

SELECT developers.id, developers.developersName, absent.absentday, date.date
FROM developers
INNER JOIN absent ON absent.absentName = developers.developersName 
INNER JOIN date ON date.dateName = developers.developersName;

This joins the three tables and returns all matches. If you want to show developers even when they don't have an entry in the absent or date table, use outer joins instead of inner joins:

SELECT developers.id, developers.developersName, absent.absentday, date.date
FROM developers
LEFT OUTER JOIN absent ON absent.absentName = developers.developersName 
LEFT OUTER JOIN date ON date.dateName = developers.developersName;

Be aware though that with this data model, names must never change. This may be true for login names, but not for natural names, where names change when people getting married for instance. A more typical database design would hence be:

CREATE TABLE developer
(
  developer_id   int           not null auto_increment,
  first_name     varchar(100)  not null,
  last_name      varchar(100)  not null,
  primary key (developer_id)
);

CREATE TABLE developer_absent
(
  developer_id   int           not null,
  day_num        int           not null,
  primary key (developer_id, day_num),
  foreign key (developer_id) referencing developer (developer_id)
);

CREATE TABLE developer_holiday
(
  developer_id   int           not null,
  holiday        date          not null,
  primary key (developer_id, holiday),
  foreign key (developer_id) referencing developer (developer_id)
);

The query would then become:

SELECT d.developer_id, d.name, da.day_num, dh.date
FROM developers d
LEFT OUTER JOIN developer_absent da ON da.developer_id = d.developer_id
LEFT OUTER JOIN developer_holiday dh ON dh.developer_id = d.developer_id;

But while this is syntactically correct, it doesn't make much sense semantically, because the absent days and the holidays are not closely related. You'd see that Hank is on holiday on 2022-07-14 and absent on Tuesdays and on holiday on 2022-07-15 and absent on Tuesdays and on holiday on 2022-07-16 and absent on Tuesdays.

A more typical query would be for instance:

-- Who is available on Friday, May 20, 2022
select *
from developer
where developer_id not in 
(
  select developer_id
  from developer_absent
  where day_num = (dayofweek(date '2022-05-20') + 5) % 7 + 1
)
and developer_id not in 
(
  select developer_id
  from developer_holiday
  where holiday = date '2022-05-20'
);

(There is a little math involved to match your MON-SUN = 1-7 with MySQL's SUN-SAT = 1-7.)