Jump to content
Kevz

MySQL Join breaking

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?

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
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?

Share this post


Link to post
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

Share this post


Link to post
Share on other sites

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.


×
×
  • 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.