It is necessary to improve the SQL query code

Question

Here is the SQL query code:

`USE [transport] GO SELECT [location].[location] AS Местоположение,[car_list].[status] AS Свободен FROM [dbo].[location], [dbo].[car_list] WHERE [location].[loc_id] = [car_list].[location] AND [car_list].[status] = 1 ORDER BY [location].[location]'

I want to make it more advanced so that I can display 3 types of status (free, busy, pause) of transport vehicles when the driver selects the status.

At the moment I can only display one status value.

How to expand the code and display all statuses in the same table as in the picture?

My query displays the data in the following form:

WHERE [location].[loc_id] = [car_list].[location] AND [car_list].[status] = 1 You are in the request output data with status = 1, remove this condition and you will be happy, you will receive with all statuses.
And about the output of all results as in the table - this is a separate question.
You need to make a conclusion on the status: make a Status column, hit on 3 pieces, respectively.
And depending on the location, consider (as I understand it) the number of drivers with a certain status.
And for a more meaningful response, a code is needed on how drivers receive this status (or rather, in which variable it is stored).
And another moment, you want to display in phpmyadmin or on a page, in a php file
it is stored in the server and via MS SQL sends SQLite to the driver’s phone

Accepted Answer · 2017-07-20T12:31:55

For example, you can. Let correct, if the decision is not optimal.

 SELECT location, SUM(free), SUM(busy), SUM(pause) FROM ( SELECT [location].[location] AS location, CASE [car_list].[status] WHEN 1 THEN 1 ELSE 0 END AS free, CASE [car_list].[status] WHEN 2 THEN 1 ELSE 0 END AS busy, CASE [car_list].[status] WHEN 3 THEN 1 ELSE 0 END AS pause FROM [dbo].[location], [dbo].[car_list] WHERE [location].[loc_id] = [car_list].[location] ) AS a GROUP BY location ORDER BY location

I take the ID from the status table, through the "foreign key" after I attach the value so far only on the table and the software part of the drivers is not ready yet
@beginneroot, I am not aware of the exact structure of the database, whether the necessary way, for example, are stored taxi driver statuses.
I'm also not sure about the exact syntax of your DBMS, the algorithm should work.
The server generates an error: Message 156, level 15, state 1, line 5 Incorrect syntax around the keyword "FROM".
Message 156, level 15, state 1, line 13 Incorrect syntax around the keyword "GROUP".
How can I show the database structure on the new question I can upload?

It is necessary to improve the SQL query code

1 answer 1

More articles: