CODEWITHSUNDEEP
sqlpostgresqlmany-to-many
Alex Mc.
Alex Mc.

Reputation: 13

Filtering a many to many (PostgreSQL)

create table "users"(
  "id"        SERIAL PRIMARY KEY NOT NULL,
  "full_name" varchar(100) NOT NULL,
  "role"      integer NOT NULL DEFAULT 0,
  "email"     varchar(100) NOT NULL
);

create table "devices"(
  "id" SERIAL PRIMARY KEY NOT NULL,
  "brand_name" varchar(20) NOT NULL
);

create table "users_and_devices"(
    "user_id" integer NOT NULL,
    "device_id" integer NOT NULL,
    foreign key (user_id) references users(id),
    foreign key (device_id) references devices(id)
);

How can I get the brand_name of all devices that belong to user 1 ? And should I add indexes to user_id and device_id in users_and_devices ?

Thank you.

Upvotes: 1

Views: 355

Answers (2)

Trenton Trama
Trenton Trama

Reputation: 4930

All you have to do is a simple join across the devices and users_and_devices tables. Did you try that? 

select brand_name 
from devices d
left join users_and_devices ud on (ud.device_id=d.id)
where ud.user_id=1

And yes, I'd put an index on user_id and device_id because you're going to be querying the table for those specific columns.

Upvotes: 1

Adriano Carneiro
Adriano Carneiro

Reputation: 58615

All brands owned by user_id 1, eliminating duplicates:

select distinct d.brand_name
from users_and_devices ud
inner join devices d on ud.device_id = d.id
where ud.user_id = 1

Upvotes: 1

Related Questions