Connecting 3 tables to get data

Question

I have 3 tables - User table, book1 table, book2 table.

User table is like this -

user_id | gender | l_name | f_name
-------- -------- -------- -------
 1        male     Doe      Jon
 2        female   Xu       Jini
 3        female   Din      Jane

book1 table -

b_id | user_id | amount | date
----- --------- -------- ----------
 1      3        98.30    2014-05-14
 2      1        65.70    2014-05-07
 3      2        14.40    2014-05-06
 4      2        55.60    2014-05-07

book2 table -

b_id | user_id | amount | date
----- --------- -------- ----------
 1      2        38.20    2014-04-06
 2      3        84.40    2014-04-02
 3      3        31.30    2014-04-12
 4      1        74.40    2014-05-06

The user gives a date range as input and I want to calculate the sales count(COUNT), total amount(SUM) and the max date(MAX) for that date range. After this I want to connect this data to the user table and get the gender and name using the user_id.

I wrote this query to get the data for the given date range from book1 and book2 tables-

SELECT * FROM book1
WHERE date between '2014-04-02' and '2014-05-15'
UNION ALL
SELECT * FROM book2
WHERE date between '2014-04-02' and '2014-05-15'
ORDER BY customer_id;

By this i get all the rows in the book1 and book2 table which satisfy the date range. Now should i use subquery or something else to reach the goal. I think sql should take care till getting the count, sum and max from book tables. Then the connection to the user table should be done in PHP. Am i on the right path? Can everything be done in SQL? I am kinda lost.

no, you can do it all at the sql level without ever involving PHP. you just need to learn about [sql join](http://en.wikipedia.org/wiki/Join_%28SQL%29)s — Marc B, May 21 '14 at 17:04

score 3 · Accepted Answer · answered May 21 '14 at 17:16

Yes, you can do it in SQL using a plain JOIN.

This will basically get all users and join them up with their respective amounts in the period. After that, the results are grouped by user so that we can sum up the amounts.

SELECT u.user_id, u.l_name, u.f_name, SUM(x.amount) `total amount`
FROM user u
JOIN (
  SELECT user_id, date, amount FROM book1
  UNION ALL
  SELECT user_id, date, amount FROM book2
) x
  ON u.user_id = x.user_id
 AND x.date between '2014-04-02' and '2014-05-15'
GROUP BY u.l_name, u.f_name,u.user_id

An SQLfiddle to test with.

As a side note, learning about joins is really a necessity to work efficiently with SQL databases.

Connecting 3 tables to get data

1 Answers1