How to write this query with two joins

I have 3 table

type

    +----+-------+
    | id | type  |
    +----+-------+
    |  1 | typeA |
    |  2 | typeB |
    |  3 | typeC |
    +----+-------+

brand (contains brands and sub brands with parent brand id, like brandC is a sub brand of brandA)

+----+--------+--------+
| id | brand  | parent |
+----+--------+--------+
|  1 | brandA |     0  |
|  2 | brandB |     0  |
|  3 | brandC |     1  |
+----+--------+--------+

equipment

+----+-------+-------+
| id | type  | brand |
+----+-------+-------+
|  1 |     1 |     2 |
|  2 |     2 |     1 |
|  3 |     3 |     3 |
+----+-------+-------+

I wrote this query:

$query = "select
    a.id,
    b.type,
    c.brand

from
    equipment a
        join type b
            on a.type=b.id
        join brand c
            on a.brand=c.id
where
    a.id=3";

it shows me the result below:

+----+--------+---------+
| id | type   | brand   |
+----+--------+---------+
|  3 | typeC  | brandC  |
+----+--------+---------+

How shall I modify my query to show the parent brand as well if a brand has a parent brand. for instance brandC is a sub brand of brandA. So my result should look like:

+----+--------+---------+----------------+
| id | type   | brand   | Parent Brand   |
+----+--------+---------+----------------+
|  3 | typeC  | brandC  | brandA         |
+----+--------+---------+----------------+

and when there is no parent brand it leaves the cell blank

also How will I modify the above query to see all equipment with their brands and sub brands like below.

+----+--------+---------+----------------+
| id | type   | brand   | Parent Brand   |
+----+--------+---------+----------------+
|  1 | typeA  | brandB  |                |
|  2 | typeB  | brandA  |                |
|  3 | typeC  | brandC  | brandA         |
+----+--------+---------+----------------+

Answers


Because not all brands have valid parents, you need a left outer join for the parent:

select e.id, t.type, b.brand, bp.brand as parentBrand
from equipment e join
     type t
     on e.type= t.id join
     brand b
     on e.brand = b.id left outer join
     brand bp
     on b.parent = bp.id
where e.id = 3;

I also changed the aliases to be abbreviations of the table names. This makes the query much easier to follow.


SELECT * FROM brands b
JOIN equipment e on e.brand = b.id -- match type to brand
JOIN types t on t.id = e.type -- get type names
JOIN brands p on p.id = b.parent; -- get parent brand

Need Your Help

Testing injected service that is a constructor function

angularjs testing jasmine

Say I have a service like this, where a car gets an engine service injected, which is a constructor function:

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.