SQLTeam.com | Weblogs | Forums

If else condition between table ,how it will use

sql2008r2
#1

I have item master table in which column are (ItemCode,ItemName,Weight) and second one table is customer item table(CItemcode,CItemName,CustomerID,ItemCode),
third table is customer (CustomerID,CustomerName)
fourth table is Produce item (Prodno,Itemcode,ItemName,Weight,Orderno).
Fifth table salesorder(Ornerno,CustomerID,Qty)

Now i want when select record from Produce item table, then item code from item master table, if exit in customer item table then CItemName get display otherwise if item code of item master file does not exit in customer item table against customer then item name display from item master file.

please help me..

#2

Like this?

select a.itemcode
      ,isnull(c.citemname,b.itemname)
  from produceitem as a
       left outer join mastertable as b
                    on b.itemcode=a.itemcode
       left outer join customeritemtable as c
                    on c.itemcode=a.itemcode
;
1 Like
#3

it is not given my result please review my requirement

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 ,,please help thanks....

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 suggest...thanks

#4

From the sample data you provided, there is no way of getting to the master table, from prod_no 10002.

I think this might work for you:

select a.prod_no
      ,isnull(nullif(b.customer_itemname,''),c.item_name)
  from produceitem as a
       inner join customeritemtable as b
               on b.item_code=a.itemcode
       left outer join mastertable as c
                    on c.itemcode=b.customer_itemcode
;

If not, please provide:

  • table definitions in the form of create statements
  • sample data in the form of insert statements
  • expected output from the sample data you provide
  • a description of how you would arrive at master table, if no customer item table rows for a given prod_no exists
#5

you query just approx my result but here you missed one thing that you didn't make check customer ID in customer item table ,that if customer_itemcode is open against any customer then itemname will be select from customer item table ,if customer_itemcode is not mentioned against any customerID in customer item table , then item will be select from itemmaster table..

#6

CREATE TABLE [dbo].[ItemMasterFile]( [CodeItem] [int] IDENTITY(1,1) NOT NULL, [Descriptionitem] [varchar](50) NULL, [BaleSize] [varchar](50) NULL, [weight] [int] NULL, [SecID] [int] NULL, [Packsize] [varchar](50) NULL, [ALID] [varchar](50) NULL, [IDUOM] [int] NULL, [entryDate] [date] NULL, CONSTRAINT [PK_ItemMasterFile]

CREATE TABLE [dbo].[CustomerItem]( [CItem] [int] IDENTITY(1,1) NOT NULL, [CIName] [varchar](50) NULL, [CustomerID] [int] NOT NULL, [Codeitem] [int] NULL, [Price] [int] NULL, [Address] [varchar](50) NULL, CONSTRAINT [PK_CustomerItem] PRIMARY KEY CLUSTERED

CREATE TABLE [dbo].[Customer]( [CustomerID] [int] IDENTITY(1,1) NOT NULL, [CustomerName] [varchar](50) NULL, [Contactno] [varchar](50) NULL, [Address] [varchar](50) NULL, CONSTRAINT [PK_Customer] PRIMARY KEY CLUSTERED

CREATE TABLE [dbo].[SalesOrder]( [OrderNo] [int] NOT NULL, [Order_Ref_No] [varchar](50) NULL, [CustomerID] [int] NULL, [Order_date] [date] NULL, [SOpirority] [int] NULL, [Status] [varchar](50) NULL, CONSTRAINT [PK_SalesOrder] PRIMARY KEY

CREATE TABLE [dbo].[Probale]( [Prdno] [int] IDENTITY(1000001,1) NOT NULL, [OrderNo] [int] NOT NULL, [Codeitem] [int] NULL, [prdqty] [int] NULL, [EntryDate] [date] NOT NULL, [Weigth] [nchar](10) NULL, [IDwokrer] [int] NULL, [FID] [int] NULL, [udate] [date] NULL, [DelID] [int] NULL, [ddate] [date] NULL, CONSTRAINT [PK_Probale] PRIMARY KEY CLUSTERED (

#7

And now please provide sample data we can use to be able to give you an answer

Insert into xyz
Select 1,2,3 union
Select 4,5,6

#8

in which table you want to insert

#9

All of the ones needed to answer your question