CODEWITHSUNDEEP
mysqlselect
user8116198
user8116198

Reputation:

MySQL | Selecting data from another table

I am trying to use a foreign key in order to setup a system where I can refer to this table to get a friendly name, rather than using the id in the api.

Currently, I have it set up like this...

| id | friendly_name
| 1  | starters  
| 2  | desserts

And I have another table like this...

| id | type | price | desc
| 1  | 1    | 3.99  | This is the desc

I want to use the type to refer to in the api so i have /api/menu/{id}/{type} but I want it to be written like /api/menu/1/starters.

I am currently trying this code but it is returning every item in the database

SELECT
   m.item,
   m.description,
   m.price
FROM
   menu m,
   types t   
WHERE
    m.restaurant_id = :restaurant_id
AND 
    t.id = :type

Upvotes: 1

Views: 36

Answers (1)

simon.ro
simon.ro

Reputation: 3302

You need to add a ON clause to your join and you need to select by type.friendly_namenot type.id.

SELECT
   m.item,
   m.description,
   m.price
FROM
   menu m INNER JOIN types t ON m.type = t.id  
WHERE
   m.restaurant_id = :restaurant_id
   AND 
   t.friendly_name= :type

Upvotes: 1

Related Questions