[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now


MySQL Left Join - Appending to variable

Posted on 2011-10-06
Medium Priority
Last Modified: 2012-05-12
I'm joining two tables.  

Let's say table one is...

Table Name: items

Item        ID
Widget    5

Table two:

Table Name: prices

Item Price         URLID
25.00               item/5

My SQL goes like...

FROM items t1
LEFT JOIN prices t2 ON t2.URLID = 'item'.t1.ID
WHERE t1.ID = '5'

What would the proper syntax be to do that?

As you can see - ID's variable in the first table (in this instance 5) relates to the 'item/VARIABLE' in the second table (in this instance, item/5).

The " t2.URLID = 'item'.t1.ID" is apparently wrong syntax - as it doesn't work.

How would I properly phrase that?

Question by:erzoolander
  • 2
  • 2

Author Comment

ID: 36927917
'item/'.t1.ID I mean.  Forgot the slash
LVL 24

Expert Comment

ID: 36927967
should just be t1.id. You don't need 'item' since you already aliased it with t1

Author Comment

ID: 36928484
Will it match up though, since URLID = item/5 and t1.id = 5?

LVL 24

Accepted Solution

johanntagle earned 2000 total points
ID: 36928511
Ah missed that part.  So it should be t1.id/5.

It might cause a slow query though as it might not use an index in t1.id (if any).  Well, if it's just a one-time thing then that's okay.  If you plan to do this regularly, suggest you add another column to items with the values already divided by 5.

Featured Post

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this series, we will discuss common questions received as a database Solutions Engineer at Percona. In this role, we speak with a wide array of MySQL and MongoDB users responsible for both extremely large and complex environments to smaller singl…
In this blog, we’ll look at how improvements to Percona XtraDB Cluster improved IST performance.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses
Course of the Month19 days, 15 hours left to enroll

873 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question