Query Mysql returns no error or result

Asked

Viewed 707 times

2

I have five tables in which each one of them has data that I need to be displayed grouped by date. Proposals are grouped by date, number of proposals, value of proposals, media, amount invested, number of clicks, code (source_code) and whether it is denied or accepted. the result would be more or less this:

data_proposta | source_code | custo(total por data) | midia | cliques | impressoes | custo | negadas | aceitas

I have a table called proposal, another media (which brings clicks, value, impressions), Precog (validates or not) and source_code. Each proposal is saved in a new row of the proposed table and this query makes a COUNT() in the amount of proposals, separate by rejected proposals and accepted with a COUNT() tbm. Brings from the media table the amount of clicks, cost value grouped by date based on the source_code.

I’m trying to make a select in Mysql and phpMyAdmin simply does not return any error and also does not display the result.

Look at my query:

SELECT data_proposta, 
       propostas.source_code, 
       Sum(midias.custo)             AS custo, 
       Count(data_proposta)          AS propostas, 
       Sum(IF(precog_fk <> 9, 1, 0)) AS validas 
FROM   propostas 
       LEFT JOIN midias 
              ON `data_proposta` = `midias`.`data` 
GROUP  BY data_proposta 
LIMIT  0, 1
mysql join

  • It would also help to place the table structures (at least the field types that are used in the query).

  • Exchange the LEFT JOIN for OUTER JOIN

  • @Dorathoto I used Outer Join and nothing happened.

  • field types: data_proposta(datetime), source_code(varchar), cost(decimal(10,2)), precog_fk(int); That’s it.

2 answers

1

Try this below:

SELECT a.data_proposta, 
       a.source_code, 
       Sum(b.custo)             AS custo, 
       Count(a.data_proposta)          AS propostas, 
       Sum(IF(precog_fk <> 9, 1, 0)) AS validas 
FROM   propostas a, midias b
       a.data_proposta(+) = b.data
GROUP  BY a.data_proposta

  • This fit you? @Debarros

0

The problem may be in the format of the date field, as you did not pass the table structure, it is difficult to identify the problem, see if this would solve your case:

SELECT DATE_FORMAT(data_proposta, '%d/%m/%Y') as data_proposta, 
       propostas.source_code, 
       Sum(midias.custo) AS custo, 
       Count(DATE_FORMAT(data_proposta, '%Y-%m-%d')) AS propostas, 
       Sum(IF(precog_fk <> 9, 1, 0)) AS validas 
FROM   propostas 
       LEFT JOIN midias 
              ON data_proposta = midias.data 
               where custo > 0
GROUP  BY data_proposta, propostas.source_code

Browser other questions tagged mysql join

You are not signed in. Login or sign up in order to post.