Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

SQL Query two tables conditional data

Posted on 2004-09-06
14
Medium Priority
?
330 Views
Last Modified: 2008-02-01
Hi There

I am quite new to sql but for one of my projects, i have to write a query on two tables to acheive the following:

I have two tables

Table 1 : Table 1  : Customer
 
Name                 Group
Customer1           A1
Customer2           A2
 
Where for example sake, A1 is the customer, and A2 is the distributor
 
Table 2 :  Inventory
 
Name            Price1           Price2
Choclate            20              10
Icecream           12               5

The client will enter in the customer name and product name, and he wants to display the price for that customer.

If the customer belongs to Group A1 (Customer1) then it will cost him $20 to buy a choclate and $12 to buy an icecrean

If the customer belongs to A2 group, then price2 should be shown

so basically the end result of the query should look like

Customer Name     Product Name     Value
Customer1             Choclae              20
Customer2             Icecream            5

The customer name and product name are parameters of the query and they would be entered each time the client wants to find out the right price for the customer

sounds simple and is easily done in a programming language like delphi but i am not sure how to do this in SQL as i am using access.

any suggestions?
 
When you run a query , you would enter the customer name and the product name so dont worry about the linking of these two tables
 
so first i want to look at customer.type and then select inventory.type1 if it is A1 or inventory.type2 if it is A2
0
Comment
Question by:chanpreet
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 3
  • 3
  • +1
14 Comments
 
LVL 11

Expert Comment

by:ram2098
ID: 11993774
This is how you can write the query........

DECLARE @CUSTNAME VARCHAR(50),
        @PRODNAME VARCHAR(50)

SELECT @CUSTNAME = 'CUST1'
SELECT @PRODNAME = 'CHO'

SELECT A.NAME,
       B.NAME,
       (CASE WHEN A.[GROUP] = 'A1' THEN PRICE1
             WHEN A.[GROUP] = 'A2' THEN PRICE2
       END) AS PRICE
FROM CUSTOMER1 A, INVENTORY B
WHERE A.NAME = @CUSTNAME
AND   B.NAME = @PRODNAME
0
 
LVL 11

Expert Comment

by:ram2098
ID: 11993778
If you want to create the same as stored procedure..write it as below...

Create Procedure Up_FindPrice
     @CUSTNAME VARCHAR(50),
     @PRODNAME VARCHAR(50)
AS

SELECT @CUSTNAME = 'CUST1'
SELECT @PRODNAME = 'CHO'

SELECT A.NAME,
       B.NAME,
       (CASE WHEN A.[GROUP] = 'A1' THEN PRICE1
             WHEN A.[GROUP] = 'A2' THEN PRICE2
       END) AS PRICE
FROM CUSTOMER1 A, INVENTORY B
WHERE A.NAME = @CUSTNAME
AND   B.NAME = @PRODNAME


0
 
LVL 11

Expert Comment

by:ram2098
ID: 11993779
The above are example queries........Change the table and field names accordingly in the query (as per your database)
0
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 

Author Comment

by:chanpreet
ID: 11993870
Hi ram

thanks for your quick response. Unfortunately  that doesnt seem to work for me.
This query is to be written in access 2003

the first error i got was that "access is expecting a select,update,insert ) value

so i removed all the declarations and used simple code similiar to

SELECT A.TITLE, B.TITLE,
      (CASE WHEN A.[ZTIER] = 'A1' THEN PRICE1
             WHEN A.[ZTIER] = 'A2' THEN PRICE2
       END) AS PRICE
FROM CUSTOMER A, INVENTRY B
WHERE A.title = 'BEVS'
AND   B.title = 'JB100'

where ztier is the Group and Title is the name

i have predefined the customer and the product for now to avoid any paramter issues.

the error i get when i run the above query is

"Syntax error (missing operator) in query expression '(case when a.[ztier]= 'A1' then price1 etc ... END').

i have also tried double qoutes "A1" around a1 and a2 as they are strings but no difference.
0
 
LVL 3

Expert Comment

