Request for several conditions

[Avante]

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

[Nykeriab]

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:

1. Determine the participating tables. We're in charge. lawyer I 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 cases Table clientand she's doing business.

2. 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 IF♪

3. 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. SUM Condition 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 punishment In 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 (C1.lawyer_id=L.lawyer_id

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