Customer item name display ,if item master id exit in

Akhter HUssain

7y826SQL

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

4 Replies

Know the answer? Post it — somebody with the same question will find it here.

Akhter HUssain7y ago
can you elobrate query little bit
0
Amit Kumar7y ago
Hi, You can see here table item master ,Customer item and product item have common field ItemCode. Which item you want select from product table get it's ItemCItemCode and check that this item code exists or not in item master again check this item code is exists or not in Customer item table it it exists then select CItemName from Customer respective ItemCode If this item code is not exists in Customer item then display the itemname from item master. If you implement it it will work perfectly.
0
Akhter HUssain7y ago
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 Amerca,1)
when i select record 10002 then it result like (10002,Computer,1)

Please suggest...thanks
0
Mohsin Arif7y ago
hi

please expalin breifly its not understandable what you want , and as you share structure of tables its not perfect

CItemcode,CItemName,CustomerID,ItemCode

two times itemcode is defined i can't understand from whom i can join.
0

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

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

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

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

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