Joining multiple tables with nulls

Question

I have two tables

Table1

StudentID   FullName    HoursLogged
1           Mark Smith       1
2           Jack Lantern     2
3           Pink Hotdog      3

Table2

StudentID   FirstName   LastName
1              Mark      Smith
2              Jack     Lantern
3              Pink     Hotdog
4              Mike     Cantu
5              Jake     Myers
6              Nathan   Cheese
7              Peter    Jackson

How can I make the table like this? With 0 in logged hours if the user has no hours recorded?

StudentID   FullName    HoursLogged
1           Mark Smith             1
2           Jack Lantern           2
3           Pink Hotdog            3
4           Mike Cantu             0
5           Jake Myyers            0
6           Nathan Cheese          0
7           Peter Jackson          0

Any help would be much appreciated. Thanks.

Answer 1

You can perform outer join and replace null values by 0 that is the way you can easily achieve this, below is example of outer join

SELECT t2.StudentID ,t2.FirstName + ' '+ t2.LastName as FullName
       ISNULL(HoursLogged,0) as HoursLogged,  
FROM Table2 t2
LEFT OUTER Table1 t1 
ON t1.studentID = t2.studentID

Answer 2

Try this Query:

select b.StudentID ,concat( FirstName,' ', LastName), ISNULL(HoursLogged,0) 
as HoursLogged FROM Table1 a LEFT OUTER Table2 b ON a.StudentID = b.StudentID;

Answer 3

Do you want the output all joined together, or are you trying to merge tables?

If you're looking just to combine tables in to a single output, I would suggest taking a look at JOIN clauses. Lasse Karlsen has an awesome visualization of joins posted over here. Specifically, I think you're after a left join, which will sorta of merge Table1, and Table2, and give you one output.

Also, since you have the full name split into two columns in Table2, you will have to throw in a CONCAT. Concat will merge multiple strings into a single string that you can then use for comparisons. E.g. CONCAT("string 1", " ", "string 3") gives you "string 1 string 3" (without the quotes).

Keep in mind too that Mark Smith in Table2 seems to have an extra space before his LastName. To take care of that, add in TRIM. This will remove leading, and trailing, spaces.

Depending on who/what/how the table was put together, the names still might not match exactly. If you do just a straight comparison (Table1.col = Table2.col), then the names would have to match up 100%, including case (FYI, I want to say this is not as important on a Windows hosted database, but I'm pretty sure it is important in Linux). You can toss in a LOWER or UPPER function to force everything to upper or lower case.

Here's my lousy pseudo-code take on it:

SELECT Table1.StudentID, Table1.FullName, Table2.HoursLogged FROM Table1 LEFT JOIN Table2 ON CONCAT(UPPER(Table2.FirstName), " ", UPPER(Table2.LastName)) = UPPER(Table1.FullName)

I've noticed sometimes databases get finicky, so you have to throw some graves in there. I'm honestly not certain why this matters sometimes, but I have had this resolve odd issues that have arisen in the past.

SELECT `Table1`.`StudentID`, `Table1`.`FullName`, `Table2`.`HoursLogged` FROM `Table1` LEFT JOIN `Table2` ON CONCAT(UPPER(`Table2`.`FirstName`), " ", UPPER(`Table2`.`LastName`)) = UPPER(`Table1`.`FullName`)

EDIT Totally forgot about the null part. Santho has the right idea.

SELECT Table1.StudentID, Table1.FullName, ISNULL(Table2.HoursLogged, 0) AS `HoursLogged` FROM Table1 LEFT JOIN Table2 ON CONCAT(UPPER(Table2.FirstName), " ", UPPER(Table2.LastName)) = UPPER(Table1.FullName)

EDIT2 You know, I looked at it again, and I feel silly now... If the StudentID's match between the two tables, you don't have to mess with all the CONCAT stuff.

SELECT Table1.StudentID, Table1.FullName, ISNULL(Table2.HoursLogged, 0) AS `HoursLogged` FROM Table1 LEFT JOIN Table2 ON Table1.StudentID = Table2.StudentID

EDIT3 Jarlh's right. I believe the standard to concatenate a string is a double vertical bar

"string 1" || "string 2"

https://msdn.microsoft.com/en-us/library/hh501243(v=sql.105).aspx

Trim leading/trailing spaces

trim(both ' ' from 'string 1')

https://msdn.microsoft.com/en-us/library/hh544564(v=sql.105).aspx

LEFT JOIN's are not standard, but you can add a WHERE clause to a (natural) JOIN

SELECT Table1.StudentID, Table1.FullName, Table2.HoursLogged FROM Table1, Table2 WHERE Table1.StudentID = Table2.StudentID

ISNULL is a function that's not supported by all database management systems, but COALESCE seems to be everywhere.

SELECT Table1.StudentID, Table1.FullName, COALESCE(Table2.HoursLogged, 0) AS HoursLogged FROM Table1, Table2 WHERE Table1.StudentID = Table2.StudentID

Answer 4

It depends on the type of the database you use. Check this, https://www.w3schools.com/sql/sql_isnull.asp, and use a correct function,

And CONCAT to concat 2 strings.

It would look something like this,

SELECT Ifnull(table1.studentid, table2.studentid) 
       StudentID, 
       Ifnull(table1.fullname, Concat(table2.firstname, ' ', table2.lastname)) 
       FullName, 
       Ifnull(table1.hourslogged, 0) 
       HoursLogged 
FROM   table1 
       LEFT JOIN table2 
              ON table1.studentid = table2.studentid 

Also, you tables design looks bad, not consistent. FirstName & LastName in 1 table, and the other table has FullName.

Better to just design it as such (or something similar)

• Student table (StudentID, FirstName, LastName)
• HourLogged table (StudentID, HourlyLogged)