merging data from another table in another database SQL

Question

Dear all

I currently have this query

   SELECT count (load_id) as [number trips], origin_zip, Destination_Zip,Load_Carrier_ID,Load_Carrier_Description 
 FROM [Rep_UK].[dbo].[Main_table_UK]
 WHERE Origin_Zip = 'pr5 0xd'
  AND [Trip Type] = 'roundtrip'
        AND Empties = 'false'
        AND datepart(year, convert(DATETIME2, deliveryDate, 103)) = 2020
 GROUP BY origin_zip, Destination_Zip,Load_Carrier_Description,Load_Carrier_ID
 HAVING COUNT(*) >= 1;

and I need to incorporate into this the below so it also filters from another (TMS) database,

select* from 
[dbo].[DB_TMS]
where Load_Service_ID like '%LTL%'

if anyone can let me know how I should incorporate into one query would be greatly appreciated!!

thank you

Answer 1

Hi @Jimko1 ,

Welcome to Microsoft Q&A!

It is recommended to provide DDL and sample data of both tables , relationship with each other and expected output.

If you would like to combine the results of both queries and they have similar columns, you could use UNION/UNION ALL as below:

SELECT count (load_id) as [number trips], origin_zip, Destination_Zip,Load_Carrier_ID,Load_Carrier_Description   
FROM [Rep_UK].[dbo].[Main_table_UK]  
WHERE Origin_Zip = 'pr5 0xd'  
AND [Trip Type] = 'roundtrip' AND Empties = 'false'  
AND datepart(year, convert(DATETIME2, deliveryDate, 103)) = 2020  
GROUP BY origin_zip, Destination_Zip,Load_Carrier_Description,Load_Carrier_ID  
HAVING COUNT(*) >= 1  
union   
select 0 [number trips], origin_zip, Destination_Zip,Load_Carrier_ID,Load_Carrier_Description    
from [dbo].[DB_TMS]  
where Load_Service_ID like '%LTL%'  

If you would like to add one condition into the first query, you could use exists/in once they have one unique column.

Please also refer below method which uses IN:

 SELECT count (m.[load_id]) as [number trips], m.[Origin_Zip], m.[Destination_Zip], m.[Load_Carrier_ID], m.[Load_Carrier_Description]   
 FROM [Rep_UK].[dbo].[Main_table_UK] AS m  
 WHERE m.[Origin_Zip] = 'pr5 0xd'  
   AND m.[Trip Type] = 'roundtrip'  
   AND m.[Empties] = 'false'  
   AND datepart(year, convert(DATETIME2, m.[deliveryDate], 103)) = 2020  
   AND Unique_Column in  (  
  SELECT Unique_Column FROM [dbo].[DB_TMS] where Load_Service_ID like '%LTL%')  
 GROUP BY m.[Origin_Zip], m.[Destination_Zip], m.[Load_Carrier_Description], m.[Load_Carrier_ID]  
 HAVING COUNT(*) >= 1;  

Best regards
Melissa

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

I guess there are the same unique columns between both tables, i.e., Unique_Column:

SELECT count (m.[load_id]) as [number trips], m.[Origin_Zip], m.[Destination_Zip], m.[Load_Carrier_ID], m.[Load_Carrier_Description] 
FROM [Rep_UK].[dbo].[Main_table_UK] AS m
WHERE m.[Origin_Zip] = 'pr5 0xd'
  AND m.[Trip Type] = 'roundtrip'
  AND m.[Empties] = 'false'
  AND datepart(year, convert(DATETIME2, m.[deliveryDate], 103)) = 2020
  AND EXISTS (
 SELECT 1 FROM [dbo].[DB_TMS] WHERE Unique_Column = m.Unique_Column AND Load_Service_ID like '%LTL%'
  )
GROUP BY m.[Origin_Zip], m.[Destination_Zip], m.[Load_Carrier_Description], m.[Load_Carrier_ID]
HAVING COUNT(*) >= 1;