inner join... how to get all fields from one table ?

Question

Hi,

I want to get all fields from one table and use DISTINCT with the second table.

I have this:

SELECT stats.*, 
DISTINCT(visit_log.blog_id) AS bid 
FROM stats 
INNER JOIN visit_log ON stats.blog_id = visit_log.blog_id

But I get this error:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'DISTINCT(visit_log.blog_id) AS bid FROM stats INNER JOIN visit_log ON stats.blog' at line 1

Any idea ?

Thanks!

Answer 1

SELECT stats.*, dr.blog_id
FROM stats
INNER JOIN (SELECT DISTINCT(visit_log.blog_id) AS bid FROM visit_log) AS dr 
      ON stats.blog_id = dr.blog_id

Answer 2

Instead of joining against visit_log, you can construct a derived table containing only the distinct blog_id values.

select stats.*, v.blog_id 
from stats 
inner join ( select distinct blog_id from visit_log where stats.blog_id = visit_log.blog_id ) as v

Answer 3

You are only selecting blog_id from visit_log which is the column you are joining on. So your query is much like:

select * 
from stats s 
where 
exists (select null from visit_log v where s.blog_id = v.blog_id)