How To Get Data From Multiple Tables Without Duplications-CodePudding

I have two tables tbl_Enquiry_Items and tbl_Estimation_Items contains Enquiry and Estimations Respectively

I am Fetching Data from Tbl_Estimation_Items Using Query

Select * from tbl_Estimation_Items Where Estimation_Id= 1

returning data Set of 5 Records. How can i get 6th row also Present in tbl_Enquiry as Well without duplication.

There is No Foreign key Between Them.. There is Another tables Which Contains Enquiry_id namely Tbl_Estimation

query i tried is

 
DECLare @EnquiryId int
Select @EnquiryId= Enquiry_Id from tbl_Estimation where  Estimation_Id= 1

Select   Estimation_Id, Rfx_Item_No , Material_No ,RTRIM( LTRIM(Description)) as Description , Quantity,Principal_Unit_Price, Total,Li_Discount_Percent,
Li_Discount_Amount ,Li_Principal_Total,Li_Principal_Currency,Li_Petromar_Unit_Price, Li_Petromar_Total,Li_Exchange_Currency,Li_Exchange_Rate,
Li_Petromar_Unit_Price_Fc,Li_Petromar_Total_Fc

into #temp from(
Select    a.Estimation_Id, a.Rfx_Item_No , a.Material_No ,a.Description , a.Quantity,a.Principal_Unit_Price, a.Total,a.Li_Discount_Percent,
a.Li_Discount_Amount ,a.Li_Principal_Total,a.Li_Principal_Currency,a.Li_Petromar_Unit_Price, a.Li_Petromar_Total,a.Li_Exchange_Currency,a.Li_Exchange_Rate,
a.Li_Petromar_Unit_Price_Fc,a.Li_Petromar_Total_Fc
from tbl_Estimation_Items a
left outer join tbl_Estimation b on a.Estimation_Id = b.Estimation_Id
left outer join tbl_Enquiry c on  b.Enquiry_Id = c.Enquiry_Id
where a.Estimation_Id= 1

Union

Select  1 as Estimation_Id, Rfx_Item_No ,  Material_No , Description , 
0.00 as Quantity, 0.00 as Principal_Unit_Price, 0.00 as Total, 0.00 as Li_Discount_Percent,
0.00 as Li_Discount_Amount ,0.00 as Li_Principal_Total, 0.00 as Li_Principal_Currency,0.00 as Li_Petromar_Unit_Price, 0.00 as Li_Petromar_Total,0.00 as Li_Exchange_Currency, 0.00 as Li_Exchange_Rate,
0.00 as Li_Petromar_Unit_Price_Fc,0.00 as Li_Petromar_Total_Fc

from

tbl_Enquiry_Items  where Enquiry_Id =@EnquiryId  
) tt

select *from #temp  

drop table #temp

but giving me following result set

CodePudding user response：

You can use UNION, but if you select all the columns in tbl_Estimation_Items, it is needed to complete the values for the columns which are not present in tbl_Enquiry_Items. Or you can also just select the columns present in both tables. Here selecting all the columns:

SELECT 
  *
FROM 
  tbl_Estimation_Items WHERE Estimation_Id=1
UNION ALL
SELECT 
  Enquiry_Item_Id AS Estimation_Item_id,
  Estimation_Id,
  Rfx_Item_No,
  Material_No,
  Description,
  NULL as Quantity,
  NULL as Principal_Unit_Price
-- Keep adding the rest of the columns with null or assign a value
FROM
  tbl_Enquiry_Items WHERE Enquiry_Item_Id = 911

CodePudding user response：

It is not completely clear what you are trying to achieve.

There is an enquiry. An enquiry can have many estimations. The enquiry can cover more items than its estimations. Your database also allows the estimations to have items that are not part of the enquiry.

What you are probably looking for is an outer join. The following shows all rows from both item tables, whether they have a match in the other or not:

select *
from tbl_estimation est
join tbl_estimation_items esti on esti.estimation_id = est.estimation_id
full outer join tbl_enquiry_items enqi on enqi.enquiry_id = est.enquiry_i
                                      and enqi.rfx_item_no = esti.rfx_item_no

If you want the data for a particular estimation, then change

from tbl_estimation est

from (select * from tbl_estimation where estimation_id = 1) est