-1

I want a query in which if and else will use but I do not know how it will use,below is my table structure ,i want result below mentioned after Produce table.

Item Master Table

item_code    Item_Name   Weight     UOM 
  1001         Pen        44       unit 
  1002       Computer     42       unit

Customer Table

Customer_ID  Customer_Name   Contact    Address
     1             Akhter         3434       3434
     2             Hussain        333        555

Customer Item Table

Customer_ItemCode    Customer_ItemName   Item_code(FK)  Weight  Customer_ID(FK) 
 2001                    Pen America          1           100       1

SalesOrder Table

Order_No     Customer_ID (FK)   Order_Ref   Date     
     1           1                 333       333     
     2           2                 222       222

Produce table

Prod_No Item_Code(FK)    qty    Order_No (FK)
   10001     1            1         1    
   10002     2            1          2

when select 10001 record from Produce table then it result like (10001,Pen America,1) when i select record 10002 then it result like (10002,Computer,1)

  • Please post the DDLs (create table) statements and a few INSERT statements. Most importantly, I would like to see the Constraints (FKs, PKs, etc.) – Michael Kutz Apr 22 at 20:40
  • You would be better posting what you are trying to achieve, rather than saying you want to use IF/ELSE. – Phil Sumner Apr 23 at 14:12
0

The basic query is pretty straight forward:

SELECT p.Prod_No,
    IsNull(Customer_ItemName, i.Item_Name) as Item_Name,
    p.qty
FROM Produce p
INNER JOIN ItemMaster I
    ON p.item_code = i.item_code
INNER JOIN SalesOrder s
    ON s.Order_No = p.Order_No
LEFT JOIN CustomerItem ci
    ON p.item_code = ci.item_code 

This makes some assumptions about your data, such as the fact that there is no item with an item_code of 1, as shown in the Produce table (Probably should be the Product table? Or is this Order_items or something similar). In any case, I'm assuming that 1 = 1001, and 2 = 1002

  • #Laughing Vergil, you didn't make check customer in customer item table,that if item name is define against customer then that item name will be select from customer item file ,if does not mentioned customer in customer item file against item then item will be select from item master file , – akhter Hussain Apr 23 at 2:07

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.