Solved

SQL Query two tables conditional data

Posted on 2004-09-06
14
317 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
 

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
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
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

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
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.
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.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

757 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now