phpmyadmin Question posted in
The official documentation can be found here.

Displaying top-selling product pairs – Phpmyadmin

I have a problem, I’ve spent a lot of time on. I have to display the pairs of items (Item1, Item2, How many) that appear on the invoices – and how many pairs have repeated. The table that connects invoices to goods is called "Transactions".

I Have 3 tables:

Items: IdItem, Name
Transactions: IdTransaction, IdInvoice, IdItem
Invoices: IdInvoice

Result of SQL Query must be:

Item1 | Item2 | Number

Tags:

2

Answers


  1. 0

    Assuming your tables look like this:

    CREATE TABLE Items (
   IDItem INT,
   Name VARCHAR,
   PRIMARY KEY(IDItem)
);

CREATE TABLE Invoices (
   IDInvoice INT,
   PRIMARY KEY(IDInvoice)
);
CREATE TABLE Transactions (
   IDTransaction INT,
   IDInvoice INT,
   IDItem INT,
   PRIMARY KEY(IDTransaction),
   FOREIGN KEY (IDItem) REFERENCES Items(IDItem),
   FOREIGN KEY (IDInvoices) REFERENCES Invoices(IDInvoices)
)

    If this is true then you need to query the results like this:

    SELECT i.Name,in.IDInvoice FROM Transactions t
JOIN Items i ON i.IDItem = t.IDItem
JOIN Invoices in ON in.IDInvoice = t.IDInvoice
WHERE i.Name = item1
    Login or Signup to reply.
  2. 0

    This sounds like a self-join and aggregation:

    select t1.IdItem as IdItem1, t2.IdItem as IdItem2, count(*)
from transactions t1 join
     transactions t2
     on t1.IdInvoice = t2.IdInvoice and
        t1.IdItem < t2.IdItem
group by t1.IdItem, t2.IdItem
order by count(*) desc;
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search