Having troubles with select from where command in mysql

Question

Hi everyone abit stuck as new to all this database stuff, playing around to try and get a test database working for a college project.

I have tables called customers and caravans, and another called customers_caravans which ties the two together.

Then i have a final table called previous works. This details the previous works for a customer_caravanID.

However can't seem to work out the where statement to only pull data for the previous work a particular customer.

It just seems to loop through the whole customer list????

For someone this will be an easy fix, with either my select command being wrong or prehaps just the whole design of my database.

Anyways heres the database...

describe customers;
+----------------+--------------+------+-----+---------+----------------+
| Field          | Type         | Null | Key | Default | Extra          |
+----------------+--------------+------+-----+---------+----------------+
| customerID     | int(11)      | NO   | PRI | NULL    | auto_increment |
| name           | varchar(256) | NO   |     | NULL    |                |
| street_address | varchar(256) | YES  |     | NULL    |                |
| postcode       | varchar(256) | YES  |     | NULL    |                |
| telephone      | varchar(256) | YES  |     | NULL    |                |
+----------------+--------------+------+-----+---------+----------------+


describe caravans;
+-------------+--------------+------+-----+---------+----------------+
| Field       | Type         | Null | Key | Default | Extra          |
+-------------+--------------+------+-----+---------+----------------+
| caravanID   | int(11)      | NO   | PRI | NULL    | auto_increment |
| make        | varchar(256) | NO   |     | NULL    |                |
| model       | varchar(256) | NO   |     | NULL    |                |
| vin_number  | varchar(256) | YES  |     | NULL    |                |
| axle_number | tinyint(4)   | YES  |     | NULL    |                |
+-------------+--------------+------+-----+---------+----------------+

describe customers_caravans;
+----------------------+---------+------+-----+---------+----------------+
| Field                | Type    | Null | Key | Default | Extra          |
+----------------------+---------+------+-----+---------+----------------+
| customers_caravansID | int(11) | NO   | PRI | NULL    | auto_increment |
| customerID           | int(11) | NO   | MUL | NULL    |                |
| caravanID            | int(11) | NO   | MUL | NULL    |                |
+----------------------+---------+------+-----+---------+----------------+

describe previous_works;
+----------------------+---------------+------+-----+---------+-------+
| Field                | Type          | Null | Key | Default | Extra |
+----------------------+---------------+------+-----+---------+-------+
| customers_caravansID | int(11)       | NO   | MUL | NULL    |       |
| work                 | varchar(256)  | YES  |     | NULL    |       |
| partID               | int(11)       | YES  | MUL | NULL    |       |
| date                 | date          | YES  |     | NULL    |       |
| cost                 | decimal(13,2) | YES  |     | NULL    |       |
+----------------------+---------------+------+-----+---------+-------+

with the following data in...

INSERT INTO customers (name,street_address,postcode,telephone) VALUES 
('Megan Rose Hoobar', '41-43 Barrows Street', 'IP14 6HJ', '01449 77777'),
 ('Tim 
Smith', '17 Wynton Rise', 'JF77 2AB', '01564 673742');

INSERT INTO caravans (make,model,vin_number,axle_number) VALUES 
('Elddis','Buccaneer Commodore','1HGBH41JXMN1091866','2'), 
('Lunar','Venus 590/6','1HGBH41JXMN1091111','1');

INSERT INTO customers_caravans (customerID,caravanID) VALUES ('1','1'), ('2','2');

INSERT INTO previous_works (customers_caravansID,work,partID,date,cost) 
VALUES 
('1','Service','3','2015-04-15','150'),
('1','Service','3','2016-04-15','150'), 
('1','Service','5','2017-04-15','180'),
('2','Solar Panel Fitting','1','2018-02-18','380');

IE this select where i only want to get the data for the customer whos customers_caravansID matches 1, ie in this case only the services i get both customers repeated...

select x.name
     , y.make
     , y.model
     , p.work 
  from caravans y
     , customers x
     , customers_caravans xy
     ,previous_works p
 where p.customers_caravansID = "1";
+----------------------+--------+---------------------+---------+
| name                 | make   | model               | work    |
+----------------------+--------+---------------------+---------+
| Megan Rose Hoobar  | Elddis | Buccaneer Commodore | Service |
| Megan Rose Hoobar  | Elddis | Buccaneer Commodore | Service |
| Megan Rose Hoobar  | Elddis | Buccaneer Commodore | Service |
| Megan Rose Hoobar  | Elddis | Buccaneer Commodore | Service |
| Megan Rose Hoobar  | Elddis | Buccaneer Commodore | Service |
| Megan Rose Hoobar  | Elddis | Buccaneer Commodore | Service |
| Megan Rose Hoobar  | Lunar  | Venus 590/6         | Service |
| Megan Rose Hoobar  | Lunar  | Venus 590/6         | Service |
| Megan Rose Hoobar  | Lunar  | Venus 590/6         | Service |
| Megan Rose Hoobar  | Lunar  | Venus 590/6         | Service |
| Megan Rose Hoobar  | Lunar  | Venus 590/6         | Service |
| Megan Rose Hoobar  | Lunar  | Venus 590/6         | Service |
| Tim Smith          | Elddis | Buccaneer Commodore | Service |
| Tim Smith          | Elddis | Buccaneer Commodore | Service |
| Tim Smith          | Elddis | Buccaneer Commodore | Service |
| Tim Smith          | Elddis | Buccaneer Commodore | Service |
| Tim Smith          | Elddis | Buccaneer Commodore | Service |
| Tim Smith          | Elddis | Buccaneer Commodore | Service |
| Tim Smith          | Lunar  | Venus 590/6         | Service |
| Tim Smith          | Lunar  | Venus 590/6         | Service |
| Tim Smith          | Lunar  | Venus 590/6         | Service |
| Tim Smith          | Lunar  | Venus 590/6         | Service |
| Tim Smith          | Lunar  | Venus 590/6         | Service |
| Tim Smith          | Lunar  | Venus 590/6         | Service |
+----------------------+--------+---------------------+---------+

Hi. Welcome to StackOverflow. Next time please use sqlfiddle.com to write table structures and queries. Coming to your question, I think you need to study regarding 'JOINS' and 'GROUP BY' features of MySQL. — Praveen E, Oct 18 '18 at 19:31
In your select, you should use joins instead of listing the tables in the from clause. Also, you aren't specifying how the tables are related. — Sloan Thrasher, Oct 18 '18 at 21:25

score 0 · Answer 1 · answered Oct 18 '18 at 21:31

0

Here's your query using the more current form of joining multiple tables. Notice how it relates each table.

SELECT
    b.name,
    a.make,
    a.model,
    d.work
FROM caravans a
JOIN customers_caravans c
    ON a.caravamID = c.caravanID
JOIN customers b
    ON a.customerID = b.customerID
JOIN previous_works d
    ON c.customers_caravansID = d.customers_caravansID
WHERE d.customers_caravansID = "1";

answered Oct 18 '18 at 21:31

Sloan Thrasher

4,953
3
22
40

'More current', as in 'post-1992' - except of course that these are inner JOINs, instead of cross JOINs – Strawberry Oct 18 '18 at 22:31
You are right @Strawberrym but this us the OP's first post, so I'm trying to be nice. – Sloan Thrasher Oct 19 '18 at 02:21

Having troubles with select from where command in mysql

1 Answers1