mySQL query, good data structure to handle an exception

Hello SQL specialists,

I have a table with some prices, as follows:

City    Price  
-----   -----  
NY      17      
SF      16      
Moscow  12      
Lima     1      
Berlin  21      
-------------

When I want to find the price, I can handle the query, with the city name as input. No problem.
But now I need to deal with an exception: in only one city, the prices depend on some additional field:

City    Price   Buyers Age
-----   -----   ----------
NY      17      nil
SF      16      nil
Moscow  12      nil
Lima     1      nil
Berlin   5      Child
Berlin  21     MiddleAge
Berlin   7      Retired
--------------------------

Is there any way to make a query, with input Buyers Age and input City, and output price ?
Example: The buyers age is Child, and he is in Lima: what is the price ?

Should I somehow make a 2nd table, to solve it ?

Best regards, Sonja
Sonja_MAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Dan CraciunIT ConsultantCommented:
SELECT Price FROM table WHERE BuyersAge = 'Child' AND City='Lima'

HTH,
Dan
0
Sonja_MAuthor Commented:
Hi Dan,

can you explain me your solution ?: is NIL matched to just anything ?

- Sonja
0
Dan CraciunIT ConsultantCommented:
What's NIL got to do with anything?

The query will simply scan the table for any value 'Child' in the BuyersAge column, then take those results and do another scan in column City for 'Lima', then return the results.

If you plan to run that query often, I would add indexes on those columns.

Tip: you can always use EXPLAIN before a SELECT so MySQL will give you the execution plan for those queries.
EXPLAIN SELECT Price FROM table WHERE BuyersAge = 'Child' AND City='Lima'
0
Acronis True Image 2019 just released!

Create a reliable backup. Make sure you always have dependable copies of your data so you can restore your entire system or individual files.

Sonja_MAuthor Commented:
Dan,
thank you for your quick answer, however, according to your description of the scan, I would expect no matching record. So once again my question: does a not set value ("nil") just match to whatever value you have in the query ?
-Sonja
0
Dan CraciunIT ConsultantCommented:
Nope. Using your sample data, the query will return no results, as there is no 'Child' in 'Lima'.

After the first condition, the result set will look like this:
City    Price   Buyers Age
-----   -----   ----------
Berlin   5      Child
--------------------------

Then MySQL will search for 'Lima' in the City column, will not find it and return no result.
0
Sonja_MAuthor Commented:
okay. This means that, after you indeed read my question, you see that your answers did not solve my question.
0
Dan CraciunIT ConsultantCommented:
OK. Then you'll have to rephrase the question.

This is what I read:
Example: The buyers age is Child, and he is in Lima: what is the price ?

The query in my first post answers that question. There is no child in Lima, so you'll get 0 rows back.
0
Sonja_MAuthor Commented:
no.
0
Dan CraciunIT ConsultantCommented:
:))
Next mind reader, please :)
0
Sonja_MAuthor Commented:
Dan, I have no time for those who answer before reading the question. Goodbye.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Dan CraciunIT ConsultantCommented:
This is usually called "the curse of knowledge".

You spent some time pondering this question, so it's obvious to you. The key word here is "you".

The rest of us have to figure the problem based on what you wrote.
For the next person that will read this thread, please try to write an explicit question.

Goodbye.
0
Sonja_MAuthor Commented:
blabla
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
MySQL Server

From novice to tech pro — start learning today.