Getting all fields from one table with INNER JOIN?

Question

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

I have it:

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 the SQL syntax; check the manual that matches your MySQL server version for the correct syntax to use next to DISTINCT (visit_log.blog_id) AS bid FROM stats INNER JOIN visit_log ON stats.blog 'on line 1

Any idea?

Answer 1

Instead of merging with visit_log, you can build a view containing only individual 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 2

 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 3

You only select blog_id from visit_log, which is the column you are joining. Therefore, your request is very similar:

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

Answer 4

 select * from visit_log v where v.blog_id in/= (select s.blog_id from stats s)