Getting all fields from one table using INNER JOIN?

Question

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?

score 8 · Accepted Answer · answered Oct 02 '10 at 19:17

8

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

answered Oct 02 '10 at 19:17

Martin

9,674
5
36
36

Ive modified a bit your code and It works for my needs. thanks! – Klian Oct 03 '10 at 08:59

Mitch Wheat · Answer 2 · 2010-10-03T01:05:29.317

3

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

edited Oct 03 '10 at 01:05

answered Oct 02 '10 at 11:01

Mitch Wheat

295,962
43
465
541

Thanks for your reply, but I get: 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 '(SELECT DISTINCT(visit_log.blog_id) AS bid FROM visit_log) dr FROM stats INNER J' – Klian Oct 02 '10 at 14:26

score 0 · Answer 3 · edited Apr 22 '15 at 10:11

0

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

edited Apr 22 '15 at 10:11

Tensibai

15,557
1
37
57

answered Apr 22 '15 at 09:46

Amine EL HABIL

1

A small explanation of how it works/why it works and markdown dormatting would make this answer more valuable. See [this](http://stackoverflow.com/help/how-to-answer) for guidance on writing answers – Tensibai Apr 22 '15 at 10:11

score 0 · Answer 4 · answered Oct 02 '10 at 19:38

0

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)

answered Oct 02 '10 at 19:38

Lord Peter

3,433
2
33
33

Your query works, but only select the columns of "stats" table, I want to select the columns of visit_log too. Ive tried with: ....select * from visit_log v where s.blog_id = v.blog_id but it doesnt work – Klian Oct 03 '10 at 07:55

Getting all fields from one table using INNER JOIN?

4 Answers4