Skip to main content
T
trojnfn
Inspiring
January 21, 2008
Answered

Query Problem

  • January 21, 2008
  • 2 replies
  • 546 views
I am using the following query in my cfform action page :

<cfquery name="qry" datasource="dsname">
select
Detail.user_employee_number,
Detail.user_status,
Detail.role_id,
Master.role_id,
Master.role,
Master.region,
Master.site
from Master left join Detail
on Master.role_id = Detail.role_id
where Detail.user_employee_number = '#url.user_employee_number#'
order by role,region,site,user_status
</cfquery>

I want to find all master records regardless of a match. If there are ten master records and only five detail records, I want all ten master records to be retrieved and displayed. What I am getting is only two records, which is the two matches that are found.

I always though left join would accomplish this, but I guess not. What am I doing wrong and what is the proper sql to do what I want to do, with the selection above ?

Thanks
    This topic has been closed for replies.
    Correct answer paross1
    Try changing where Detail.user_employee_number = '#url.user_employee_number#' to and Detail.user_employee_number = '#url.user_employee_number#'. This will preserve the OUTER join. Once you reference the DETAIL table in the WHERE clause, you lose that, so include the clause in the FROM by making it an AND in addition to the ON clause in the JOIN.

    select
    Detail.user_employee_number,
    Detail.user_status,
    Detail.role_id,
    Master.role_id,
    Master.role,
    Master.region,
    Master.site
    from Master left join Detail
    on Master.role_id = Detail.role_id
    and Detail.user_employee_number = '#url.user_employee_number#'
    order by role,region,site,user_status

    Phil

    2 replies

    D
    Dan_Bracuk
    Inspiring
    January 21, 2008
    Whenever you do a left join
    select stuff from t1 left join t2,

    and constrain on t2 in the where clause
    where t2.something = something

    you are effectively doing an inner join. The workaround is to put a subquery in your select clause.

    select stuff from t1 left join
    (select stuff
    from t2
    where something = something) YOU_NEED_THIS_ALIAS on etc
    P
    paross1
    Participating Frequently
    January 21, 2008
    quote:
    The workaround is to put a subquery in your select clause.
    ....Or, as I suggested, move the "constraint" out of the where, and add it as an AND to the OUTER JOIN.

    Phil
    P
    paross1Correct answer
    Participating Frequently
    January 21, 2008
    Try changing where Detail.user_employee_number = '#url.user_employee_number#' to and Detail.user_employee_number = '#url.user_employee_number#'. This will preserve the OUTER join. Once you reference the DETAIL table in the WHERE clause, you lose that, so include the clause in the FROM by making it an AND in addition to the ON clause in the JOIN.

    select
    Detail.user_employee_number,
    Detail.user_status,
    Detail.role_id,
    Master.role_id,
    Master.role,
    Master.region,
    Master.site
    from Master left join Detail
    on Master.role_id = Detail.role_id
    and Detail.user_employee_number = '#url.user_employee_number#'
    order by role,region,site,user_status

    Phil
    T
    trojnfnAuthor
    Inspiring
    January 22, 2008
    Phil, this works.

    Thanks
    Terms & ConditionsAccessibility statement
    Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices