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

There is this plate with garages and cars.

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?

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
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