asked on

Left Join

I have two select quiers which I want to left join. However when I try the left join SQL Server 2005 changes the code to read LEFT OUTER JOIN.

Here is a sample of my code:

SELECT tb1.AttDate AS Attendance_Date, SUM(tb1.Rows1) AS RW3, SUM(tb2.Rows1) AS RW4
FROM (SELECT RowID, AttendanceDate, Derived_Provider, 1 AS Rows1, CAST(MONTH(AttendanceDate) AS VarChar(20)) + '-' + CAST(YEAR(AttendanceDate)
AS VarChar(20)) AS AttDate
FROM OPA_General AS OPA_General
WHERE (AttendanceDate BETWEEN CONVERT(DATETIME, '17/09/2007 00:00:00', 103) AND CONVERT(DATETIME, '17/09/2008 00:00:00', 103)) AND
(LEFT(Derived_Provider, 3) = 'RW3')) AS tb1 LEFT OUTER JOIN
(SELECT RowID, AttendanceDate, Derived_Provider, 1 AS Rows1, CAST(MONTH(AttendanceDate) AS VarChar(20)) + '-' + CAST(YEAR(AttendanceDate)
AS VarChar(20)) AS AttDate
FROM OPA_General AS OPA_General
WHERE (AttendanceDate BETWEEN CONVERT(DATETIME, '03/01/2008 00:00:00', 103) AND CONVERT(DATETIME, '05/01/2008 00:00:00', 103)) AND
(LEFT(Derived_Provider, 3) = 'RW6')) AS tb2 ON tb1.AttDate = tb2.AttDate
GROUP BY tb1.Derived_Provider, tb1.AttDate

Thanks

chapmandew

left join is the same as left outer join.

Kevin Cross

LEFT JOIN is same as LEFT OUTER JOIN so you are fine, but I will take a look at the code as you may not want an outer join and may need to use INNER JOIN instead.

Mr_Shaw

ASKER

Ok...

My query times out. What is the likely reason for this?

ASKER CERTIFIED SOLUTION

chapmandew

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Mr_Shaw

ASKER

I have tried Inner Joiner.

The results which I am after are more suited to a left join.

Kevin Cross

You SQL will return all records from the first query in the from clause t1 and only those records in second t2 that match, so your result may have records that only have data in t1 and not t2.

If you want only data that is in both tables and match, try INNER JOIN like suggested above like this.

Otherwise, you are getting the LEFT JOIN you desire.

SELECT     tb1.AttDate AS Attendance_Date, SUM(tb1.Rows1) AS RW3, SUM(tb2.Rows1) AS RW4
FROM         (SELECT     RowID, AttendanceDate, Derived_Provider, 1 AS Rows1, CAST(MONTH(AttendanceDate) AS VarChar(20)) + '-' + CAST(YEAR(AttendanceDate) 
                                              AS VarChar(20)) AS AttDate
                       FROM          OPA_General AS OPA_General
                       WHERE      (AttendanceDate BETWEEN CONVERT(DATETIME, '17/09/2007 00:00:00', 103) AND CONVERT(DATETIME, '17/09/2008 00:00:00', 103)) AND 
                                              (LEFT(Derived_Provider, 3) = 'RW3')) AS tb1 INNER JOIN
                          (SELECT     RowID, AttendanceDate, Derived_Provider, 1 AS Rows1, CAST(MONTH(AttendanceDate) AS VarChar(20)) + '-' + CAST(YEAR(AttendanceDate)
                                                    AS VarChar(20)) AS AttDate
                            FROM          OPA_General AS OPA_General
                            WHERE      (AttendanceDate BETWEEN CONVERT(DATETIME, '03/01/2008 00:00:00', 103) AND CONVERT(DATETIME, '05/01/2008 00:00:00', 103)) AND
                                                    (LEFT(Derived_Provider, 3) = 'RW6')) AS tb2 ON tb1.AttDate = tb2.AttDate
GROUP BY tb1.Derived_Provider, tb1.AttDate

Open in new window

