Click here to Skip to main content
14,743,780 members
Please Sign up or sign in to vote.
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
Updated 16-May-20 21:21pm

1 solution

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

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




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900