none
OUTER JOIN more than two tables

    Question

  • Hi, i'm trying  to get the rows of inquiry list that has or has not the teacher name or even the count of any records in the child table that rows may by exist or not  , but when i try to get the inquiry list rows first i had to inner join with more than two tables and then outer join with the two tables this is the code :

    SELECT co.[name] , el.cors_time , el.enq_lst_cont AS notes , tech.[NAME] AS teach_name , el.order_id , count(enquery_list_details.order_id) AS reqs_count

    FROM  COURSE AS co , enquery_list AS el , LVL_CORS_TBL AS lct , LVL_DEP_TBL AS ldt 

    RIGHT JOIN enquery_list_details 

    ON enquery_list_details.order_id = el.order_id

    RIGHT JOIN TEACHER AS tech

    ON tech.TEACH_NO = el.teach_no

    WHERE el.LVL_CORS_KEY = lct.LVL_CORS_KEY

        AND ldt.LVL_DEP_KEY = lct.LVL_DEP_KEY

        AND lct.LVL_DEP_KEY = @lvl_dep_key

        AND lct.CORS_NO = co.CORS_NO

        AND MONTH(el.enq_date) <= MONTH(GETDATE())

        AND YEAR(el.enq_date) = YEAR(GETDATE())

        AND el.enq_lst_cont = @enq_lst_type  

    GROUP BY eld.order_id , el.order_id , el.cors_time, el.enq_lst_cont , co.[name] , tech.[NAME]

    END 

    Monday, June 21, 2010 1:33 AM

Answers

  • It may be easier to understand the query if you switch to use left outer joins. Here is a query to try:

    SELECT co.[name], 
        el.cors_time, 
        el.enq_lst_cont AS notes, 
        tech.[NAME] AS teach_name, 
        el.order_id, 
        COUNT(enquery_list_details.order_id) AS reqs_count
    FROM TEACHER AS tech
    LEFT OUTER JOIN enquery_list AS el 
     ON tech.TEACH_NO = el.teach_no
     AND el.enq_date >= DATEADD(YEAR, DATEDIFF(YEAR, 0, CURRENT_TIMESTAMP), 0)
     AND el.enq_date < DATEADD(MONTH, DATEDIFF(MONTH, 0, CURRENT_TIMESTAMP) + 1, 0)
     AND el.enq_lst_cont = @enq_lst_type 
    LEFT OUTER JOIN enquery_list_details
     ON enquery_list_details.order_id = el.order_id
    LEFT OUTER JOIN LVL_CORS_TBL AS lct 
     ON el.LVL_CORS_KEY = lct.LVL_CORS_KEY
     AND lct.LVL_DEP_KEY = @lvl_dep_key
    LEFT OUTER JOIN LVL_DEP_TBL AS ldt 
     ON ldt.LVL_DEP_KEY = lct.LVL_DEP_KEY
    LEFT OUTER JOIN COURSE AS co 
     ON lct.CORS_NO = co.CORS_NO
    GROUP BY el.order_id, el.cors_time, el.enq_lst_cont, co.[name], tech.[NAME];


    Plamen Ratchev
    • Marked as answer by Rashiya Friday, June 25, 2010 4:11 AM
    Monday, June 21, 2010 2:41 PM
    Moderator

