Logic for query in signatures system

Question

Logic for query in signatures system

Navigation

#1 by (0 votes)
#2 by (0 votes)

0

Hello, I'm developing an online subscription system for a client, and I've come across a lot of difficulties. I will explain a bit about the system's operation and what difficulties I am having.

My system has 8 plans, registered in a table called Plans.
Each signature can have more than one plan.
A signature can contain more than one user.

So long, OK. However, I'm having difficulty sorting some items. I need to list all records of Registered Users. However, they should list only those with the regular signature. This is the first difficulty.

What defines the regular situation is a record in a table called SignaturePlane, which stores the ID of the signature and plan, and contains a flag called Situation, which if equal to 3, is OK. However, in this table, there is no user ID, since it is in another table, called UserName. I'm not sure how to capture this information in the same query I'm already doing:

This query brings the active users of Curitiba city: SELECT * FROM sistema_cliente c INNER JOIN sistema_assinatura_cliente ac ON ac.cliId = c.cliId WHERE cliAtivo = 'S' AND cliExcluido = 'N' AND cidId IN (SELECT cidId FROM sistema_assinatura_cidade WHERE cidId IN (2878)) LIMIT 0,15

How can I just bring users who are on subscriptions that have plans that are on regular payments? I do not know if I was clear, rs.

Here are the tables and an example of a cadastre to illustrate:

CLIENTS

CREATE TABLE IF NOT EXISTS 'client_client' ('cliId' int (11) NOT   NULL, 'cliName' varchar (255) NOT NULL, 'cliActive' enum ('S', 'N')   NOT NULL, 'cliExcluido' enum ('S', 'N') NOT NULL) ENGINE = MyISAM   AUTO_INCREMENT = 3 DEFAULT CHARSET = utf8;

INSERT INTO 'client_client' ('cliId', 'cliName', 'cliActive',   'cliExcluido') VALUES (1, 'Maykel Esser', 'S', 'N');

PLANS

CREATE TABLE IF NOT EXISTS 'plan_name' ('plaId' int (11) NOT   NULL, 'plaTitulo' varchar (255) NOT NULL, 'plaModality'   enum ('M', 'T', 'S', 'A') NOT NULL, 'plaValor' float NOT NULL,
  'plaDataCadastro' datetime NOT NULL, 'plaAtivo' enum ('S', 'N') NOT   NULL, 'plaExcluded' enum ('S', 'N') NOT NULL) ENGINE = MyISAM   AUTO_INCREMENT = 12 DEFAULT CHARSET = utf8;

INSERT INTO 'system_plans' ('plaId', 'plaTitulo', 'plaModalidade',   'plaValor', 'plaDataCadastro', 'plaAtivo', 'plaExcluido') VALUES (1,   'Traditional', 'M', 19.9, '2017-04-11 00:00:00', 'S', 'N'), (2,   'Traditional', 'T', 49.9, '2017-04-11 00:00:00', 'S', 'N'), (3,   'Traditional', 'S', 89.9, '2017-04-11 00:00:00', 'S', 'N'), (4,   'Traditional', 'A', 129.9, '2017-04-11 00:00:00', 'S', 'N'), (5,   'Office', 'M', 59.9, '2017-04-11 00:00:00', 'S', 'N'), (6,   'Office', 'T', 149.9, '2017-04-11 00:00:00', 'S', 'N'), (7,   'Office', 'S', 269.9, '2017-04-11 00:00:00', 'S', 'N'), (8,   'Office', 'A', 389.9, '2017-04-11 00:00:00', 'S', 'N'), (9,   'Benefits' - Bronze, 'M', 3, '2017-04-11 00:00:00', 'S', 'N'), (10,   'Benefits' - 'Silver', 'M', 7, '2017-04-11 00:00:00', 'S', 'N'), (11,   'Benefits' - 'Gold', 'M', 14, '2017-04-11 00:00:00', 'S', 'N');

SIGNATURES

CREATE TABLE IF NOT EXISTS 'system_name' ('assId' int (11)   NOT NULL, 'assDataCadastro' datetime NOT NULL, 'assAtive'   enum ('S', 'N') NOT NULL, 'asExcluded' enum ('S', 'N') NOT NULL)   ENGINE = MyISAM AUTO_INCREMENT = 5 DEFAULT CHARSET = utf8;

INSERT INTO 'enrollment_name' ('assId', 'enrollment_address',   'Assertive', 'AssExcluded') VALUES (1, '2017-04-14 22:42:33', 'S',   'N');

SIGNATURE-CLIENT

