?
Solved

Mysql Lookup Q

Posted on 2007-07-24
2
Medium Priority
?
188 Views
Last Modified: 2010-04-01
I've got two Mysql tables:

Table: Lookup
ID | Value
-------------
0  | Zero
1  | One
2  | Two

And my main table, has lots of values.
First,Last,Middle,Address,LOOKUP_ID
Where lookup id is 1,2,3..

I'd like to be able to do a search like:
where first="bob" and lookup_id = "Zero"
Convert the lookup_id to its value, and search my main table for the lookup_id returned.

I'm pretty sure it's a standard lookup, but I don't know the syntax.  Also, I'm not sure how I trap if no results come back from the lookup?  What if they search for "blahblah" that doesn't exist in my lookup table?
0
Comment
Question by:ecuguru
  • 2
2 Comments
 
LVL 17

Accepted Solution

by:
Aleksandar Bradarić earned 1500 total points
ID: 19557923
Try this:
---
SELECT
  t1.First,
  t1.Last,
  t1.Middle,
  t1.Address,
  t1.Lookup_ID,
  t2.Value
FROM
  yourTable t1
  LEFT JOIN Lookup t2 ON t2.ID = t1.Lookup_ID
WHERE
  t1.First = 'bob' AND
  t2.Value = 'Zero'
---
0
 
LVL 17

Expert Comment

by:Aleksandar Bradarić
ID: 19557928
> What if they search for "blahblah" that doesn't exist in my lookup table?

Nothing will be returned (empty result set).
0

Featured Post

 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

Question has a verified solution.

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

When table data gets too large to manage or queries take too long to execute the solution is often to buy bigger hardware or assign more CPUs and memory resources to the machine to solve the problem. However, the best, cheapest and most effective so…
This article shows the steps required to install WordPress on Azure. Web Apps, Mobile Apps, API Apps, or Functions, in Azure all these run in an App Service plan. WordPress is no exception and requires an App Service Plan and Database to install
The goal of the tutorial is to teach the user how to use functions in C++. The video will cover how to define functions, how to call functions and how to create functions prototypes. Microsoft Visual C++ 2010 Express will be used as a text editor an…
The viewer will be introduced to the technique of using vectors in C++. The video will cover how to define a vector, store values in the vector and retrieve data from the values stored in the vector.
Suggested Courses

850 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