Marc Marc - 20 days ago 8
MySQL Question

Get all Items attached to sellerId - SQL

When execute my query i just get 1 item back that i attached to the sellerId instead of 2. Does anyone know how i can say?

select the name of item and re seller for each item that belongs to the re seller. With a rating higher than 4?

Current Query:

SELECT items.name, sellers.name
FROM items
inner JOIN sellers
on items.id=sellers.id
WHERE rating > 4
ORDER BY sellerId


The query for tables inc. data:

CREATE TABLE sellers (
id INTEGER NOT NULL PRIMARY KEY,
name VARCHAR(30) NOT NULL,
rating INTEGER NOT NULL
);

CREATE TABLE items (
id INTEGER NOT NULL PRIMARY KEY,
name VARCHAR(30) NOT NULL,
sellerId INTEGER REFERENCES sellers(id)
);

INSERT INTO sellers(id, name, rating) values(1, 'Roger', 3);
INSERT INTO sellers(id, name, rating) values(2, 'Penny', 5);

INSERT INTO items(id, name, sellerId) values(1, 'Notebook', 2);
INSERT INTO items(id, name, sellerId) values(2, 'Stapler', 1);
INSERT INTO items(id, name, sellerId) values(3, 'Pencil', 2);

Answer

You've got the wrong join, here's a corrected query;

SELECT items.name, sellers.name
FROM items
inner JOIN sellers
    on items.sellerId=sellers.id
WHERE rating > 4
ORDER BY sellerId

You're joining on id = id, you want sellerid = id

Notice in your table definition that item.sellerId is the field that joins to seller.id

CREATE TABLE items (
  id INTEGER NOT NULL PRIMARY KEY,
  name VARCHAR(30) NOT NULL,
  sellerId INTEGER REFERENCES sellers(id)
);