question

Jimko1-7792 avatar image
0 Votes"
Jimko1-7792 asked Jimko1-7792 commented

merging data from another table in another database SQL

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

sql-server-generalsql-server-transact-sql
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

MelissaMa-msft avatar image
1 Vote"
MelissaMa-msft answered Jimko1-7792 commented

Hi @Jimko1-7792,

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.

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

thnk you very much!

0 Votes 0 ·
GuoxiongYuan-7218 avatar image
1 Vote"
GuoxiongYuan-7218 answered Jimko1-7792 commented

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;
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

thanks very much!

0 Votes 0 ·