CODEWITHSUNDEEP
mysqlsqlmysql-workbench
Mariija Leković
Mariija Leković

Reputation: 1

How to write a query using two tables and auxiliary table?

I have a table:

table1:

CREATE TABLE product(
product_id INT AUTO_INCREMENT NOT NULL,
name VARCHAR (30),
price DECIMAL(20),
PRIMARY KEY (product_id)
);

table2:

CREATE TABLE people(
people_id INT AUTO_INCREMENT NOT NULL,
adress_id INT,
name VARCHAR (20),
lastName VARCHAR (20),
email VARCHAR (50),
PRIMARY KEY (people_id ),
);

auxiliary table:

CREATE TABLE buy (
people_id INT,
product_id int,
productAmount INT,
PRIMARY KEY (people_id , product_id)
);

I have to write a query:

Select all people who have NOT to buy any products.

Upvotes: 0

Views: 210

Answers (4)

JuSun Lee
JuSun Lee

Reputation: 144

Any Query.

select * from people a
where not exists (
  select 1 from buy x where x.people_id = a.people_id
)

Upvotes: 0

Mayuree Budhe
Mayuree Budhe

Reputation: 130

You can use:

select * from people where people_id not in (select distinct people_id from buy);

Upvotes: 0

nbk
nbk

Reputation: 49385

You can use

Select * From People Where people_id not in (Select people_id from buy);

Upvotes: 1

elliottmjohnson
elliottmjohnson

Reputation: 19

SELECT * FROM people
LEFT JOIN buy ON people.people_id = buy.people_id
WHERE buy.people_id IS NULL
GROUP BY people.people_id

Cheers

Upvotes: 2

Related Questions