SQL query from 3 tables

As I understand it, you need to choose the minimum prices for all products in all stores, even if they are not specified. Alternatively, you can use such a query based on what the distinguished nick_n_a has suggested :

 SELECT signalizacii.*, name, coalesce(MIN(cost), 0) FROM uc1 JOIN signalizacii LEFT JOIN costusers ON costusers.id_user = uc_id AND costusers.id_sign = id GROUP BY name, model 

Working example: http://sqlfiddle.com/#!9/c9294/1/0

With the selection of the city:

 SELECT signalizacii.*, name, coalesce(MIN(cost), 0) FROM uc1 JOIN signalizacii LEFT JOIN costusers ON costusers.id_user = uc_id AND costusers.id_sign = id WHERE city='Краснодар' GROUP BY name, model 

Are you trying to kill two birds with one stone? Why is it so necessary to do everything with a single SQL query? What will you achieve with this?

Surely the list of alarms you have more or less fixed, that is, does not change every second. So you can download a list of all alarms with a single request, save this list in the cache in the desired order. Then, as necessary, request prices for a particular city, while visualizing, substituting prices for each alarm into the column.

Think about it this way: it’s not so important to do everything in one request, as it is important that you yourself can understand what is happening here in a year or two, when you need to fix a bug very urgently. In one case, you immediately immediately figure out what was happening, spending 10 minutes to fix. In another case, you will spend an hour or two to restore the train of thought and remember all the hacks used to write the request.

There are a lot of LEFT JOINs, it would be better to do zero prices with a separate UNION, and otherwise it works as requested.

 SELECT DISTINCT s.*, ISNULL(m.mincost, 0) AS 'mincost' FROM #signal_signalizacii s LEFT JOIN #signal_costusers u ON u.id_sign = s.id LEFT JOIN #signal_uc1 c ON u.[user_id] = c.uc_id AND c.city = 'Краснодар' OUTER APPLY ( SELECT MIN(cost) AS 'mincost' FROM #signal_costusers WHERE id_sign = s.id AND [user_id] = c.uc_id AND c.uc_id IS NOT NULL ) m 

Can you clarify all the same in which database the query is executed? this is mysql oracle postgre or mssql

If it were Oracle, one could use analytical functions: http://www.sql.ru/blogs/oracleandsql/1926 , construct

 MIN(cost) OVER(Partition BY city) 

works exactly as you need. If you specify the database, you can search for an implementation similar to analytical functions in Oracle

Sorry I did not notice that this is Mysql, then please check: http://sqlfiddle.com/#!9/0de13/11

deal with the zeros themselves then what to replace them:

 /* все сигнализации из таблицы signalizacii */ SELECT s.* FROM signalizacii s ; /* и добавить минимальную цену по городу, т.е. добавляем поле Город min_cost_s_c - минимальная цена на сигнализацию по городу */ SELECT s.*, u.city, min(c.cost) as min_cost_s_c FROM signalizacii s left join costusers c ON s.id = c.id_sign left join uc1 u ON c.id_user = u.user_id group by s.id, s.firma, s.model, u.city; /* если нужна минимальная цена по городу не зависимо от сигнализации, просто минимальная цена то делаем так */ SELECT s.*, t.city, t.cost FROM signalizacii s left join ( select u.city, min(c.cost) as cost, max(c.id_sign) as id_sign from costusers c left join uc1 u ON c.id_user = u.user_id GROUP BY u.city) t ON s.id = t.id_sign; 

or if max confuses (c.id_sign), then you can try differently

 /* если нужна минимальная цена по городу не зависимо от сигнализации, просто минимальная цена то делаем так */ SELECT s.*, t.city, t.cost FROM signalizacii s left join ( select u.city, min(c.cost) as cost, group_concat(c.id_sign) as id_sign from costusers c left join uc1 u ON c.id_user = u.user_id GROUP BY u.city) t ON s.id IN (t.id_sign); 

  left join uc1 on costusers.id_user = uc1.user_id where uc1.city = 'Краснодар' 

This is such a way to invisibly write an inner join , possibly simultaneously confusing and query optimizer. Left join in case the string is not found uses for all fields NULL . NULL can not be equal to anything, even to itself. Therefore, a comparison with a constant string does not give true and the whole string is discarded. What turns a query into an inner join .

You must either consider the possibility of NULL appearing explicitly.

  left join uc1 on costusers.id_user = uc1.user_id where (uc1.city is null or uc1.city = 'Краснодар') 

Or, which is usually clearer, move the search condition to the join condition:

  left join uc1 on costusers.id_user = uc1.user_id and uc1.city = 'Краснодар' -- where при этом уже не нужен 

Eventually

Getting all the rows from the table with the strange name signalizacii and adding the minimum value proposition in the selected city

  select signalizacii.*, COALESCE(MIN(CASE WHEN costusers.cost != 0 THEN costusers.cost END), 0) from signalizacii left join costusers on id_sign = id left join uc1 on costusers.id_user = uc1.user_id and uc1.city = 'Краснодар' group by signalizacii.id 

Or you can rewrite the query like this:

 select signalizacii.*, COALESCE(mincost, 0) from signalizacii left join ( select id_sign, min(costusers.cost) as mincost from costusers join uc1 using(id_user) where uc1.city = 'Краснодар' and costusers.cost > 0 -- исходно у вас != 0, наверное логичнее > 0? group by id_sign ) m ON id = id_sign 

Use on health)

 select s.*, IF(costs.minCost is null, 0, costs.minCost) as minCost FROM signalizacii s LEFT JOIN (select min(cost) as minCost, c.id_sign FROM costusers c LEFT JOIN uc1 ON c.id_user = uc1.uc_id WHERE city = "BAKU" GROUP BY c.id_sign) costs ON s.id = costs.id_sign