CODEWITHSUNDEEP
mysqlsql
user1801810
user1801810

Reputation: 614

SQL Join vs Sub-query

I'm running MySQL 5.1.71. In my database there are three tables - load, brass and mfg with load being my "main" table. My goal is to query load and have mfg.name included in the results. I've tried various iterations of JOIN clauses vs sub-queries both with and without WHERE clauses. It seems this should be pretty trivial so I'm not sure how I can't arrive at the solution.

load
-------------------------
| id | desc  | brass_id |
-------------------------
|  1 | One   |        2 |
|  2 | Two   |        1 |
-------------------------

brass
---------------
| id | mfg_id |
---------------
|  1 |      6 |
|  2 |      8 |
---------------

brass_mfg
------------------------
| id | name            |
------------------------
|  6 | This Company    |
|  8 | That Company    |
------------------------

My desired results would be...

results
---------------------------
| load  | mfg             |
---------------------------
| One   | That Company    |
| Two   | This Company    |
---------------------------



EDIT
The previously provided sample data (above) has been updated. Also, below are the query I'm running and the results I'm getting. The company is wrong in each record that is returned. I've included in the query and the results the IDs across the tables. The company names that appear are not the names in for the IDs in the mfg table.

SELECT 
    load.id AS "load.id", 
    load.brass_id AS "load.brass_id", 
    brass.id AS "brass.id", 
    brass.mfg_id AS "brass.mfg_id", 
    brass_mfg.id AS "brass_mfg.id", 
    brass_mfg.name AS "brass_mfg.name" 
FROM `load`
LEFT JOIN brass ON load.brass_id = brass.id
LEFT JOIN brass_mfg ON brass.id = brass_mfg.id 


-----------------------------------------------------------------------------------------
| load.id | load.brass_id | brass.id | brass.mfg_id | brass_mfg.id | brass_mfg.name     |   
-----------------------------------------------------------------------------------------
| 1       | 2             | 2        | 6            | 2            | Wrong Company      |
| 2       | 1             | 1        | 8            | 1            | Incorrect Company  |
-----------------------------------------------------------------------------------------

Upvotes: 2

Views: 223

Answers (3)

Wajih
Wajih

Reputation: 4383

You have an error in your join query. Try this one:

Select 
 l.id AS "load.id", 
 l.brass_id AS "load.brass_id", 
 b.id AS "brass.id", 
 b.mfg_id AS "brass.mfg_id",
 m.id AS "brass_mfg.id",
 m.`name` AS "brass_mfg.name"
FROM `load` as l
LEFT JOIN brass as b ON l.brass_id = b.id
LEFT JOIN brass_mfg as m ON b.mfg_id = m.id

enter image description here

You need LEFT JOIN only

Upvotes: 0

Excalibrus
Excalibrus

Reputation: 130

If this is single - single relationship, why having middle table? In your case the best scenario is simple join.

SELECT pt.desc as Product, mfg.name as Mfs
FROM Product pt
Join Lot lt on lt.id = pt.lot_id
Join Mfg mf on mf.id = lt.mfg_id

Upvotes: 0

Matt
Matt

Reputation: 15061

Look at your tables and see what data relates to one another then build up joins table by table to get your desired output.

SELECT p.desc AS Product, m.name AS mfg
FROM product p
INNER JOIN lot l ON p.lot_id = l.id
INNER JOIN mfg m ON l.mfg_id = m.id

Upvotes: 2

Related Questions