CODEWITHSUNDEEP
mysql
Wuagliono
Wuagliono

Reputation: 402

how to retrieve foreign key linked to a certain primary key

I have a simple MySQL DB with a table which has PK composed by 2 varchar columns.

CREATE TABLE prodotti(
type_prod varchar(10) not null,
model_prod varchar(10) not null,
brand_prod varchar(20) not null,
name_prod varchar(30) not null,
year_prod int not null, 
description_prod varchar(500) not null,
price_prod float not null,
qnt_prod int not null,

PRIMARY KEY(type_prod,model_prod) );

There are also other table with FKs linked to this PK (in this case,I show you just 2 of these tables)

CREATE TABLE cpu_component(
id_cpu varchar(10) not null,
series_cpu varchar(20) not null,    
num_cores int not null,
frequency_ghz int not null,
socket_cpu int not null,
label_cpu varchar(10),
model_cpu varchar(10),

PRIMARY KEY (id_cpu),

FOREIGN KEY(label_cpu,model_cpu) REFERENCES prodotti(type_prod,model_prod)
ON UPDATE CASCADE
ON DELETE CASCADE );

CREATE TABLE motherboard_component(
id_motherboard varchar(10) not null,
series_motherboard varchar(20) not null,
chipset_motherboard varchar(15) not null,
socket_motherboard int not null,
type_ram_motherboard varchar(15) not null,
ram_frequency_index int not null,
slot_ram int not null,
hdmi_ports_gpu int not null,
usb_ports_motherboard int not null,
bios_motherboard varchar(10) not null,
label_motherboard varchar(10),
model_motherboard varchar(10),

PRIMARY KEY (id_motherboard),

FOREIGN KEY(label_motherboard,model_motherboard) REFERENCES prodotti(type_prod,model_prod)
ON UPDATE CASCADE
ON DELETE CASCADE );

I want to retrieve all FKs linked to a specific PK obtained by user input

P.S this is my first question here, please be clement lol

Upvotes: 2

Views: 41

Answers (1)

Omari Celestine
Omari Celestine

Reputation: 1435

You can use SQL's INNER JOIN statements to achieve this in which joining the tables prodotti and cpu_component will give you all the rows from cpu_component that are also in prodotti.

SELECT p.type_prod, p.model_prod, cpu.num_cores
FROM prodotti p
INNER JOIN cpu_component cpu ON(cpu.label_cpu = p.type_prod AND cpu.model_cpu = model_prod)

As for joining the table prodotti with the table motherboard_component:

SELECT p.type_prod, p.model_prod, mc.chipset_motherboard
FROM prodotti p
INNER JOIN motherboard_component mc ON(mc.label_motherboard = p.type_prod AND mc.model_motherboard = p.model_prod)

Upvotes: 1

Related Questions