Solved

SQL Query two tables conditional data

Posted on 2004-09-06
14
322 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
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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
I have a large data set and a SSIS package. How can I load this file in multi threading?
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

786 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