CODEWITHSUNDEEP
sql-serverjoininner-join
jmrivas
jmrivas

Reputation: 137

Inner Join to Same Table Twice on same column

I'm having a problem with a SQL Server query trying to join a view with another view twice

SELECT 
    FAC.* 
FROM 
    ViewFacturacionDiaria_Test AS FAC
INNER JOIN
    ViewInformacionRepresentantes AS REP
        ON REP.RepIDTabacal = FAC.Vendedor
INNER JOIN
    ViewInformacionRepresentantes AS REP2
        ON REP2.RepIDCtayOrden = FAC.Vendedor
WHERE
    FecCpbte BETWEEN '2015-11-28' AND '2015-11-30'

In the "FAC" view I have sales information, in the other one I have a specific group of sales person which I want to filter from the main view.

I would like to understand why the query is returning an empty resultset.

Upvotes: 1

Views: 9194

Answers (1)

Andrew O'Brien
Andrew O'Brien

Reputation: 1823

Sorry, I cannot comment. But I believe Peter is right in his comment. Since you are using 2 inner joins they both need to return results. Are you expecting both joins to find a match?

Try this and see which column is null. That is the join that is resulting in no returned rows.

SELECT 
    FAC.Vendedor
    ,REP.RepIDTabacal
    ,REP2.RepIDCtayOrden
FROM 
    ViewFacturacionDiaria_Test AS FAC
LEFT JOIN
    ViewInformacionRepresentantes AS REP ON
    REP.RepIDTabacal = FAC.Vendedor
LEFT JOIN
    ViewInformacionRepresentantes AS REP2 ON
    REP2.RepIDCtayOrden = FAC.Vendedor
WHERE
    FecCpbte BETWEEN '2015-11-28' AND '2015-11-30'

Upvotes: 4

Related Questions