CODEWITHSUNDEEP
phpmysqlsql
Dmitry
Dmitry

Reputation: 1105

Multiple selection of three tables

I have three linked tables - order_tab which contains orders, autosorder_tab which connects two other and auto_tab which contains all cars in shop.
One order can contain any number of cars.

CREATE TABLE order_tab
(`id` int, `_phone` varchar(10), `_email` varchar(9), `_date` varchar(10))
;

INSERT INTO order_tab
(`id`, `_phone`, `_email`, `_date`)
VALUES
(1, '111-111111', '[email protected]', '2013-08-19')
;

CREATE TABLE auto_tab
(`id` int, `us_id` int, `str_1` varchar(3), `str_2` varchar(8))
;

INSERT INTO auto_tab
(`id`, `us_id`, `str_1`, `str_2`)
VALUES
(2, 0, 'BMW', '530i E60'),
(6, 0, 'BMW', '530i')
;

CREATE TABLE autosorder_tab
(`id` int, `au_id` int, `or_id` int, `hours` int, `price` decimal(19,2))
;

INSERT INTO autosorder_tab
(`id`, `au_id`, `or_id`, `hours`, `price`)
VALUES
(1, 2, 1, 3, 2700),
(2, 6, 1, 2, 3500)

order_tab id - is main. or_id from autosorder_tab is id from order_tab. au_id is id from auto_tab
How can I select all orders to all cars in each order?

Upvotes: 1

Views: 85

Answers (2)

peterm
peterm

Reputation: 92805

If I understand correctly just use JOIN

SELECT o.*, a.*
  FROM autosorder_tab ao JOIN order_tab o
    ON ao.or_id = o.id JOIN auto_tab a
    ON ao.au_id = a.id
 ORDER BY o.id, a.id

Here is SQLFiddle demo

UPDATE If you want to group your information per order use GROUP BY and appropriate aggregate functions. For names of cars you want to use GROUP_CONCAT()

SELECT o.id, o._date, 
       GROUP_CONCAT(CONCAT(a.str_1, ' ', a.str_2) 
          ORDER BY a.str_1, a.str_2
          SEPARATOR ',') autos,
       SUM(hours) hours,
       ...
  FROM autosorder_tab ao JOIN order_tab o
    ON ao.or_id = o.id JOIN auto_tab a
    ON ao.au_id = a.id
 GROUP BY o.id

To change a delimiter use SEPARATOR clause. Or use comma by default and then when iterating over the result set in your client code change it according to the presentation rules.

Here is SQLFiddle demo

Upvotes: 3

Bora
Bora

Reputation: 10717

Should be like below. Please try..

SELECTED order_tab all rows.

Then JOINED autosorder_tab, auto_tab and ORDERED by order id descending.

    SELECT * FROM order_tab 
INNER JOIN autosorder_tab ON order_tab.id = autosorder_tab.or_id 
INNER JOIN auto_tab ON auto_tab.id = autosorder_tab.au_id 
  ORDER BY order_tab.id DESC

Upvotes: 1

Related Questions