MySQL Left Join - Appending to variable

Posted on 2011-10-06
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
    LVL 1

    Author Comment

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

    Expert Comment

    should just be You don't need 'item' since you already aliased it with t1
    LVL 1

    Author Comment

    Will it match up though, since URLID = item/5 and = 5?

    LVL 24

    Accepted Solution

    Ah missed that part.  So it should be

    It might cause a slow query though as it might not use an index in (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

    IT, Stop Being Called Into Every Meeting

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    Join & Write a Comment

    Fore-Foreword Today (2016) Maxmind has a new approach to the distribution of its data sets.  This article may be obsolete.  Instead of using the examples here, have a look at the MaxMind API ( …
    Does the idea of dealing with bits scare or confuse you? Does it seem like a waste of time in an age where we all have terabytes of storage? If so, you're missing out on one of the core tools in every professional programmer's toolbox. Learn how to …
    Hi everyone! This is Experts Exchange customer support.  This quick video will show you how to change your primary email address.  If you have any questions, then please Write a Comment below!
    how to add IIS SMTP to handle application/Scanner relays into office 365.

    729 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

    Need Help in Real-Time?

    Connect with top rated Experts

    20 Experts available now in Live!

    Get 1:1 Help Now