Request for several conditions
Most recently, on the same site, I was told how to make a request for data from the four tables, taking this as an example, I made another request:
SELECT L.*, (SELECT group_concat(distinct client.client_full_name) FROM client JOIN cases ON (client.client_id=cases.client_id) JOIN lawyer ON (lawyer.lawyer_id=cases.lawyer_id) GROUP BY lawyer.lawyer_full_name) clientname, (SELECT count(cases.lawyer_id) FROM cases JOIN lawyer ON (lawyer.lawyer_id=cases.lawyer_id) WHERE cases.case_archive=1 GROUP BY lawyer.lawyer_id) archive FROM lawyer L
Makes me a mistake, I don't understand why and how to fix it. ♪ ♪
The purpose of the request for rows for each lawyer (table lawyer) is all (*) of his data + the list of his clients now (from the case table) and of his archive case files (also cases, only cases.case_archive=1).
I'm also presenting an online database model for user-friendly queries: http://sqlfiddle.com/#!9/9130c/
And last, I'm asking you to describe every step of the request, I'm very confused about the requests, and I'm just blocking the forum with almost the same topics. ♪ ♪
In fact, the questioning on the sample list is an emergency. Yeah, he's comfortable. But usually he's making a harder request. It should be used when conventional funds are not chastised. For example, if the basic request, by clipping several tables, creates a volume of records that they cannot already receive the amount of a field, that is, the same value of the field is met several times.
Example plan for such a request:
Determine the participating tables. We're in charge.
lawyerI mean, it's the one that needs to get all the records, no matter what the cases and the clients are. A table should be added to obtain the number of cases
clientand she's doing business.
Establish a baseline request:
SELECT * FROM lawyer L LEFT JOIN cases S ON (L.lawyer_id=S.lawyer_id) LEFT JOIN client C ON (S.client_id=C.client_id)
We use it. only
LEFT JOIN- so the lawyers will be all. No records are required in the remaining tables.
Let's see if we're looking at all the records we need for the next summarization. Yeah, that's it. There's names of all the clients, and there's all the files on the lawyer. Although we're only interested in archival cases, and we've got all the files on our request (if there's no business, we're not gonna get all the clients. So we're gonna have to do some tricky thing. Applicable
Add grouping and group functions
SELECT L.*, group_concat(distinct C.client_full_name) clients, sum(IF(S.case_archive=1,1,0)) case_arx_sum, count(S.case_id) case_all_sum FROM lawyer L LEFT JOIN cases S ON (L.lawyer_id=S.lawyer_id) LEFT JOIN client C ON (S.client_id=C.client_id) GROUP BY L.lawyer_id
For group function with IF.
count(S.case_id)Considers that all NULL is meaningless, and so he will consider the case, and he will not touch the records that only a lawyer without a single case. And we want to get all the files we're doing.
SUMCondition to be issued for summation 1 for archives and 0 in all other cases, including absence of cases
Let's say we'd have to show this request the same summary punishment of the clients of this lawyer. If we stick the table
punishmentIn the same request, some cases will be filed several times, since the records are simply multiplied in cases where there are several penalties for the client. If there are two cases and two punishments, we'll get four records, all versions of the case/order. In a situation like this, it's gonna be hard to count a stake in something. That's why we're gonna need a subpoena on the sample list.
The request must return One the value of each lawyer. We're writing it first as a separate request that will be relevant to the ID of one specific lawyer, so we only need an ID lawyer, so the bars themselves are not required to use, until the ID we need, we can get through the case table.
cases) Receive such a request for counsel ID=1:
SELECT sum(P.punishment_value) FROM punishment P, cases C1 WHERE C1.client_id=P.client_id and C1.lawyer_id=1
It is only left to be placed on the basic request, using the relevant id from the basic request as id counsel (
SELECT L.*, group_concat(distinct C.client_full_name) clients, sum(IF(S.case_archive=1,1,0)) case_arx_sum, count(S.case_id) case_all_sum, (SELECT sum(P.punishment_value) FROM punishment P, cases C1 WHERE C1.client_id=P.client_id and C1.lawyer_id=L.lawyer_id) punish_sum FROM lawyer L LEFT JOIN cases S ON (L.lawyer_id=S.lawyer_id) LEFT JOIN client C ON (S.client_id=C.client_id) GROUP BY L.lawyer_id