Jump to content

MySQL Join breaking


Kevz
 Share

Recommended Posts

Creating a shopping list app, i have a drop down to display the location of the item or meal

dishes table

id | dish | items | locationid
:--------|:--------:|---------:|---------:
1| fajitas | chicken,wraps | 1
2| curry| chicken,tomatos | 1
2| | mayonaise | 2

location table

id | location
:--------|:--------:
1| meal
2| fridge

displaying items with this query

     SELECT * FROM dishes LEFT JOIN location ON dishes.locationid = location.id ORDER BY dish ASC"

however now, it's taking the locationid, and using that as the dish id, so things like edit are broken as it's grabbing the wrong ID, am i using the wrong join clause?

Link to comment
Share on other sites

1 minute ago, Barand said:

IDs are supposed to be unique identifiers. You have two meals with id = 2.

Your items need to be normalised - don't store comma-separated lists.

yeah sorry that was just typo on here.

i fixed it with this query instead

SELECT d.id,d.dish,d.items,d.locationid,d.inlist,l.location FROM dishes AS d LEFT JOIN location AS l ON d.locationid = l.id ORDER BY dish ASC

thank you, why shouldn't i store as comma seperated?

Link to comment
Share on other sites

Simple answer - it's bad design.

Database tables are not spreadsheets. To get maximum functionality the tables should be designed using relational design principles, which means they should be normalised.

EDIT: Looks like you discovered another basic rule - don't use "select * ", specify the columns you need.

Edited by Barand
Link to comment
Share on other sites

This thread is more than a year old.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

 Share

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.