3

I have two tables, one a record of hits, the other a user table. If as user is logged in, the hit is recorded with that user's ID. If the user is not logged in, the hit is logged as user_id = 0

When I run a report, I want to JOIN the hits against the users table to get the username-- but there's no user with the ID 0, so I get bad data back (eg. no results for all the non-logged in hits). Is there a way to select a dummy username like "Guest" where the JOIN condition isn't met?

Here's the query:

SELECT DATE_FORMAT(a.timestamp, '%Y-%m-%d') date, count(a.*) hits, a.user_id, b.username
FROM hits a
JOIN users b ON a.user_id = b.id
WHERE 1
julio
  • 6,630
  • 15
  • 60
  • 82

2 Answers2

1

Try left join and COALESCE to default

SELECT DATE_FORMAT(a.timestamp, '%Y-%m-%d') date, count(a.*) hits, a.user_id,
  COALESCE( b.username, 'GUEST')
FROM hits a
LEFT JOIN USERS b ON a.user_id = b.id
Conrad Frix
  • 51,984
  • 12
  • 96
  • 155
  • @julio. No problem you can also use [IFNULL](http://dev.mysql.com/doc/refman/5.0/en/control-flow-functions.html#function_ifnull) instead – Conrad Frix Jun 15 '11 at 18:23
0

You need to do an LEFT OUTER JOIN; that will return you the records that have a user_id entry in the hits table where the user does not exist in the users table.

Paul Sonier
  • 38,903
  • 3
  • 77
  • 117