473,473 Members | 1,960 Online
Bytes | Software Development & Data Engineering Community
Create Post

Home Posts Topics Members FAQ

VLookup equivalent MySQL multiple criteria

1 New Member
Hi everyone, first post so thanks in advance.

I have been trawling sites for 2 days trying to find the answer to this so apologies if I missed something (MySQL newbie!).

I have a table of reference data that presents a standard cost based on 3 criteria, example data shown below:

TABLE_COSTS

Location|Size|SLA|Cost
London|Small|Economy|5000
London|Medium|Economy|6000
London|Large|Premium|10000
Singapore|Small|Economy|4000
(note this is a sample, there are many more combos)

And I have a another table with a list of components that can have any combo of the first three columns as criteria i.e.

TABLE_COMPONENTS

Component|Location|Size|SLA
A|Singapore|Small|Economy
B|London|Small|Economy
C|London|Large|Premium
etc

Here's the challenge. I need to identify the cost associated with each component and ideally store it as an additional column in the TABLE_COMPONENTS or create another temp table.

Hope that's clear. Thanks a lot.

Gareth
Jun 6 '12 #1
1 3875
Rabbit
12,516 Recognized Expert Moderator MVP
You posted in the wrong forum. MS SQL Server is not MySQL. I have moved your thread.

User an Inner Join on the tables on the three fields. Don't store the price twice, there's no need to. You can just join the tables whenever you need to get the price.
Jun 6 '12 #2

Sign in to post your reply or Sign up for a free account.

Similar topics

4
by: ShyGuy | last post by:
I have a table with 4 fields. Three are used for criteria. I can get the DLookup to work with 1 criteria with the following but can't get it to work with 2 or three. NumofAppts = DLookup("",...
3
by: developing | last post by:
Hello How do I specify multiple criteria for FindFirst or FindRecord (or anything else) that takes the criteria from a form. (text field and number field) edit: this will be in the after...
4
by: JHNielson | last post by:
I have a query that I'm trying to update with a dlookup with multiple criteria This is the string: EVNT_DT: DLookUp("","","( .EVNT_QTR=.) & (.=.)") When i run it it says it can't find the...
1
by: 2D Rick | last post by:
I want to open a report using OpenReport and passing it a Where Clause with multiple criteria. I know the Where Clause below is way off but it conveys what I need. Dates are of string type. ...
0
by: ChadK | last post by:
I am trying to open a report based on what the user selects on a form. Each individual criteria works but when I try to combine to pass multiple criteria it doesn't. I have read what I can find on...
2
by: Emre DÝNÇER | last post by:
is it possible to have a multiple criteria switch in C# switch(name , surname){ case "John","Smith" break; } thanks in advance
1
by: akirekab | last post by:
I am using DCount, but I am not able to find how to set simple multiple criteria. Here is sample of what i need. =DCount("PatientProfileID","qryFaceToFaceReason_EAP_VG","FaceToFaceReasonID=2"...
3
by: kstevens | last post by:
Please help. I know the sysntax is wrong. Here are some details. I am looking for the sum of past shipped items. I originally wrote this Dsum with only one criteria, but because of multiple...
1
by: Brendan Wolf | last post by:
Happy Halloween all, I have been struggling at work with a DLookup using multiple criteria. I would like a text box to display the results of a DLookup based on the values selected in three...
3
by: Brendan Wolf | last post by:
Happy Halloween all, I have been struggling at work with a DLookup using multiple criteria. I would like a text box to display the results of a DLookup based on the values selected in three...
0
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
0
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
1
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...
0
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing,...
0
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and...
0
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The...
0
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated ...
1
muto222
php
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
0
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.