CODEWITHSUNDEEP
sql
LoveToys
LoveToys

Reputation: 21

joining tables with both results

I have two tables, t1 and t2. t1 holds contract numbers, while t2 holds contract events for those contracts.

I am trying to select all contracts from t1 and all records from t2 that match t1, but also trying to return all records from t1 where they don't match t2. My query seems to just bring back all records that match both tables.

I have tried:

and yet I still get the same result.

t1 holds 293 results, t2 holds 270 events. Some of the t2 events have multiple events for 1 contractid, while some accounts have no events registered against them.

I want to bring back all contractIDs and all events linked to that contractID, but I also want to return the contracts that have no event, having null in the contractID column.

My current query:

            SELECT  p.contractfk 
                    ,[GetEnddate](p.ContractFK) [Contract End Date] -- function
                    ,CASE 
                                WHEN c.StartDate IS NULL THEN 1
                                WHEN c.StartDate > ce.CreatedDate THEN 1
                                ELSE 0
                                END [PreEngineer]
                    ,CASE 
                                WHEN c.StartDate < ce.CreatedDate THEN 1 ELSE 0
                                END [PostEngineer]
            FROM [CRM].[pbx].[PBX] p
                    LEFT JOIN [ContractEnquiry] ce
                        ON ce.ContractFK = p.ContractFK 
                    LEFT JOIN [Contract] c
                        ON [c].[contractID] = [P].[contractfk]

Upvotes: 0

Views: 112

Answers (2)

Hart CO
Hart CO

Reputation: 34774

It seems like you have your order of tables mixed around a bit, and you have to select ContractID if you want to return it. You can re-order the tables in your query:

        SELECT  DISTINCT c.contractID 
                ,[GetEnddate](p.ContractFK) [Contract End Date] -- function
                ,CASE 
                            WHEN c.StartDate IS NULL THEN 1
                            WHEN c.StartDate > ce.CreatedDate THEN 1
                            ELSE 0
                            END [PreEngineer]
                ,CASE 
                            WHEN c.StartDate < ce.CreatedDate THEN 1 ELSE 0
                            END [PostEngineer]
        FROM [Contract] c
                LEFT JOIN [CRM].[pbx].[PBX] p
                    ON c.[contractID] = p.[contractfk]
                LEFT JOIN [ContractEnquiry] ce
                    ON ce.ContractFK = p.ContractFK

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269953

Your query is a basic left outer join:

select t1.*, t2.*
from t1 left outer join
     t2
     on t1.contractId = t2.ContractId;

If you then want to get one contract, be sure to choose from t1 and not t2:

select t1.*, t2.*
from t1 left outer join
     t2
     on t1.contractId = t2.ContractId
where t1.contractId = MYCONTRACTID;

If you choose from t2, you will turn the left outer join back into an inner join.

Upvotes: 1

Related Questions