0

Example pseudo-database:

Table "items":
    PRIMARY item_id number,
    FOREIGN factory_id,
    name string,
    price number,

Table "shops":
    PRIMARY shop_id number,
    address string

Table "factories":
    PRIMARY factory_id number,
    address string,
    status string

Table "items_in_shops":
    FOREIGN item_id,
    FOREIGN shop_id

If I want to know which shop.address has item.name created on factory.address, which SQL query I need to use? (For example, MySQL query).

I guess it possible using several queries (pseudo-code):

facotry_address = "example address";
item_name = "toy";
factory_id = SELECT factory_id FROM factories WHERE address = {factory_address};
item_id = SELECT item_id FROM items WHERE name = {item_name} AND factory_id = {factory_id};
shop_ids = SELECT shop_id FORM items_in_shops WHERE item_id = {item_id};
for (shop_id in shop_ids):
    shop_address = SELECT addres FROM shops WHERE shop_id = {shop_id};

But how can I access it with only one query? Which MySQL construction should I use?

Shadow
  • 33,525
  • 10
  • 51
  • 64
Don2Quixote
  • 228
  • 2
  • 12
  • 1
    Ever heard of JOINs? https://dev.mysql.com/doc/refman/8.0/en/join.html – P.Salmon Nov 10 '20 at 14:00
  • @P.Salmon I did `SELECT items.name AS item_name, shops.address AS shop_address, factories.address AS factory_address FROM items_in_shops JOIN items ON items_in_shops.item_id = items.id JOIN shops ON items_in_shops.shop_id = shop_id JOIN factories ON items.factory_id = factories.id WHERE items.name = "Car toy" AND factories.address = "Far away street"` And it did what I wanted! Thanks you. But is it best query I could do? May be I still know not everything, because this query is too long for this job in my opinion. – Don2Quixote Nov 10 '20 at 15:16

0 Answers0