Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

SQL Query two tables conditional data

Posted on 2004-09-06
14
Medium Priority
?
333 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
  • 6
  • 3
  • 3
  • +1
13 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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 

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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
This shares a stored procedure to retrieve permissions for a given user on the current database or across all databases on a server.
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.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…
Suggested Courses

564 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