CODEWITHSUNDEEP
mysqlsqlselectmysql-workbench
Badja
Badja

Reputation: 875

Find all sales made by each customer

I am trying to list all the sales, which links with a customer ID, so I expect to see

sale ID    customerID    customerfirstname    customerlastname 
1          1             J                    Bloggs
2          2             G                    Green
3          1             J                    Bloggs

This is all I have no far and it's not quite right...

SELECT  sale.sale_id
        customer.customer_id ,
        customer.customer_first_name , 
        customer.customer_last_name , 
FROM mydb.customer , mydb.sale
WHERE sale.sale_id = customer_id

Here is my structure

CREATE TABLE `sale` (
  `sale_id` int(11) NOT NULL,
  `sale_items` int(3) NOT NULL,
  `sale_paid` tinyint(1) DEFAULT NULL,
  `customer_customer_id` int(11) NOT NULL,
  PRIMARY KEY (`sale_id`,`customer_customer_id`),
  KEY `fk_sale_customer1_idx` (`customer_customer_id`),
  CONSTRAINT `fk_sale_customer1` FOREIGN KEY (`customer_customer_id`) REFERENCES `customer` (`customer_id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


CREATE TABLE `customer` (
  `customer_id` int(11) NOT NULL,
  `customer_first_name` varchar(45) DEFAULT NULL,
  `customer_last_name` varchar(45) DEFAULT NULL,
  `customer_address1` varchar(45) DEFAULT NULL,
  `customer_address2` varchar(45) DEFAULT NULL,
  `customer_address3` varchar(45) DEFAULT NULL,
  `customer_post_code` varchar(45) DEFAULT NULL,
  `customer_debit` varchar(45) DEFAULT NULL,
  PRIMARY KEY (`customer_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Upvotes: 2

Views: 92

Answers (4)

Shadow
Shadow

Reputation: 34285

You should join customer id on customer id, not on sale id.

SELECT  sale.sale_id,
        customer.customer_id ,
        customer.customer_first_name , 
        customer.customer_last_name 
FROM mydb.customer INNER JOIN mydb.sale ON sale.customer_customer_id = customer.customer_id

Upvotes: 2

Anton
Anton

Reputation: 4052

For your structure it should be something like:

SELECT  s.sale_id
        s.customer_id ,
        c.customer_first_name , 
        c.customer_last_name , 
FROM 
  mydb.customer c
INNER JOIN
  mydb.sale s
ON 
  s.customer_id = c.customer_id

Upvotes: 1

Mureinik
Mureinik

Reputation: 311893

You are joining on the wrong field - both tables have a column referencing the customer id, which you should use. Additionally, you have a redundant comma after the last item in the select list and are missing one after the first item:

SELECT  sale.sale_id,
        customer.customer_id,
        customer.customer_first_name, 
        customer.customer_last_name
FROM    customer, sale
WHERE   sale.customer_customer_id = customer.customer_id

Note, however, that using implicit joins (i.e., having more than one table in the from clause) is deprecated and discouraged, and you should probably use explicit joins instead:

SELECT  sale.sale_id,
        customer.customer_id,
        customer.customer_first_name, 
        customer.customer_last_name
FROM    customer
JOIN    sale ON sale.customer_customer_id = customer.customer_id

Upvotes: 1

Nir Levy
Nir Levy

Reputation: 12953

you need to use JOIN to connect the tables. from looking at your tables, it looks like customer_customer_id is the column on sale table that matches the customer id:

SELECT  sale.sale_id
        customer.customer_id ,
        customer.customer_first_name , 
        customer.customer_last_name 
FROM customer INNER JOIN sale on 
sale.customer_customer_id = customer.customer_id

Upvotes: 2

Related Questions