CODEWITHSUNDEEP
sqlsql-servert-sql
tarrball
tarrball

Reputation: 2298

Get Distinct From Multiple Similar Tables

I have this query to join a couple tables and get distinct values, it looks something like this:

SELECT DISTINCT [TrackingCode]
    ,[Opponent]
    ,CONCAT([TrackingCode], ' | ', [Opponent]) AS RowName
    ,[MultiYrEvent] 
    ,[Identifier]
FROM [BUDGET_FY2014].[dbo].[TrackingCodes]

INNER JOIN
(
    SELECT * 
    FROM [BUDGET_FY2014].[dbo].[Transactions]
    WHERE Report='2377010003'
) AS T
ON T.EventCode LIKE CAST(TrackingCodes.TrackingCode AS nvarchar(20))+'%'
ORDER BY TrackingCode ASC

It works fine. However, I've got multiple Transactions tables with the same schema for the first and second previous years relative to the Transactions table, and I'd like to see distinct values from all three tables. So for example, if I copy/paste this query and change [Transactions] to [Transactions_Yr1] or [Transactions_Yr2], then I get the data I want from those tables. But, I want to combine the three. If I try to join them all, I get no results returned. I sort of understand why this doesn't work, but I don't know where to go from here:

SELECT DISTINCT [TrackingCode]
    ,[Opponent]
    ,CONCAT([TrackingCode], ' | ', [Opponent]) AS RowName
    ,[MultiYrEvent] 
    ,[Identifier]
FROM [BUDGET_FY2014].[dbo].[TrackingCodes]

INNER JOIN
(
    SELECT * 
    FROM [BUDGET_FY2014].[dbo].[Transactions]
    WHERE Report='2377010003'
) AS T
ON T.EventCode LIKE CAST(TrackingCodes.TrackingCode AS nvarchar(20))+'%'

INNER JOIN
(
    SELECT * 
    FROM [BUDGET_FY2014].[dbo].[Transactions_Yr1]
    WHERE Report='2377010003'
) AS T1
ON T1.EventCode LIKE CAST(TrackingCodes.TrackingCode AS nvarchar(20))+'%'

INNER JOIN
(
    SELECT * 
    FROM [BUDGET_FY2014].[dbo].[Transactions_Yr2]
    WHERE Report='2377010003'
) AS T2
ON T2.EventCode LIKE CAST(TrackingCodes.TrackingCode AS nvarchar(20))+'%'

ORDER BY TrackingCode ASC

Any advice would be appreciated!

Upvotes: 0

Views: 57

Answers (2)

Mat Forsberg
Mat Forsberg

Reputation: 454

have you tried unioning your transaction tables together? Reference: https://msdn.microsoft.com/en-us/library/ms180026.aspx

SELECT DISTINCT [TrackingCode]
    ,[Opponent]
    ,CONCAT([TrackingCode], ' | ', [Opponent]) AS RowName
    ,[MultiYrEvent] 
    ,[Identifier]
FROM [BUDGET_FY2014].[dbo].[TrackingCodes]

INNER JOIN
(
    SELECT * FROM(
    SELECT * 
    FROM [BUDGET_FY2014].[dbo].[Transactions]
    WHERE Report='2377010003'
) AS T
ON T.EventCode LIKE CAST(TrackingCodes.TrackingCode AS nvarchar(20))+'%'

Union
(
    SELECT * 
    FROM [BUDGET_FY2014].[dbo].[Transactions_Yr1]
    WHERE Report='2377010003'
) AS T1
ON T1.EventCode LIKE CAST(TrackingCodes.TrackingCode AS nvarchar(20))+'%'

Union
(
    SELECT * 
    FROM [BUDGET_FY2014].[dbo].[Transactions_Yr2]
    WHERE Report='2377010003'
) AS T2
ON T2.EventCode LIKE CAST(TrackingCodes.TrackingCode AS nvarchar(20))+'%'
)
ORDER BY TrackingCode ASC

Upvotes: 0

Renatto Machado
Renatto Machado

Reputation: 1574

Try use UNION ALL clausele, e.g.:

SELECT DISTINCT [FILDS] 
 FROM (
    SELECT * 
    FROM [BUDGET_FY2014].[dbo].[Transactions]
    WHERE Report='2377010003'

    UNION ALL

    SELECT * 
    FROM [BUDGET_FY2014].[dbo].[Transactions_Yr1]
    WHERE Report='2377010003'

    UNION ALL

     SELECT * 
    FROM [BUDGET_FY2014].[dbo].[Transactions_Yr2]
    WHERE Report='2377010003'
    )

ORDER BY TrackingCode ASC

Upvotes: 5

Related Questions