CODEWITHSUNDEEP
phpmysql
user3998446
user3998446

Reputation: 27

mysql join not showing all rows

I'm having some problems getting all the results with this query. Can you help me clear this out?

I have this query:

if (!$SqlCount = $db->sql_query("SELECT count(*) as count FROM
  actividades
  LEFT JOIN tarifas
    ON actividades.id = tarifas.id_actividade
WHERE
  $query
  AND actividades.dias LIKE '%$diaExtenso%'
  AND (tarifas.id_actividade IS NULL
    OR (tarifas.dia = '$dia' AND tarifas.fechado = 0 AND tarifas.id_actividade = actividades.id))
")) {
    message_die("DS", TRUE);
}

I want to show everything from the first table if: there's no match at all in the second table OR if there's a match for the day ($dia) and fechado = 0

If I have a rule for a specific day, it just doesn't show anything, despite the day choosen. What am I doing wrong?

Thanks a lot!

EDIT:

Thanks for the help guys, I guess the question was not put the best way, here's what I think it's the problem:

The query is checking if tarifas.id_actividade is not null, and if there's a rule for a day, let's say tomorrow, indeed tarifas.id_actividade will not be null and it will go to the OR part, where he checks if there's a rule for that day.

So, there's not a rule for today but if there's one for tomorrow, tarifas.id_actividade will not be null and there will not be a rule for today .. so it doesn't show anything, and it should.

Does it make any sense? Should I post the mysql tables and the "$query" part?

Again, thanks for helping me with this.

Upvotes: 2

Views: 94

Answers (4)

user3998446
user3998446

Reputation: 27

Ok guys, here's what resolved the problem:

if (!$SqlCount = $db->sql_query("SELECT count(*) as count FROM
  actividades
  LEFT JOIN tarifas
    ON actividades.id = tarifas.id_actividade AND tarifas.dia = '$dia'
WHERE
  $query
  AND actividades.dias LIKE '%$diaExtenso%'
  AND (tarifas.id_actividade IS NULL
    OR (tarifas.dia = '$dia' AND tarifas.fechado = 0 AND tarifas.id_actividade = actividades.id))
")) {
    message_die("DS", TRUE);
}

On the ON clause, I added tarifas.dia = '$dia'

Thanks for your help.

Upvotes: 0

user2941651
user2941651

Reputation:

Please try:

if (!$SqlCount = $db->sql_query("SELECT count(A.*) as count FROM
FROM
(
    SELECT * 
    FROM
      actividades
      LEFT JOIN tarifas
        ON actividades.id = tarifas.id_actividade
    WHERE
      $query
      AND actividades.dias LIKE '%$diaExtenso%'
      AND tarifas.id_actividade IS NULL
    UNION ALL
    SELECT *  FROM
      actividades
      INNER JOIN tarifas
        ON actividades.id = tarifas.id_actividade
    WHERE
      $query
      AND actividades.dias LIKE '%$diaExtenso%'
      AND (tarifas.dia = '$dia' AND tarifas.fechado = 0 AND tarifas.id_actividade = actividades.id))
) AS A")) {
    message_die("DS", TRUE);
}

Please tell what is the $query element in WHERE clause?

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269873

I think that is what your query should be doing what you want, although the last condition on the id is unnecessary:

SELECT count(*) as count
FROM actividades a LEFT JOIN
     tarifas t
     ON a.id = t.id_actividade 
WHERE $query AND
      a.dias LIKE '%$diaExtenso%' AND
      (t.id_actividade IS NULL OR (t.dia = '$dia' AND t.fechado = 0))

And alternative formulation would be:

select a.cnt + b.cnt
from (select count(*) as cnt
      from actividades a
      where not exists (select 1 from tarifas t where a.id = t.id_actividade)
     ) a join
     (select count(*) as cnt
      from actividades a left join
           tarifas t
      where a.id = t.id_actividade and t.dia = '$dia' AND t.fechado = 0
     ) b;

My suspicion is that the t.dia = '$dia' piece may not be working as you expect. You should, in fact, be using parameterized queries for this.

Upvotes: 1

Strawberry
Strawberry

Reputation: 33945

Note that this query remains (potentially) slow because it can use no index on a.dias. Also it will only show a count, because that's all that was asked for.

SELECT COUNT(t.id_actividade) count 
  FROM actividades a
  LEFT 
  JOIN tarifas t
    ON t.id_actividade = a.id 
   AND t.dia = '$dia' 
   AND t.fechado = 0
 WHERE a.dias LIKE '%$diaExtenso%';

Upvotes: 1

Related Questions