merging results from two tables

[javaQQ]

I would like to ask for some help fixing this query:
SELECT u.user_id, u.first_name, u.last_name, SUM( p.job_price ) AS 'used'
FROM TestUsers AS u, TestPriceline AS p GROUP BY user_id;

I want to merge columns from two tables to come up with the following result:
+---------+------------+-----------+------+
| user_id | first_name | last_name | used |
+---------+------------+-----------+------+
| abc-A | Albert | Allen | 1.00 |
| abc-B | Bill | Bell | 2.00 |
| abc-C | Carla | Carey | 2.00 |
| abc-D | Dana | Dawkins | 2.00 |
+---------+------------+-----------+------+
4 rows in set (0.01 sec)

My query gives me this reult:
mysql> SELECT u.user_id, u.first_name, u.last_name, SUM( p.job_price ) AS 'used'
-> FROM TestUsers AS u, TestPriceline AS p GROUP BY user_id;
+---------+------------+-----------+------+
| user_id | first_name | last_name | used |
+---------+------------+-----------+------+
| abc-A | Albert | Allen | 7.00 |
| abc-B | Bill | Bell | 7.00 |
| abc-C | Carla | Carey | 7.00 |
| abc-D | Dana | Dawkins | 7.00 |
+---------+------------+-----------+------+
4 rows in set (0.01 sec)
Cose, but it gives me the sum of the entire "job_price" column as the sum for each user


I start with two tables:
mysql> SELECT * FROM TestUsers;
+------------+---------+------------+-----------+
| account_id | user_id | first_name | last_name |
+------------+---------+------------+-----------+
| abc | abc-A | Albert | Allen |
| abc | abc-B | Bill | Bell |
| abc | abc-C | Carla | Carey |
| abc | abc-D | Dana | Dawkins |
+------------+---------+------------+-----------+
4 rows in set (0.00 sec)

mysql> SELECT * FROM TestPriceline;
+------------+---------+--------+-----------+
| account_id | user_id | job_id | job_price |
+------------+---------+--------+-----------+
| abc | abc-A | abc#1 | 1.00 |
| abc | abc-B | abc#2 | 1.00 |
| abc | abc-B | abc#3 | 1.00 |
| abc | abc-C | abc#4 | 1.00 |
| abc | abc-C | abc#5 | 1.00 |
| abc | abc-D | abc#6 | 1.00 |
| abc | abc-D | abc#7 | 1.00 |
+------------+---------+--------+-----------+
7 rows in set (0.00 sec)

The following queries work properly:

SELECT user_id, first_name, last_name FROM TestUsers GROUP BY user_id;
+---------+------------+-----------+
| user_id | first_name | last_name |
+---------+------------+-----------+
| abc-A | Albert | Allen |
| abc-B | Bill | Bell |
| abc-C | Carla | Carey |
| abc-D | Dana | Dawkins |
+---------+------------+-----------+
4 rows in set (0.02 sec)

SELECT user_id, SUM( job_price ) AS 'used' FROM TestPriceline GROUP BY user_id;
+---------+------+
| user_id | used |
+---------+------+
| abc-A | 1.00 |
| abc-B | 2.00 |
| abc-C | 2.00 |
| abc-D | 2.00 |
+---------+------+
4 rows in set (0.03 sec)


The entire code is below:
/* ---------------------------------------------------- Code Begins Here: -----------------
DROP TABLE IF EXISTS TestUsers;
CREATE TABLE TestUsers (

account_id varchar(15),
user_id varchar(15),
first_name varchar(15),
last_name varchar(15)

)
;

INSERT INTO TestUsers ( account_id, user_id, first_name, last_name )
VALUES ( "abc", "abc-A", "Albert", "Allen" )
;

INSERT INTO TestUsers ( account_id, user_id, first_name, last_name )
VALUES ( "abc", "abc-B", "Bill", "Bell" )
;

INSERT INTO TestUsers ( account_id, user_id, first_name, last_name )
VALUES ( "abc", "abc-C", "Carla", "Carey" )
;

INSERT INTO TestUsers ( account_id, user_id, first_name, last_name )
VALUES ( "abc", "abc-D", "Dana", "Dawkins" )
;

DROP TABLE IF EXISTS TestPriceline;
CREATE TABLE TestPriceline (

account_id varchar(15),
user_id varchar(15),
job_id varchar(15),
job_price decimal(10,2)

)
;

INSERT INTO TestPriceline ( account_id, user_id, job_id, job_price )
VALUES ( "abc", "abc-A", "abc#1", 1.00 )
;

INSERT INTO TestPriceline ( account_id, user_id, job_id, job_price )
VALUES ( "abc", "abc-B", "abc#2", 1.00 )
;

INSERT INTO TestPriceline ( account_id, user_id, job_id, job_price )
VALUES ( "abc", "abc-B", "abc#3", 1.00 )
;

INSERT INTO TestPriceline ( account_id, user_id, job_id, job_price )
VALUES ( "abc", "abc-C", "abc#4", 1.00 )
;

INSERT INTO TestPriceline ( account_id, user_id, job_id, job_price )
VALUES ( "abc", "abc-C", "abc#5", 1.00 )
;

INSERT INTO TestPriceline ( account_id, user_id, job_id, job_price )
VALUES ( "abc", "abc-D", "abc#6", 1.00 )
;

INSERT INTO TestPriceline ( account_id, user_id, job_id, job_price )
VALUES ( "abc", "abc-D", "abc#7", 1.00 )
;

SELECT user_id, SUM( job_price ) AS 'used' FROM TestPriceline GROUP BY user_id;

SELECT user_id, first_name, last_name FROM TestUsers GROUP BY user_id;

SELECT u.user_id, u.first_name, u.last_name, SUM( p.job_price ) AS 'used'
FROM TestUsers AS u, TestPriceline AS p GROUP BY user_id;

[jalilmon]

try this

SELECT u.user_id, u.first_name, u.last_name, SUM( p.job_price ) AS 'used'
FROM TestUsers AS u inner join TestPriceline AS p on u.user_id = p.user_id GROUP BY user_id;

use inner join