CREATE TABLE IF NOT EXISTS 'customer_system' ('ascId'   int (11) NOT NULL, 'assId' int (11) NOT NULL, 'cliId' int (11) NOT   NULL) ENGINE = MyISAM AUTO_INCREMENT = 5 DEFAULT CHARSET = utf8;

INSERT INTO 'customer_system' ('ascId', 'assId', 'cliId')   VALUES (1, 1, 1);

PLAN SIGNATURE

CREATE TABLE IF NOT EXISTS 'plan_name' ('aspId'   int (11) NOT NULL, 'aspSituation' int (1) NOT NULL,
  'aspDataVencer' int (2) NOT NULL, 'assId' int (11) NOT NULL,
  'plaId' int (11) NOT NULL) ENGINE = InnoDB AUTO_INCREMENT = 6 DEFAULT   CHARSET = utf8;

INSERT INTO 'system_info_name' ('aspId', 'aspSituation',   'aspDataVencimento', 'assId', 'plaId') VALUES (1, 1, 0, 1, 1), (2, 1,   0, 1, 9);

mysql

asked by anonymous 30.04.2017 / 03:31

2 answers

Shopping cart How can I set Ionic ranges with a predetermined initial value?

score 0 · Answer 1

SELECT * FROM sistema_cliente c INNER JOIN sistema_assinatura_cliente ac ON ac.cliId = c.cliId WHERE cliAtivo = 'S' AND cliExcluido = 'N' AND cidId IN (SELECT cidId FROM sistema_assinatura_cidade WHERE cidId IN (2878)) AND c.cliId IN (SELECT cliId FROM sistema_assinatura a INNER JOIN sistema_assinatura_cliente ac ON ac.assId = a.assID INNER JOIN sistema_assinatura_plano ap ON ap.assId = a.assID WHERE ap.aspSituacao = 3) LIMIT 0,15

score 0 · Answer 2

Query desired:

"Users who are on signatures that have plans that are with regular payments. "

First step:

We need to identify what we mean by "plans that have regular payments." In my view, "plans that are with regular payments" are those that have assAtivo='S' of the sistema_assinatura and aspSituacao = 1 table of the sistema_assinatura_plano table.

Step 2:

Try to understand all the data that we should get for the query.

A) The name of the users comes from the sistema_cliente table, cliNome column.

B) The name of the plans and values comes from the table sistema_planos , columns plaTitulo and plaValor .

C) The sistema_assinatura table stores the subscription status ( assAtivo ) and the assId field is used to link sistema_assinatura_cliente and sistema_assinatura_plano .

D) The sistema_assinatura_cliente table associates a signature with a client.

E) The sistema_assinatura_plano table associates a signature with the chosen plane.

Based on this understanding of A, B, C, D, and E, we can assemble the desired query, using select the fields we want to display and doing all table bindings in the where.

SELECT 
    cliNome, plaTitulo, plaValor
FROM 
    sistema_cliente, 
    sistema_planos, 
    sistema_assinatura, 
    sistema_assinatura_cliente, 
    sistema_assinatura_plano
WHERE
    assAtivo='S'
AND
    aspSituacao = 1
AND
    sistema_assinatura_cliente.assId = sistema_assinatura.assId
AND
    sistema_assinatura_cliente.cliId = sistema_cliente.cliId    
AND
    sistema_assinatura_plano.assId = sistema_assinatura.assId
AND
    sistema_assinatura_plano.plaId = sistema_planos.plaId

Comment response

Using inner join with explicit word or oldschool way linking the tables including the names in the FROM and the associations in the WHERE gives the same. However, there is a certain common sense that says the less sub-queries, the better. I do not know to what extent this is true because it is not always that a sub-query will slow the query down, it depends a lot on the bank's internal optimizer.

Run this query below and see if there is the same result as the one you put in the comment. I changed the joins to that oldschool that I use the most and removed the subqueries:

SELECT DISTINCT 
    c.cliId,
    c.cliNome,
    c.cliAtivo,
    c.cliExcluido
FROM 
    sistema_cliente c, 
    sistema_assinatura_cliente ac,  
    sistema_assinatura a, 
    sistema_assinatura_plano ap,
    sistema_assinatura_cidade
WHERE 
    ac.cliId = c.cliId 
AND
    cliAtivo = 'S' 
AND 
    cliExcluido = 'N' 
AND 
    cidId=2878
AND 
    ap.aspSituacao = 3
AND
    ac.assId = a.assID 
AND
    ap.assId = a.assID 
LIMIT 0,15