Kevin Cross

Sorry for the out of sequence posted. :) Typing too slow today.

Kevin Cross

This might be slowing you down too:
CONVERT(DATETIME, '17/09/2007 00:00:00', 103) AND CONVERT(DATETIME, '17/09/2008 00:00:00', 103))

Kevin Cross

Try this...

SELECT     tb1.AttDate AS Attendance_Date, SUM(tb1.Rows1) AS RW3, SUM(tb2.Rows1) AS RW4
FROM         (SELECT     RowID, AttendanceDate, Derived_Provider, 1 AS Rows1, CAST(MONTH(AttendanceDate) AS VarChar(20)) + '-' + CAST(YEAR(AttendanceDate) 
                                              AS VarChar(20)) AS AttDate
                       FROM          OPA_General AS OPA_General
                       WHERE      (AttendanceDate BETWEEN CONVERT(DATETIME, '09/17/2007 00:00:00', 103) AND CONVERT(DATETIME, '09/17/2008 00:00:00', 103)) AND 
                                              (LEFT(Derived_Provider, 3) = 'RW3')) AS tb1 LEFT OUTER JOIN
                          (SELECT     RowID, AttendanceDate, Derived_Provider, 1 AS Rows1, CAST(MONTH(AttendanceDate) AS VarChar(20)) + '-' + CAST(YEAR(AttendanceDate)
                                                    AS VarChar(20)) AS AttDate
                            FROM          OPA_General AS OPA_General
                            WHERE      (AttendanceDate BETWEEN CONVERT(DATETIME, '03/01/2008 00:00:00', 103) AND CONVERT(DATETIME, '05/01/2008 00:00:00', 103)) AND
                                                    (LEFT(Derived_Provider, 3) = 'RW6')) AS tb2 ON tb1.AttDate = tb2.AttDate
GROUP BY tb1.Derived_Provider, tb1.AttDate

Open in new window

Mr_Shaw

ASKER

Hi Chapmandew suggested that the left () function was causing a problem. He was right... Thanks.

Now the code is grouping all the results of the second query into one row in the second column. I have attached a screen shot so you can see what I mean. Also here is the SQL code

SELECT     tb1.AttDate AS Attendance_Date, SUM(tb1.Rows1) AS RW3, SUM(tb2.Rows1) AS RW4
FROM         (SELECT     RowID, AttendanceDate, Derived_Provider, 1 AS Rows1, CAST(MONTH(AttendanceDate) AS VarChar(20)) + '-' + CAST(YEAR(AttendanceDate) 
                                              AS VarChar(20)) AS AttDate
                       FROM          OPA_General AS OPA_General
                       WHERE      (AttendanceDate BETWEEN CONVERT(DATETIME, '17/09/2007 00:00:00', 103) AND CONVERT(DATETIME, '17/09/2008 00:00:00', 103)) AND 
                                              (LEFT(Derived_Provider, 3) = 'RW3')) AS tb1 LEFT OUTER JOIN
                          (SELECT     RowID, AttendanceDate, Derived_Provider, 1 AS Rows1, CAST(MONTH(AttendanceDate) AS VarChar(20)) + '-' + CAST(YEAR(AttendanceDate)
                                                    AS VarChar(20)) AS AttDate
                            FROM          OPA_General AS OPA_General
                            WHERE      (AttendanceDate BETWEEN CONVERT(DATETIME, '03/01/2008 00:00:00', 103) AND CONVERT(DATETIME, '05/01/2008 00:00:00', 103)) AND
                                                    (LEFT(Derived_Provider, 3) = 'RW6')) AS tb2 ON tb1.AttendanceDate = tb2.AttendanceDate
GROUP BY tb1.Derived_Provider, tb1.AttDate
ORDER BY RIGHT(tb1.AttDate, 4), CAST(LEFT(tb1.AttDate, CHARINDEX('-', tb1.AttDate) - 1) AS INT)

Open in new window

sql-query.bmp

Mr_Shaw

ASKER

Does anybody have a suggestion