Combing 2 different tables with relatable data

Question

0.00/5 (No votes)

See more:

I have 2 MySQL tables. One is a form on my site that gets submitted. The Second is a library of all employees and their departments. Screenshots attached

First Table
1st Table Form Submissions

Second Table
2nd Table "Library"

So basically, I would like to show all the data in table1 along with having the department from table2 at the end of the table 1 row based on the common name. So after the indexData column, I would see the department for that specific name, fullName data.

This is obviously not a proper statement but can sort of help with explaining what I'm trying to do,

SELECT results.fullName, employees.department, results.result, results.data1 where results.fullName = employees.name

Thanks for any help!

What I have tried:

I looked at Join and inner Join but both would not combine. It's like it only showed the common data between 2 tables based on the name and eliminated any nonrelated matches which is not what I want.

Posted 16-May-20 19:42pm

Cody O'Meara

Updated 16-May-20 20:21pm

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

OriginalGriff · Accepted Answer · 2020-05-16T20:21:00

Your table design needs work:
1) While it will work - some of the time - using the fullName as a Unique Key to identify a row is very flawed, partly because it's inefficient, but mostly because they are very rarely unique: as the number of people in your DB grows, so does the probability that two users will have identical names: it's very, very common! As soon as that happens, your data integrity goes out the window, and your whole design crashes. Always use a Row ID column, which should be the "master index" to your table data.
2) It's also very inefficient to store the same data in multiple places: "Cody OMeara" appears in at least two places (and that takes space), and so will the Department. If the user changes their name - when they get married for example - or departments split or merge you have to change it in multiple places and that's both complicated and dangerous. There is also the risk of different tables having slightly different data which should be the same: "Cody OMeara" and "Cody O'Meara" perhaps, or "Cody Omeara". Your code has to cope with all the possibly variations or again your data integrity fails and so does your app.
3) Your "Library" table is useless as a separate table - it contains a single item which should be held in the user table, unless the user can be part of multiple Departments - in which case it becomes even less efficient to use the fullName as a key.
4) Don't call columns by keywords: DATETIME is a datatype in SQL, so you shouldn't use it for a column name: "Timestamp", "EnterDate", or "JoinedOn" are mush better, depending on what the data they contain means. Similarly for "data1", "data2", ... "data6" you shoudl use names that describe what the column holds, not a generic name which doesn;t help your code be easy to read.

So add ID rows to your tables, us eteh ID as the primary key to identify a row, and then you can JOIN the data without problems:
Users:

ID            INT, IDENTITY (Or UNIQUEIDENTIFIER, depending on your preference)
fullName      NVARCHAR
timeStamp     DATATIME2
...

Library:

ID           INT, IDENTITY (Or UNIQUEIDENTIFIER, depending on your preference)
UserID       INT, IDENTITY (Or UNIQUEIDENTIFIER, match it with the Users ID)
DeptID       INT, IDENTITY (Or UNIQUEIDENTIFIER, match it with the Departments ID)

Departments:

ID           INT, IDENTITY (Or UNIQUEIDENTIFIER, depending on your preference)
Name         NVARCHAR

And JOIN them together:

SQL

SELECT u.fullNAme, u.TimeStamp, d.Name
FROM Users u
JOIN Library l ON u.ID = l.UserID
JOIN Departments d ON l.DeptID = d.ID

That way, data is stored in one place, and you are letting the relational database do what it it good at: sorting out relationships between data items!