CODEWITHSUNDEEP
mysqlsql
greenbandit
greenbandit

Reputation: 2275

mysql join 3 tables by id

I have 3 tables to join and need some help to make it work, this is my schema:

donations:

+--------------------+------------+   
|   uid   |  amount  |   date     |
+---------+----------+------------+
|    1    |    20    | 2013-10-10 | 
|    2    |    5     | 2013-10-03 | 
|    2    |    50    | 2013-09-25 |
|    2    |    5     | 2013-10-01 |
+---------+----------+------------+

users:

+----+------------+
| id |  username  | 
+----+------------+
| 1  |    rob     |
| 2  |    mike    | 
+----+------------+

causes:

+--------------------+------------+
|   id    |   uid    |   cause    | <missing cid (cause id)
+---------+----------+------------+
|    1    |    1     |  stop war  | 
|    2    |    2     |   love     | 
|    3    |    2     |   hate     | 
|    4    |    2     |   love     | 
+---------+----------+------------+

Result I want (data cropped for reading purposes)

+---------+-------------+---------+-------------+
|    id   |   username  | amount  |    cause    | 
+---------+-------------+---------+-------------+
|    1    |     rob     |   20    |  stop war   |
|    2    |     mike    |   5     |    love     |
+---------+-------------+-----------------------+

etc...

This is my current query, but returns double data:

SELECT i.*, t.cause as tag_name
FROM users i
INNER JOIN donations tti ON (tti.uid = i.id)
INNER JOIN causes t ON (t.uid = tti.uid)

EDIT: fixed sql schema on fiddle http://sqlfiddle.com/#!2/0e06c/1 schema and data

How I can do this?

Upvotes: 2

Views: 2895

Answers (3)

Esteban Elverdin
Esteban Elverdin

Reputation: 3582

It seems your table's model is not right. There should be a relation between the Causes and Donations.

If not when you do your joins you will get duplicated rows.

For instance. Your model could look like this:

Donations

+--------------------+------------+
|   uid   |  amount  |   date     |  causeId
+---------+----------+------------+
|    1    |    20    | 2013-10-10 |     1
|    2    |    5     | 2013-10-03 |     2
|    2    |    50    | 2013-09-25 |     3
|    2    |    5     | 2013-10-01 |     2
+---------+----------+------------+

causes:

+----------------------+
|   id    |   cause    |
+---------+------------+
|    1    |   stop war | 
|    2    |   love     | 
|    3    |   hate     | 
+---------+------------+

And the right query then should be this

SELECT i.*, t.cause as tag_name
FROM users i
INNER JOIN donations tti ON (tti.uid = i.id)
INNER JOIN causes t ON (t.id = tti.causeId)

Upvotes: 1

steoleary
steoleary

Reputation: 9278

You need to match the id from both the users and causes table at the same time, like so:

SELECT i.*, t.cause as tag_name
FROM users i
INNER JOIN donations tti ON (tti.uid = i.id)
INNER JOIN causes t ON (t.uid = tti.uid and t.id = i.id)

Apologies for formatting, I'm typing this on a phone.

Upvotes: 0

M Khalid Junaid
M Khalid Junaid

Reputation: 64466

Try this 

SELECT CONCAT(i.username ,' ',i.first_name) `name`, 
SUM(tti.amount), 
t.cause AS tag_name
FROM users i
LEFT JOIN donations tti ON (tti.uid = i.id)
INNER JOIN causes t ON (t.uid = tti.uid)
GROUP BY i.id

Fiddle

Upvotes: 0

Related Questions