如何通过使用某些连接或其他查询来检索所需的数据/结果？

Question

I am having a problem to fetch the required result in php/mysql. I am seeking your help to get it solved.

Here is my problem -

I have two tables

1. new_equipment

2. new_equipment_trims

new_equipment table -

equip_id              equip_name    
1                      Air Conditioning
2                      Brake System
3                      Mobile Connectivity
4                      Steering Wheel
5                      Navigation System

new_equipment_trims table -

row_id        trim_id          equip_id               equip_name                               equip_val
1             101286108        1                      Front Air Conditioning Zones             dual
2             101286108        1                      Front Air Conditioning                   automatic climate control
3             101286108        1                      Rear Heat                                rear ventilation ducts
4             101286108        1                      Air Filtration                           interior air filtration
5             101286108        2                      Rear Brake Type                          disc
6            101286108         2                      Electronic Brakeforce Distribution       electronic brakeforce distribution
7            101286108         2                      Front Brake Diameter                     12.6

You can see the relationship between these two tables is equip_id.

Now i want to show the result like this -

Obviously this is the result for equip_id = 1 and trim_id = 101286108. It can be for any equip_id and trim_id. But if one gets going fine then others will also come in this format. Thus the loop will go on and first show the main equipment name from first table then the equipment name and its value from second table for each equip_id.

Air Conditioning (Main Equipment Name)
(Equipment Name)                                     (Equipment Value)
Air Filtration                                       interior air filtration
Front Air Conditioning                               automatic climate control
Front Air Conditioning Zones                         dual
Rear Heat                                            rear ventilation ducts

So please tell me what should be the accurate query to bring this result.

I am trying this -

SELECT `ne`.`equip_id` , `ne`.`equip_name` AS 'main_equip_name', `net`.`equip_name` AS 'equip_name', `net`.`equip_val` AS 'equip_value'
FROM `new_equipment` AS `ne`
INNER JOIN `new_equipment_trims` AS `net` ON `ne`.`equip_id` = `net`.`equip_id`
WHERE `net`.`trim_id` =101286108
GROUP BY `ne`.`equip_name`
ORDER BY `ne`.`equip_name`

But its not working correctly and doesn't fetch the above desired result. So please help me on this.

EDIT -

By using your inner join with union, the query is bringing the correct result that i wanted, but i am having now a new issue. I notice those rows with main equipment name are also coming in the result set which dont have their sub equipment and their values. To understand it better, please look at the snapshot -

You can see that the main equipment like Sunroof etc. are not having any sub equipment name and values but they are still coming. How can i remove them from the result? Here is my query -

SELECT 0 AS RowType, `equip_id` , `equip_name` , 'Main Equip' AS `equip_value`
FROM `new_equipment`
UNION SELECT 1 , `net`.`equip_id` , `net`.`equip_name` , `net`.`equip_val`
FROM `new_equipment` AS `ne`
INNER JOIN `new_equipment_trims` AS `net` ON `ne`.`equip_id` = `net`.`equip_id`
WHERE `net`.`trim_id` =101286108
ORDER BY `equip_id` , RowType

Answer 1

So to get for equip_id=1 and trim_id=101286108 you can do this:

SELECT ne.equip_id, ne.equip_name AS main_equip_name, net.equip_name, net.equip_val
FROM new_equipment AS ne
INNER JOIN new_equipment_trims AS net 
  ON ne.equip_id = net.equip_id
WHERE net.equip_id=1
  AND net.trim_id=101286108

However, to get one query that shows you the main equipment name followed by the subitems, you could try something like this:

SELECT 0 AS RowType, equip_id, equip_name, '(Main Equip)' AS equip_value
FROM new_equipment 
UNION
SELECT 1, net.equip_id, net.equip_name, net.equip_val
FROM new_equipment AS ne
INNER JOIN new_equipment_trims AS net 
  ON ne.equip_id = net.equip_id
ORDER BY equip_id, RowType