Help query MySql

Question

Help query MySql

Navigation

#1 by (0 votes)
#2 by (0 votes)
#3 by (0 votes)

0

I'm trying some consultations and I can not get one to work for me. I need to get the list of orders each of which with the name of the associated carrier . I only have 8 orders so I should have 8 results but it returns a lot more ...

SQL create tables:

CREATE TABLE carriers (
  id_carrier  int(10) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  name        varchar(64) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE orders (
  id_order    int(10) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  reference   varchar(9) DEFAULT NULL,
  id_carrier  int(10) UNSIGNED NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

ALTER TABLE orders
  ADD UNIQUE(reference),
  ADD FOREIGN KEY (id_carrier) REFERENCES carriers(id_carrier);

SQL inserts for tests:

INSERT INTO carriers ( id_carrier , name ) VALUES
(NULL, 'test'),
(NULL, 'seur'),
(NULL, 'asm'),
(NULL, 'xxx');

INSERT INTO orders ( id_order, reference, id_carrier ) VALUES
(NULL, 'XKBKNABJK', 1),
(NULL, 'OHSATSERP', 2),
(NULL, 'UOYEVOLIT', 2),
(NULL, 'FFATNOMMJ', 1),
(NULL, 'KHWLILZLL', 4),
(NULL, 'ZRIIQDSYN', 1),
(NULL, 'DGAJIPUWH', 3),
(NULL, 'RTJHJMRAC', 3);

I was trying different combinations with the following:

SELECT DISTINCT 
    id_order AS Pedido, 
    reference AS Referencia, 
    carriers.name AS Servicio 
        FROM orders, carriers 
        WHERE orders.id_carrier IN (SELECT id_carrier FROM carriers) 

    /*GROUP BY orders.id_order, Servicio*/
    ORDER BY orders.id_order ASC

mysql

asked by Orici 03.10.2017 в 13:51

source

3 answers

Javascript Manners Icons disappear when I change my view

score 0 · Answer 1

Nothing, I found a way to do it:

SELECT DISTINCT 
    id_order AS Pedido, 
    reference AS Referencia, 
    carriers.name AS Servicio 
        FROM orders, carriers 
        WHERE orders.id_carrier = carriers.id_carrier

    ORDER BY orders.id_order ASC;

Ref: link

score 0 · Answer 2

Good morning Try doing a left join instead of just showing the two tables:

SELECT id_order AS Pedido, reference AS Referencia, carriers.name AS Servicio 
    FROM orders left join carriers on orders.id_carrier = carriers.id_carrier
    WHERE orders.id_carrier IN (SELECT id_carrier FROM carriers) 

/*GROUP BY orders.id_order, Servicio*/
ORDER BY orders.id_order ASC

This way you can even remove the DISTINCT, since it will not take duplicate rows for orders.

score 0 · Answer 3

This query has a very basic and natural way to resolve

SELECT DISTINCT 
    o.id_order AS Pedido, 
    o.reference AS Referencia, 
    c.carriers.name AS Servicio 
    FROM orders o
    LEFT JOIN carriers c
       ON c.id_carrier = o.id_carrier 
    ORDER BY o.id_order ASC

We retrieve all the orders and we relate each one (1 to 1) with the only carrier that could have. If an order does not have a carrier the LEFT JOIN assures us that it will show it anyway.