CODEWITHSUNDEEP
mysqlsqljoin
stack
stack

Reputation: 10218

How to use join but just get the rows of first table?

I have these two tables:

// one
+----+-------+
| id | value |
+----+-------+
| 1  | aaa   |
| 2  | bbb   |
| 3  | ccc   |
+----+-------+

// two
+----+-------+
| id | value |
+----+-------+
| 2  | ddd   |
| 3  | eee   |
| 4  | fff   |
+----+-------+

And here is my query:

SELECT one.value FROM one JOIN two ON one.id = two.id

And here is current result:

// 4 rows
+-----+
| bbb |
|     |
| ccc |
|     |
+-----+

And here is expected output:

// 2 rows
+-----+
| bbb |
| ccc |
+-----+

As you see I'm trying to only select the rows from one table. How can I do that?

Upvotes: 0

Views: 33

Answers (2)

Shyam Bhimani
Shyam Bhimani

Reputation: 1270

This will work for you 

SELECT table1.value FROM table1, table2 WHERE table1.id = table2.id

This basically Equi join

you can refer more about Equi Join here here

Upvotes: 1

Bulat
Bulat

Reputation: 6969

If you try this in your sql fiddle:

SELECT post_id, COUNT(*) from events
GROUP BY post_id
ORDER BY COUNT(*) DESC

You can notice that each qanda has several associated events, hence you are getting all events that have matching post_id.

If you are expecting two rows, add DISTINCT:

SELECT DISTINCT q.id, ee.table_code, q.subject 
FROM 
  qanda q JOIN 
  events ee on ee.post_id = q.id and 
         ee.author_id = 31 and 
         ee.table_code = 15

Upvotes: 1

Related Questions