How can I SELECT in this example only one row (value) when in the joined table one column have duplicated value in foreign key? For example:

CREATE TABLE customers(
id INT (10) NOT NULL PRIMARY KEY AUTO_INCREMENT,
name  VARCHAR (50) NOT NULL,
city  VARCHAR (50) NOT NULL
);

CREATE TABLE orders(
cus_id INT (10) NOT NULL,
order_date DATETIME NOT NULL
);

CREATE TABLE products(
id INT (5) NOT NULL PRIMARY KEY,
product_name  VARCHAR(50) NOT NULL,
product_price INT(10) NOT NULL
);

CREATE TABLE ordered_items(
id INT (10) NOT NULL PRIMARY KEY AUTO_INCREMENT,
cus_id INT (10) NOT NULL,
product_id  INT(5) NOT NULL
);

ALTER TABLE orders ADD CONSTRAINT customers_id_fr FOREIGN KEY ( cus_id ) REFERENCES customers ( id );
ALTER TABLE ordered_items ADD CONSTRAINT ordered_items_fr FOREIGN KEY ( cus_id ) REFERENCES customers ( id );

INSERT INTO customers (name, city) VALUES ("My Name", "New York");
INSERT INTO orders (cus_id, order_date) VALUES ("1", NOW());
INSERT INTO products (id, product_name, product_price) VALUES ("99", "My test product nr. 1", "20");
INSERT INTO products (id, product_name, product_price) VALUES ("98", "My test product nr. 2", "10");


INSERT INTO ordered_items (id, cus_id, product_id) VALUES ("1", "1", "99");
INSERT INTO ordered_items (id, cus_id, product_id) VALUES ("2", "1", "98");


SELECT name FROM customers INNER JOIN orders ON orders.cus_id = customers.id 
INNER JOIN ordered_items ON ordered_items.cus_id = orders.cus_id 
INNER JOIN products ON products.id = ordered_items.product_id;

In this example result will have two rows with identical value. Because in ordered_items table I have two values to the same customer id.

 ========= 
| name    |
 ========= 
| My Name |
 --------- 
| My Name |
 --------- 

Or just simply SELECT DISCINT will be the solution? Thank you!

Working example

CodePudding user response：

Demo

SELECT name -- Now... If a customer name could duplicate... that's a different problem in the data. 
FROM customers 
WHERE EXISTS (SELECT 1 
              FROM orders 
              INNER JOIN ordered_items ON ordered_items.cus_id = orders.cus_id 
              INNER JOIN products ON products.id = ordered_items.product_id
              WHERE orders.cus_id = customers.id)

WHY?

Simply put: avoid pulling in data/tables you don't need.

If all you're after is the name from customers table and need no values from the other tables us an EXISTS statement in your where clause; don't join. Joins are used when you need fields from those tables being joined. Exists in your where clause will early exit once an instance is found. it can use indexes and should be slightly faster as it doesn't have to query the table and join all fields/rows. If you need values from other tables, distinct is your answer. If you can't use distinct because you just want 1 row Lateral apply/Cross Apply and TOP 1 with order by should do it. I'm not sure why you even have other tables involved just customers and ordered_items is what you need right? or are there other fields you're not telling us about?

Now the second you add other fields they may cause the customer name to replicate depending on those values: so GROUP BY may be what you need. So... What is the "Business Question" you're trying to answer with this query?

If all you are really after is customers who have placed an order:

Demo

SELECT name 
FROM customers 
WHERE EXISTS (SELECT 1 
              FROM ordered_items ON ordered_items.cus_id = Customers.id 
              WHERE orders.cus_id = customers.id)

CodePudding user response：

As mentioned in comment above, use GROUP BY statement at customers.id

Take a look and test if over here if you want: https://www.w3schools.com/sql/sql_groupby.asp