I have three tables in my database, one for clients, one for products and one for orders, containing both the primary keys of products and clients as a foreign key. I am using SQL and MySQL

A query I'm struggling with is how to match two clients who bought the same product in 2014. They should also be displayed as c1.cname < c2.cname but that's not the important part.

I have tried the following:

select c1.cname as c1name, c2.cname as c2name, p.pname from customers as c1 left outer join customers as c2 on c1.cid!=c2.cid inner join orders as o on o.cid=c1.cid=c2.cid inner join products as p on p.pid=o.pid where year(o.odate)=2014;

Expected results using the following data would be :

c1name c2name pname

Jones   Smith   chocolate
Dupond  Gupta   sugar
Gupta   Jones   sugar
Jones   Smith   sugar
Dupond  Jones   milk
Jones   Smith   milk
Blake   Dupond  tea
Dupond  Gupta   tea
Gupta   Jones   tea

However, only one customer name is displayed in c2name for a reason I can't understand. Also, this customer is matched to customers having bought item number 6 when he never bought it himself.

In need of help and advice if anyone could help.

Here is the script for the database import:

create table products (

    pid         int,    
    
    pname       varchar(30) not null,

    price       decimal(7,2) not null check (price >= 0),

    origin      varchar(20),                          

    constraint products_pk primary key (pid)
);

create table customers (
    cid         int,

    cname       varchar(30) not null,

    residence   varchar(50),                          

    constraint customers_pk primary key (cid)

);

create table orders (
    pid         int,

    cid         int,

    odate       date not null,                          

    quantity    int not null check (quantity > 0),   

    constraint orders_pk primary key (pid, cid, odate),

    constraint orders_fk_pid foreign key (pid) references products (pid),

    constraint orders_fk_cid foreign key (cid) references customers (cid)

);

CodePudding user response：

The query I come up with may not produce the exact result format, but it still gives a similar answer.

SELECT o.pid, GROUP_CONCAT(c.cname) AS cusname, p.pname 
FROM orders AS o
JOIN customers AS c ON c.cid = o.cid
JOIN products AS p ON p.pid = o.pid
WHERE YEAR(o.odate) = 2014
GROUP BY pid

The result table is :

UnknownTable

Hope it helps

CodePudding user response：

This should do. Do take into consideration that your expected output doesn't match with your sample data and what you wrote in question. i.e. for milk you have pairs dupond - jones and jones - smith, it seem sto me that dupond - smith should be present too. They both ordered same product and dupond < smith (I am guessing here that you are doing that comparison based on first letter of name)

SELECT
    c1.cname as c1name, c2.cname as c2name, p.pname
FROM 
    orders o1
    JOIN customers c1 on o1.cid = c1.cid
    JOIN orders o2 on o1.pid = o2.pid and o1.cid != o2.cid
    JOIN customers c2 on o2.cid = c2.cid
    join products p on o1.pid = p.pid
where 
    c1.cname < c2.cname
    and
    year(o1.odate) = 2014
    AND
    year(o2.odate) = 2014
GROUP BY
    c1.cname, c2.cname, p.pname
ORDER BY pname