by:Minna
ID: 11993909
Should've asked it in the MS Access section not the Microsoft SQL section.
0
 
LVL 10

Expert Comment

by:imrancs
ID: 11994110
here is alternative to CASE statement in Access

SELECT A.TITLE, B.TITLE,
      (IIF (A.[ZTIER] = 'A1' , PRICE1,  A.[ZTIER] = 'A2' PRICE2) AS PRICE
FROM CUSTOMER A, INVENTRY B
WHERE A.title = 'BEVS'
AND   B.title = 'JB100'



Imran
0
 
LVL 10

Expert Comment

by:imrancs
ID: 11994118
sorry typo...

remove the '(' before the IIF

SELECT A.TITLE, B.TITLE,
      IIF (A.[ZTIER] = 'A1' , PRICE1,  A.[ZTIER] = 'A2' PRICE2) AS PRICE
FROM CUSTOMER A, INVENTRY B
WHERE A.title = 'BEVS'
AND   B.title = 'JB100'


Imran
0
 
LVL 10

Expert Comment

by:imrancs
ID: 11994126
OOps.. another typo. (copy/paste)


SELECT A.TITLE, B.TITLE,
      IIF (A.[ZTIER] = 'A1' , PRICE1,  PRICE2) AS PRICE
FROM CUSTOMER A, INVENTRY B
WHERE A.title = 'BEVS'
AND   B.title = 'JB100'


Imran
0
 

Author Comment

by:chanpreet
ID: 11994376
Oh cool that almost works

just one thing, how do i write the same thing in a case statement instead of a if then statement as i have about 5 groups and 5 prices.

thanks
0
 
LVL 10

Expert Comment

by:imrancs
ID: 11994404

SELECT A.TITLE, B.TITLE,
      IIF (A.[ZTIER] = 'A1' , PRICE1,IIF(A.[ZTIER] = 'A2',  PRICE2, IIF(A.[ZTIER] = 'A3', PRICE3,IIF(A.[ZTIER] = 'A4',PRICE4,PRICE5)))) AS PRICE
FROM CUSTOMER A, INVENTRY B
WHERE A.title = 'BEVS'
AND   B.title = 'JB100'



Imran
0
 

Author Comment

by:chanpreet
ID: 11994407
another question, though could be easily ignored as its not a part of the original question, is there anyway to restrict or convert the input paramters (text) into a query to be upper case.

for instance in the above case i want the customer name and product name entered to uppercase no waht how the user enters it

my current query is similiar to

PARAMETERS CustNo Text ( 255 ), ProdNo Text ( 255 );
SELECT A.TITLE, B.TITLE, IIf(A.ZTIER='A2',PRICE1,PRICE2) AS PRICE
FROM CUSTOMER AS A, INVENTRY AS B
WHERE A.ACCOUNTNO=[CustNo] And B.ACCOUNTNO=[ProdNo];

0
 
LVL 10

Expert Comment

by:imrancs
ID: 11994519
use UCASE() fucntion to convert to upper case


SELECT UCASE(A.TITLE) AS CustomerTitle , UCASE( B.TITLE) AS InventoryTitle, IIf(A.ZTIER='A2',PRICE1,PRICE2) AS PRICE
FROM CUSTOMER AS A, INVENTRY AS B
WHERE A.ACCOUNTNO = [CustNo] And B.ACCOUNTNO=[ProdNo];


Imran
0
 
LVL 10

Accepted Solution

by:
imrancs earned 500 total points
ID: 11994539
if you want to convert parameters to Upper Case then its better to use UCASE() on the both side in WHERE, like


SELECT UCASE(A.TITLE) AS CustomerTitle , UCASE( B.TITLE) AS InventoryTitle, IIf(A.ZTIER='A2',PRICE1,PRICE2) AS PRICE
FROM CUSTOMER AS A, INVENTRY AS B
WHERE UCASE(A.ACCOUNTNO) = UCASE([CustNo]) And UCASE(B.ACCOUNTNO) = UCASE([ProdNo]);


Imran
0

Featured Post

Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

Question has a verified solution.

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

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

670 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