CODEWITHSUNDEEP
phpjoinmysqli
Chris78
Chris78

Reputation: 387

Proper way to join these tables?

I am attempting to learn the difference in JOIN and LEFT JOIN. I watched several youtube videos and researched Google. However, I can't quite grasp why my JOIN query is not working. If I run the sql I get Column 'user_id' in field list is ambiguous.

sales table

sales_id   user_id   amount    date         status
3          1         1258.32   2013-07-02   S

users table

user_id   fname   lname
1         John    Doe

My Code:

$top = mysqli_query($mysqli, "SELECT user_id, fname, lname, SUM(amount) as total 
FROM sales
LEFT JOIN users ON
user_id.sales = user_id.users
WHERE status = 'S' AND MONTH(date) = MONTH(CURDATE()) AND YEAR(date) = YEAR(CURDATE()) GROUP BY user_id ORDER BY total DESC LIMIT 5");

     while($row = mysqli_fetch_assoc($top)) {
           $topamount[]     = $row['total'];
           $topfnameuser[]  = $row['fname'];
           $toplnameuser[]  = $row['lname'];
     }

Upvotes: 0

Views: 83

Answers (5)

Angripa
Angripa

Reputation: 157

I think you have miss understanding with the using of LEFT JOIN, Try this.. I have create table like you want, and I have error if i use your query and solve it so the query like this

SELECT fname,lname, SUM(amount) AS total
  FROM users LEFT JOIN sales
  ON users.user_id = sales.user_id
    WHERE sales.status = 'S' AND MONTH(sales.date) = MONTH(CURDATE()) AND YEAR(sales.date) = YEAR(CURDATE()) GROUP BY users.user_id
ORDER BY total DESC LIMIT 5

Upvotes: 2

Jacob Wan
Jacob Wan

Reputation: 2719

Focusing on the "attempting to learn the difference in JOIN and LEFT JOIN" part, outer joins are useful when you want to see all rows from one table, even those that have no corresponding rows in another related table.

Here's a simple example using students and test grades.

create table students (student_id serial primary key, student_name text);
create table tests (test_id serial primary key, test_name text, test_date date);
create table grades (
    student_id int,
    test_id int,
    grade char,
    primary key (student_id, test_id),
    foreign key (student_id) references students (student_id),
    foreign key (test_id) references tests (test_id));

insert into students (student_name) values ('joe');
insert into students (student_name) values ('amber');
insert into students (student_name) values ('steve');

insert into tests (test_name, test_date) values ('test 1', '2013-01-20');
insert into tests (test_name, test_date) values ('test 2', '2013-02-10');

insert into grades (student_id, test_id, grade) values (1, 1, 'A');
insert into grades (student_id, test_id, grade) values (1, 2, 'B');
insert into grades (student_id, test_id, grade) values (2, 1, 'B');
insert into grades (student_id, test_id, grade) values (2, 2, 'A');

The following query would return a list of students and their grades, including one row identifying students without any grades yet (in this case, Steve):

select s.student_name, t.test_name, t.test_date, g.grade
from students as s
left join grades as g on s.student_id = g.student_id
left join tests as t on g.test_id = t.test_id;

student_name | test_name | test_date  | grade 
--------------+-----------+------------+-------
joe          | test 1    | 2013-01-20 | A
joe          | test 2    | 2013-02-10 | B
amber        | test 1    | 2013-01-20 | B
amber        | test 1    | 2013-01-20 | A
steve        |           |            | 
(5 rows)

One really useful way to use outer joins is when you want to see only rows without matching rows in the related table. You can also do this with a subquery, but using an outer join isn't a bad option:

select s.student_name                                   
from students as s
left join grades as g on s.student_id = g.student_id
where g.student_id is null;
student_name 
--------------
steve
(1 row)

...is functionally equivalent to:

select student_name from students where student_id not in (select student_id from grades); 
student_name 
--------------
steve
(1 row)

Upvotes: 1

Matías Cánepa
Matías Cánepa

Reputation: 5974

You use JOIN when a given id is in both tables that you are joining.

You use LEFT JOIN when a given id is in one table and maybe in the second one

In your case you should select from users and left join sales

SELECT u.user_id, u.fname, u.lname, SUM(s.amount) as total 
FROM users u LEFT JOIN sales s ON s.user_id = u.user_id
WHERE s.status = 'S' AND MONTH(s.date) = MONTH(CURDATE()) AND YEAR(s.date) = YEAR(CURDATE())
GROUP BY u.user_id
ORDER BY total DESC LIMIT 5

Note the alias in table naming (users u). Then with that alias you can specify which column from which table to select.

Also I will recommend not to name a column "date" because it's a reserved word by MySQL. See: http://dev.mysql.com/doc/refman/5.6/en/reserved-words.html

Upvotes: 0

DevZer0
DevZer0

Reputation: 13535

Introduce a table alias so you can refer to it when referencing fields. Your issue is you have user_id in both tables and you have not defined which table to read the user_id from.

"SELECT s.user_id, u.fname, u.lname, SUM(s.amount) as total 
FROM sales AS s
LEFT JOIN users AS u ON
s.user_id = u.user_id

Upvotes: 0

Barmar
Barmar

Reputation: 781350

Since both sales and users have a column named user_id, you have to be specific in your SELECT list about which you want:

SELECT sales.user_id as user_id, fname, lname, SUM(amount) as total 
FROM sales
LEFT JOIN users
ON sales.user_id = users.user_id
WHERE status = 'S' AND MONTH(date) = MONTH(CURDATE()) AND YEAR(date) = YEAR(CURDATE()) 
GROUP BY user_id
ORDER BY total DESC
LIMIT 5

You also had the table and column names backwards in the ON clause. It's table.column, not column.table

Upvotes: 2

Related Questions