Is it possible to attach an additional column based on the table data?

There is this plate with garages and cars.
5c9cf74b4fa6c768754735.png
1 the garage can be up to 2 machines.
I need to get all the garages where for example there is a field to add in each line with the answer 1 parameter -> second car in the garage.
Is it possible to make a 1m solution?
5c9cf8d851cbd837573396.png
Logic dictates that if possible, it is done through a join but the brain is not enough, please tell me how to be.
March 19th 20 at 09:04
2 answers
March 19th 20 at 09:06
Solution
SELECT garages.garage, garages.car AS 1st, garages2dn.car AS 2nd
 FROM garages 
 INNER JOIN garages ON garages AS garages2dn.garage = garages2dn.garage
 WHERE (garages.car = 'niva') AND (garages2dn.car <> 'niva')

But if there is a second machine:
SELECT garages.garage, garages.car AS 1st, garages2dn.car AS 2nd
FROM garages 
 LEFT OUTER JOIN garages AS garages2dn 
 ON garages.garage = garages2dn.garage AND garages.ID <> garages2dn.ID
WHERE (garages.car = 'niva')
but if there is not a field?

PS
Reread the question - Yes, it's field. Hats off to you.
But my query is universal)) - Abdul_Luettgen commented on March 19th 20 at 09:09
@Abdul_LuettgenIn terms of tasks niva. - Adol commented on March 19th 20 at 09:12
@Adol, Yes, I have altered the review. Sorry toaster not stored change history comments and answers would be clearer what is happening here.

Now I wonder what will the author)) - Abdul_Luettgen commented on March 19th 20 at 09:15
But if there is no other machine or it is also niva:


Then, if you take into account one car in the garage:

SELECT
car1,
car2,
t1.garage
FROM (SELECT
 MIN(car), car1,
garage
 FROM garages
 GROUP BY garage) t1
 JOIN (SELECT
 MAX(car) car2,
garage
 FROM garages
 GROUP BY garage) t2
 ON t1.garage = t2.garage
- Abdul_Luettgen commented on March 19th 20 at 09:18
@Adol, you see the old man in it. And do not tell, what exactly is my query bad? not from the point of view of the question, namely in terms of - is it possible to pereproshit to do better? If you don't mind. - Abdul_Luettgen commented on March 19th 20 at 09:21
@Abdul_Luettgen, Error: if not the second car, she turns to niva.
Inaccuracy: lada first paired with niva. - Adol commented on March 19th 20 at 09:24
@Adol, can you tell me why Your requests may be given a syntax error?
looks powerful, but the start comes out) - cary_Dietrich commented on March 19th 20 at 09:27
@cary_Dietrich, square brackets should be removed. I may be blunt, but the brackets work in mssql. I have the queries work fine - Abdul_Luettgen commented on March 19th 20 at 09:30
@Abdul_Luettgen, all right, thank you very much! - cary_Dietrich commented on March 19th 20 at 09:33
@cary_Dietrich, Oh, well, at least someone I helped. It's nice. Good evening, colleague. - Abdul_Luettgen commented on March 19th 20 at 09:36
March 19th 20 at 09:08
Solution
Call me crazy, but:
SELECT
car1,
car2,
t1.garage
FROM (SELECT
 MIN(car), car1,
garage
 FROM garages
 GROUP BY garage) t1
 JOIN (SELECT
 MAX(car) car2,
garage
 FROM garages
 GROUP BY garage) t2
 ON t1.garage = t2.garage
WHERE car1 <> car2


Is relevant of course only at the highest two cars in the garage.

I apologize in advance I user @FanatPHP for it, because most likely, I told him it would ruin the mood completely))

I will be glad to see the correct solution for SQL me very interested.

Find more questions by tags MySQL