Doubt in MySQL query

Navigation
1

Good morning I have a doubt in this MySQL query. As a first condition I want the id_gral to be equal to the one I send, second condition that is from a range of dates, third condition that the dst is equal to the one I send and finally that from accountcode I get the last two digits and I want it to be the same al in.

The query is working fine but it is not bringing me data. 

SELECT * FROM cdr c INNER JOIN general g ON g.id_gral = 4 AND c.calldate BETWEEN '2018-02-01%' AND '2018-10-25%' AND c.dst = '175' AND right(c.accountcode, 2) = 'in';
    
asked by Javier fr 26.10.2018 в 17:54
source

2 answers

4

First of all you are having a problem in declaring your sentence.

When you use a INNER JOIN you want to link one table with another through its columns, where the values exist in both tables, for this you use the ON of the following way: 

SELECT*
FROM tabla1 AS a
INNER JOIN tabla2 AS b
    ON (a.campo1 = b.campo1)

But I see that in the ON you are locating your filters.

What you must do is the following, understand the relationship between both tables to correctly arm the ON , and then use WHERE to define the filters. Staying something like this: 

SELECT*
FROM tabla1 AS a
INNER JOIN tabla2 AS b
    ON (a.campo1 = b.campo1)
WHERE a.campo1 = 'filtro1'
    AND a.campo2 BETWEEN 'filtro2' AND 'filtro3'

With this I hope to have guided you in the resolution of your problem.

NOTE: I invite you to review how the relationships INNER JOIN , LEFT JOIN , RIGHT JOIN

answered by 26.10.2018 в 18:07
4

As @Rostan says, the four conditions that your query requires must be included as part of a WHERE filter.

The query is very easy to put together. I'm going to put your conditions, and I'll be adding them one by one in the WHERE part:

  • first condition I want the id_gral to be equal to the one I send, y
  • second condition that is from a range of dates, y
  • third condition that the dst is equal to the one I send y
  • finally that from accountcode I get the last two digits and I want it to be equal to in.

We build the query: 

SELECT * 
FROM (falta indicar la forma en que se relacionan las tablas)
WHERE 
    g.id_gral=4 AND                                             -- 1ª condición
    c.calldate BETWEEN '2018-02-01%' AND '2018-10-25%' AND      -- 2ª condición 
    c.dst = '175'  AND                                          -- 3ª condición 
    right(c.accountcode, 2) = 'in'                              -- 4ª condición

As you can see, the query remains in the air in the FROM part, since you have one condition to define or rather, you need to contextualize the data design: how the tables are related .

Let's imagine a context: I have a table general that is related to a table cuenta using the column id_gral .

With that context information, we can now complete the% share% of the query, and consequently the complete query: 

SELECT * 
FROM 
    general g INNER JOIN cuenta c ON g.id_gral=c.id_gral        -- información que faltaba
WHERE 
    g.id_gral=4 AND                                             -- 1ª condición
    c.calldate BETWEEN '2018-02-01%' AND '2018-10-25%' AND      -- 2ª condición 
    c.dst = '175'  AND                                          -- 3ª condición 
    right(c.accountcode, 2) = 'in'                              -- 4ª condición

By the way, I had never seen the use of FROM in the conditions of % . Is that good for something?

    
answered by 26.10.2018 в 21:08
Can you help me find the error? repetitive cycle for C ++ JQuery / Javascript - Sort Multidimensional Array ()