All replies

  • Modify the query as below. You cannot mix up the tables in the from clause of query with JOINS and without joins

     

    SELECT co.[name] , el.cors_time , el.enq_lst_cont AS notes , tech.[NAME] AS teach_name , el.order_id , count(enquery_list_details.order_id) AS reqs_count

    FROM  COURSE AS co

    INNER JOIN LVL_CORS_TBL AS lct

      ON lct.CORS_NO = co.CORS_NO

    INNER JOIN LVL_DEP_TBL AS ldt

      ON ldt.LVL_DEP_KEY = lct.LVL_DEP_KEY

    INNER JOIN enquery_list AS el

      ON el.LVL_CORS_KEY = lct.LVL_CORS_KEY

    RIGHT JOIN enquery_list_details

      ON enquery_list_details.order_id = el.order_id

    RIGHT JOIN TEACHER AS tech

    ON tech.TEACH_NO = el.teach_no

    WHERE lct.LVL_DEP_KEY = @lvl_dep_key

      AND MONTH(el.enq_date) <= MONTH(GETDATE())

      AND YEAR(el.enq_date) = YEAR(GETDATE())

      AND el.enq_lst_cont = @enq_lst_type 

    GROUP BY eld.order_id , el.order_id , el.cors_time, el.enq_lst_cont , co.[name] , tech.[NAME]

    Monday, June 21, 2010 6:14 AM
  • Hi, Sorna when i try to execute the code that you write this error was occurred :

    Msg 4104, Level 16, State 1, Procedure get_enqs_times3, Line 15

    The multi-part identifier "eld.order_id" could not be bound. 


    Monday, June 21, 2010 2:21 PM
  • It may be easier to understand the query if you switch to use left outer joins. Here is a query to try:

    SELECT co.[name], 
        el.cors_time, 
        el.enq_lst_cont AS notes, 
        tech.[NAME] AS teach_name, 
        el.order_id, 
        COUNT(enquery_list_details.order_id) AS reqs_count
    FROM TEACHER AS tech
    LEFT OUTER JOIN enquery_list AS el 
     ON tech.TEACH_NO = el.teach_no
     AND el.enq_date >= DATEADD(YEAR, DATEDIFF(YEAR, 0, CURRENT_TIMESTAMP), 0)
     AND el.enq_date < DATEADD(MONTH, DATEDIFF(MONTH, 0, CURRENT_TIMESTAMP) + 1, 0)
     AND el.enq_lst_cont = @enq_lst_type 
    LEFT OUTER JOIN enquery_list_details
     ON enquery_list_details.order_id = el.order_id
    LEFT OUTER JOIN LVL_CORS_TBL AS lct 
     ON el.LVL_CORS_KEY = lct.LVL_CORS_KEY
     AND lct.LVL_DEP_KEY = @lvl_dep_key
    LEFT OUTER JOIN LVL_DEP_TBL AS ldt 
     ON ldt.LVL_DEP_KEY = lct.LVL_DEP_KEY
    LEFT OUTER JOIN COURSE AS co 
     ON lct.CORS_NO = co.CORS_NO
    GROUP BY el.order_id, el.cors_time, el.enq_lst_cont, co.[name], tech.[NAME];


    Plamen Ratchev
    • Marked as answer by Rashiya Friday, June 25, 2010 4:11 AM
    Monday, June 21, 2010 2:41 PM
    Moderator
  • Try this one

     

    SELECT co.[name] , el.cors_time , el.enq_lst_cont AS notes , tech.[NAME] AS teach_name , el.order_id , count(enquery_list_details.order_id) AS reqs_count

    FROM  COURSE AS co

    INNER JOIN LVL_CORS_TBL AS lct

      ON lct.CORS_NO = co.CORS_NO

    INNER JOIN LVL_DEP_TBL AS ldt

      ON ldt.LVL_DEP_KEY = lct.LVL_DEP_KEY

    INNER JOIN enquery_list AS el

      ON el.LVL_CORS_KEY = lct.LVL_CORS_KEY

    RIGHT JOIN enquery_list_details

      ON enquery_list_details.order_id = el.order_id

    RIGHT JOIN TEACHER AS tech

    ON tech.TEACH_NO = el.teach_no

    WHERE lct.LVL_DEP_KEY = @lvl_dep_key

      AND MONTH(el.enq_date) <= MONTH(GETDATE())

      AND YEAR(el.enq_date) = YEAR(GETDATE())

      AND el.enq_lst_cont = @enq_lst_type 

    GROUP BY el.order_id , el.order_id , el.cors_time, el.enq_lst_cont , co.[name] , tech.[NAME]

    Monday, June 21, 2010 3:07 PM
  • Thanks a lot MR. Plamen the code you write simplified the understanding but i had to change some of the code to get the wanted result . and thanks Sorna for you help .

    This is the code after the changing :

    FROM enquery_list AS el

    LEFT OUTER JOIN TEACHER AS tech 

     ON el.teach_no = tech.TEACH_NO

     AND el.enq_date >= DATEADD(YEAR, DATEDIFF(YEAR, 0, CURRENT_TIMESTAMP), 0)

     AND el.enq_date < DATEADD(MONTH, DATEDIFF(MONTH, 0, CURRENT_TIMESTAMP) + 1, 0)

    LEFT OUTER JOIN enquery_list_details

     ON enquery_list_details.order_id = el.order_id

    LEFT OUTER JOIN LVL_CORS_TBL AS lct 

     ON el.LVL_CORS_KEY = lct.LVL_CORS_KEY

     AND lct.LVL_DEP_KEY = @lvl_dep_key

    LEFT OUTER JOIN LVL_DEP_TBL AS ldt 

     ON ldt.LVL_DEP_KEY = lct.LVL_DEP_KEY

    INNER JOIN COURSE AS co 

     ON lct.CORS_NO = co.CORS_NO

     AND el.enq_lst_cont = @enq_lst_type

    GROUP BY el.order_id, el.cors_time, el.enq_lst_cont, co.[name], tech.[NAME]; 

    Monday, June 21, 2010 3:25 PM