CODEWITHSUNDEEP
mysqljoin
Jamie Turner
Jamie Turner

Reputation: 579

Count(*) rows on two tables where row id's don't match.

Having a headache of a time with this:

I need to count(*) the amount of rows in two tables between now and 1 day ago where historyId in table one != historyId in table two and userId=?. I need to then return the sum of all the rows of the two tables.

If historyId in table one does equal historyId in table two it needs to only be counted as one row, rather than two.

Query one

    SELECT HOUR(date) as hr, historyId, COUNT(*) as num_rows 
    FROM webHistory WHERE userId=? AND date BETWEEN (SYSDATE() - INTERVAL 1 DAY) 
    AND SYSDATE() GROUP BY HOUR(date);

Query two

    SELECT HOUR(date) as hr, historyId, COUNT(*) as num_rows 
    FROM locationHistory WHERE userId=? AND date BETWEEN (SYSDATE() - INTERVAL 1 DAY) 
    AND SYSDATE() GROUP BY HOUR(date);

I've been looking at JOIN but I am completely stuck and have no idea what to even search for next let alone a route to venture and test down.

Upvotes: 0

Views: 130

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269723

I think you want to use union for this:

select count(distinct historyid)
from (select hour(date) as hr, historyid
      from webhistory
      where userid = ? and date between sysdate() - interval 1 day and sysdate()
      union all
      select hour(date) as hr, historyid
      from locationhistory
      where userid = ? and date between sysdate() - interval 1 day and sysdate()
     ) t
group by hr;

Upvotes: 1

echo_Me
echo_Me

Reputation: 37233

you can use A UNION here:

  SELECT HOUR(date) as hr, historyId, COUNT(*) as num_rows 
    FROM webHistory WHERE userId=? AND date BETWEEN (SYSDATE() - INTERVAL 1 DAY) 
    AND SYSDATE() GROUP BY HOUR(date)
 UNION
  SELECT HOUR(date) as hr, historyId, COUNT(*) as num_rows 
   FROM locationHistory WHERE userId=? AND date BETWEEN (SYSDATE() - INTERVAL 1 DAY) 
   AND SYSDATE() GROUP BY HOUR(date);

Upvotes: 1

Related Questions