Logic for consultation in subscription system

Question

Hello, I’m developing a system of online plan subscriptions for one client, and I’ve encountered a number of difficulties. I’ll explain a little bit about how the system works and what difficulties I’m having.

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

So far, OK. However, I’m having difficulty sorting some items. I need to list all the records of registered Users. However, you should list only those with the regular subscription. This is the first difficulty.

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

This query brings active users of the city Curitiba: 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 bring only users who are on subscriptions who have plans that are with regular payments? I do not know if I was clear, rs.

Follow the tables and an example of registration to illustrate:

CUSTOMERS

CREATE TABLE IF NOT EXISTS 'sistema_client' ( 'cliId' int(11) NOT NULL, 'cliNome' varchar(255) NOT NULL, 'cliAtivo' Enum(’S','N') NOT NULL, 'cliExcluido' Enum(’S','N') NOT NULL ) ENGINE=Myisam AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;

INSERT INTO 'sistema_client' ('cliId', 'cliNome', 'cliAtivo', 'cliExcluido') VALUES (1, 'Maykel Esser', ’S', 'N');

PLANS

CREATE TABLE IF NOT EXISTS 'system_plans' ( '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, 'plaExcluido' Enum(’S','N') NOT NULL ) ENGINE=Myisam AUTO_INCREMENT=12 DEFAULT CHARSET=utf8;

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

SIGNATURES

CREATE TABLE IF NOT EXISTS 'system_signature' ( 'Assid' int(11) NOT NULL, 'assDataCadastro' datetime NOT NULL, 'assAtivo' Enum(’S','N') NOT NULL, 'assExcluido' Enum(’S','N') NOT NULL ) ENGINE=Myisam AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;

INSERT INTO 'system_signature' ('Assid', 'assDataCadastro', 'assAtivo', 'assExcluido') VALUES (1, '2017-04-14 22:42:33', ’S', 'N');

CLIENT-SIGNATURE

CREATE TABLE IF NOT EXISTS 'system_signed client' ( '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 'system_signed client' ('ascId', 'Assid', 'cliId') VALUES (1, 1, 1);

SIGNATURE-PLAN

CREATE TABLE IF NOT EXISTS 'system_signing' ( 'aspId' int(11) NOT NULL, 'aspSituacao' int(1) NOT NULL,
'aspDataVencing' 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_signature_plan' ('aspId', 'aspSituacao', 'aspDataVencing', 'Assid', 'Plaid') VALUES (1, 1, 0, 1, 1), (2, 1, 0, 1, 9);

Answer 1

Desired query:

"Users who are in subscriptions that have plans that are with regular payments."

First step:

We need to identify what "plans that are with regular payments" means. In my opinion "plans that are with regular payments" are those that have assAtivo='S' table sistema_assinatura and aspSituacao = 1 table sistema_assinatura_plano.

Second step:

Try to understand all the data we should take for the query.

A) Users name comes from the table sistema_cliente, spine cliNome.

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

C) The table sistema_assinatura keeps the status of the signature (assAtivo) and the countryside assId is used to link sistema_assinatura_cliente and sistema_assinatura_plano.

D) The table sistema_assinatura_cliente associates a subscription to a client.

E) The table sistema_assinatura_plano associates a signature to the chosen plan.

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 making all the table linkages in 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

---

Reply to the comment

Using Inner Join with explicit word or more oldschool linking the tables including the names in FROM and the associations in WHERE gives in it. However, there is a certain common sense that says the less sub-queries, the better. I don’t know to what extent this is true because it’s not always that a sub-query will slow down the query, it depends a lot on the internal optimizer of the database.

Run this query below and see if it comes out the same result you pasted in the comment. I changed the joins to this way oldschool what I use most and removed the Queries:

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

Answer 2

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