CODEWITHSUNDEEP
mysqlleft-join
ThereIsNothingImpossible
ThereIsNothingImpossible

Reputation: 53

Left joins, i need an explanation about a code

i am watching a tutorial. There is a code which i don't understand what is supposed to do.

$sql = 'SELECT p.*, 
      a.screen_name AS author_name, 
      c.name AS category_name
    FROM 
      posts p
      LEFT JOIN 
        admin_users a ON p.author_id = a.id
      LEFT JOIN 
        categories c ON p.category_id = c.id
    WHERE 
      p.id = ?';

I read about the left joins but i didn't understand them. Can somebody please explain me the code i shared. Thanks in advance!

Upvotes: 0

Views: 46

Answers (1)

Jamin
Jamin

Reputation: 1402

Imagine you have two tables. One that stores the information about the programmers on your website, and the other table that keeps track of their online purchases.

PROGRAMMERS Table

+--------------------------------------------+
| ID | NAME     | AGE | ADDRESS   | SALARY   |
+----+----------+-----+-----------+----------+
|  1 | Desire   |  32 | 123 fake s|  3000.00 |
|  2 | Jamin    |  25 | 234 fake s|  2500.00 |
|  3 | Jon      |  23 | 567 fake s|  2000.00 |
|  4 | Bob      |  30 | 789 fake s|  1500.00 |
|  5 | OtherGuy |  31 | 890 fake s|  1000.00 |
|  6 | DudeMan  |  32 | 901 fake s|   500.00 |
+--------------------------------------------+

PURCHASES Table

+---------------------------------------------+
| ORDER_ID    | PROG_ID |    DATE    | PRICE  | 
+-------------+---------+---------------------|
|  1          | 1       |  1-1-2017  | 100    |
|  2          | 2       |  1-2-2017  | 200    |
|  3          | 6       |  1-3-2017  | 300    |
+---------------------------------------------|

You decide you need to make a new table to consolidate this information to a table that contains certain columns you want.

For example, you figure it would be nice for shipping purposes to have a table that has the ID, the NAME, the PRICE, and the DATE columns.

Currently, the tables we have don't display all of that in a single table.

If we were to LEFT JOIN these tables, we would end up filling the desired columns with NULL values where there is no information to join.

SELECT ID, NAME, PRICE, DATE
    FROM PROGRAMMERS
    LEFT JOIN PURCHASES
    ON PROGRAMMERS.ID = PURCHASES.PROG_ID;

Notice that I'm selecting the columns I want from the starting table, then joining the right table even though there might be missing information.

RESULTING TABLE

+-------------------------------------+
| ID | NAME     | PRICE   |   DATE    |
+----+----------+-----------------+---+
|  1 | Desire   | 100     | 1-1-2017  |
|  2 | Jamin    | 200     | 1-2-2017  |
|  3 | Jon      | NULL    |   NULL    |
|  4 | Bob      | NULL    |   NULL    |
|  5 | OtherGuy | NULL    |   NULL    |
|  6 | DudeMan  | 300     | 1-3-2017  |
+-------------------------------------+

For a visual representation of the difference between SQL JOINs check out https://www.codeproject.com/Articles/33052/Visual-Representation-of-SQL-Joins .

Upvotes: 2

Related Questions