Solved

SQL Query two tables conditional data

Posted on 2004-09-06
14
329 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
[Live Webinar] The Cloud Skills Gap

As Cloud technologies come of age, business leaders grapple with the impact it has on their team's skills and the gap associated with the use of a cloud platform.

Join experts from 451 Research and Concerto Cloud Services on July 27th where we will examine fact and fiction.

 

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 125 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 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

Question has a verified solution.

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

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

617 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