CODEWITHSUNDEEP
mysqljoin
epineda
epineda

Reputation: 5

Return value from a third table field based on second table id

I have this tables

ADDS (1)

+----+--------------+--------------+
| id | name of add  | Date         |
+----+--------------+--------------+
|  1 | Add01        | March 01     |
|  2 | Add02        | March 02     |
|  3 | Add03        | March 03     |
|  4 | Add04        | March 04     |
+----+--------------+--------------+

TYPE OF ADDS (2)

+----+----------+
| id | Add id   |
+----+----------+
| 21 | 1        |  NOTE: Add id of table (2) = id of table (1)
| 22 | 2        |
| 23 | 3        |
| 24 | 4        |
+----+----------+

NAMES OF TYPES (3)

+----+-----------+--------------+
| id | Type id   | Name         |
+----+-----------+--------------+
| 31 | 21        | Text add     |
| 32 | 22        | Banner       | NOTE: Type id of table (3) = id of table (2)
| 33 | 23        | Video add    |
| 34 | 24        | Other        |
+----+-----------+--------------+

I need a report like this:

+--------+-----------+--------------+
| Add id | Add name  | Type of add  |
+--------+-----------+--------------+
|  1     | Add01     | Text add     |
|  2     | Add02     | Banner       |
|  3     | Add03     | Video add    |
|  4     | Add04     | Other        |
+--------+-----------+--------------+

(Add id from table (1), Add name from table (1), Type of add from table (3))

So far I can do a SELECT query LEFT JOIN table 1 and 2 but I don't know how to return the value Name of type from table 3. How can I do it?

Upvotes: 0

Views: 282

Answers (4)

Anuj Jain
Anuj Jain

Reputation: 87

Try this query:

select ADDS.nameofadds,TYPEOFADDS.ADDid,NAMEOFTYPES.Name
    FROM ADDS 
    JOIN TYPEOFADDS 
         ON  ADDS.id=TYPEOFADDS.ADDid
    JOIN NAMEOFTYPES 
         ON  TYPEOFADDS.id=NAMEOFTYPES.TYPEid

Upvotes: 1

Jared
Jared

Reputation: 301

SELECT `table1`.`id` AS `Add id`, `table1`.`name_of_add` AS `Add name`, `table3`.`Name` AS `Type of add`
FROM `table1`, `table2`, `table3`
WHERE `table1`.`id` = `table2`.`add_id` AND `table2`.`id` = `table3`.`Type_id`

Upvotes: 1

Moyed Ansari
Moyed Ansari

Reputation: 8461

SELECT  ad.id, ad.name,t.name
FROM ADDS ad
LEFT JOIN TYPEADDS ta ON ta.add_id = ad.id
LEFT JOIN TYPES t ON t.id = ta.id

Upvotes: 1

eggyal
eggyal

Reputation: 126035

Join again with table 3:

SELECT t1.`id`          AS `Add id`,
       t1.`name of add` AS `Add name`,
       t3.`Name`        AS `Type of add`
FROM   `ADDS` AS t1
  LEFT JOIN `TYPE OF ADDS`   AS t2 ON t2.`Add id`  = t1.`id`
  LEFT JOIN `NAMES OF TYPES` AS t3 ON t3.`Type id` = t2.`id`

Upvotes: 1

Related Questions