Hi..
Please help me out again in making a query...
My Scenario is -
I have 5 tables -
Table1 - CompanyMaster
ID(Pk, Unique, Not null)
| CompName
| CompCode
| Flag
|
1
| ABC
| 5001
| 0
|
2
| Abc
| 5001
| 0
|
3
| Pqe
| 5042
| 0
|
4
| PQR
| 5043
| 0
|
Next 2 tables are same as CompanyMaster
i.e. Columns Names are different but, their properties are same.
Table - 2 -Location Master
| ID(Pk, Unique, Not null) | LocationName | LocationCode
| Flag
|
1
| Abc
| LO90
| 0
|
2
| ABC
| LO90 | 0
|
3
| Pqo
| LO90 | 0
|
4
| pqo
| LO100
| 0
|
Same thing is for Table 3 - Vendor Master.
---------
Table4 - ORDERNUMBERS
ID
| ORDER_NO
| GRP_CODE
| Flag
| VendCode
| LocationCode
| CompanyCode
|
1
| 1001
| 10
| 0
| V200
| LO90
| 5001
|
2
| 2002
| 20
| 0
| V202
| LO100
| 5042
|
3
| 3003
| 30
| 0
| V200
| LO100
| 5042
|
4
| 4004
| 40
| 0
| V204
| LO90
| 5043
|
5
| 5005
| 50
| 0
| V300
| LO100
| 5001
|
Table5 - ORDERDETAILS
ID
| Remarks
| Status
| GRP_CODE
| EMP_NO
| DATE
| INWARDS
| companyCode
| LocationCode
| VendorCode
|
1
| test
| 50
| 10
| 1
| 1-jan-15
| 1001
| | LO90 | V300
|
2
| test1
| 10
| 10
| 2
| 1-jan-15 | 1001
| | LO90 | V400
|
3
| test23
| 22
| 20
| 3
| 1-jan-15 | 1002
| | LO90 | V100
|
4
| test45
| 11
| 30
| 4
| 1-jan-15 | 1002
| | LO90 | V100 |
5
| tetst34 | 12
| 20
| 4
| 1-jan-15 | 1003
| 5001 | LO100 | V100 |
6
| tetst34 | 11
| 50
| 6
| 1-jan-15 | 1003
| 5001 | LO100 | V400 |
7
| tetst34 | 50
| 50
| 6
| 1-jan-15 | 1003
| 5043 | LO100 | V400 |
My Requirement - (Dynamic Query - Date , Location, Company and Vendor will be sent from front-end )
I need the following output -
My Query -
select order_no, grp_code from ORDERNUMBERS
where grp_code in (select grp_code in ORDERDETAILS )
---
I have to display the following columns -
1. Company Name according to company Code (company code is not unique).
2. Location Name according to location Code (location code is not unique).
3. Vendor Name according to vendor Code (vendor code is not unique).
4. OrderNo (Table-ORDERNUMBERS)
5. Inward Number, Emp_NO, Remarks, Date (Table -ORDERDETAILS)
6. Where Clause - Date Between (DATE, Table -ORDERDETAILS).