CODEWITHSUNDEEP
phpmysqlsql
akhil.cs
akhil.cs

Reputation: 691

MySQL Complex Joining

I have two tables (vehicles, available_colors) as following,

vehicles

id  vehicle_name
--  ------------
1   Honda Dio
2   Yamaha FZ
3   RE Classic 350

available_colors

id  vehicle_id  color
--  ----------  ----
1   1           Red
2   1           Yello
3   1           White
4   1           Black
5   2           Metalic Red
6   2           Metalic Black
7   2           Metalic Blue
8   3           Classic Red
9   3           Classic Black
10  3           Classic Silver

I want to perform an operation with following criterias

IF available_colors.color LIKE '%Metalic Red%'

THEN return that "vehicle" with all the "available colors",

as follows,

id  vehicle_name    color
--  ------------    ----- 
2   Yamaha FZ       Metalic Red
2   Yamaha FZ       Metalic Black
2   Yamaha FZ       Metalic Blue

Upvotes: 1

Views: 94

Answers (5)

Gordon Linoff
Gordon Linoff

Reputation: 1269853

If you were willing to get the colors on a single row for each vehicle, you could do:

select v.id, v.vehicle_name, group_concat(ac.color) as colors
from vehicles v join
     available_colors ac
     on v.id = ac.vehicle_id
group by v.id, v.vehicle_name 
having sum(ac.color LIKE '%Metalic Red%) > 0;

Upvotes: 0

Agha Umair Ahmed
Agha Umair Ahmed

Reputation: 1020

Your query should be like this 

Select v.vehicle_name,ac.color From vehicles v LEFT JOIN available_colors ac ON ac.vehicle_id = v.id WHERE ac.color LIKE %Metalic%

Upvotes: 0

Mureinik
Mureinik

Reputation: 311383

There are two parts to this problem - the first is joining each vehicle with its corresponding colors. The second, is selecting only the vehicles that exist in Metallic Red:

SELECT        vehicles.id, vehicle_name, color
FROM          vehicles
JOIN          available_colors ON vehicles.id = available_colors.vehicle_id
WHERE EXISTS (SELECT 1
              FROM   available_colors
              WHERE  color LIKE '%Metallic Red%' AND 
                     vehicles.id = available_colors.vehicle_id)

Upvotes: 1

digitai
digitai

Reputation: 1842

Join both tables and try this:

select a.id, a.vehicle_name, b.color from vehicles a inner join available_colors b on 
a.id=b.vehicle_id where b.color like '%Metallic red%'

Upvotes: 0

Sujith Surendranathan
Sujith Surendranathan

Reputation: 2579

SELECT * FROM vehicles V1 
JOIN  available_colors AC1 ON V1.id = AC1.vehicle_id
WHERE EXISTS 
( SELECT * FROM available_colors ac 
where ac.vehicle_id = v.id and ac.color like '%Metalic Red%')

Upvotes: 4

Related Questions