Results 1 to 7 of 7
  1. #1
    Join Date
    Feb 2005
    Posts
    3

    Unanswered: SQL - conditional join (?)

    I have a bit of a difficult problem that has been bugging me for two weeks. I need to join two tables together, but if there is no matching right-hand record it needs to select a "default record".

    It's for a work registration package where I need to join on a "work factor" table. I have about 200 kinds of work, and about 10 of them have a factor. The rest should link to a default factor. The table with the factors holds a foreign key to the table with the actual work.

    Code:
    -- Work --	-- Factor --
    Name	WorkID	WorkID	Factor
    Type 1	1	0	1	// DEFAULT
    Type 2	2	1	1.5
    Type 3	3	2	1.2
    		3	1.1
    Type 4	4
    Resulting in
    Code:
    Type 1	1.5
    Type 2	1.2
    Type 3	1.1
    Type 4	1	// DEFAULT
    So, if there is no matching factor for the work, it needs to join on a "default" factor.

    This expands to a table that contains the actual work done on the workfloor, but if I can get this going that won't be much of a problem.

    I hope someone can help me with this.

    Thanks

    [edit] I should mention that I'm using the JET engine [/edit]
    Last edited by Structural; 02-15-05 at 03:35.

  2. #2
    Join Date
    Feb 2005
    Posts
    3
    I came up with the following so far:
    Code:
    SELECT Work.work_name, factor.factor
    FROM work LEFT JOIN factor ON factor.work_id = work.work_id OR factor.work_id = 0
    WHERE factor.work_id = (SELECT MAX(factor.work_ID) FROM factor WHERE factor.work_id = work.work_id OR factor.work_id = 0);
    But with the subquery and all I have the feeling this is not particularly one of the most efficient methods.

  3. #3
    Join Date
    Jan 2004
    Location
    The Netherlands
    Posts
    421
    I created it in Access, you will need to change the nz and cdbl to your equivalent:

    Query1:
    Code:
    SELECT Work.Name, Factor.Factor
    FROM [Work] LEFT JOIN Factor ON Work.WorkID = Factor.WorkID;
    Final Query (using query1):
    Code:
    SELECT Query1.Name, CDbl(nz([Factor],[deffactor])) AS Factors
    FROM Query1, (select Factor as DefFactor from factor where workid = 0) AS [Default];
    Regards

  4. #4
    Join Date
    Feb 2005
    Location
    London
    Posts
    19
    I am not sure whether Jet supports Coalesce function or not.... but if it does then following query will work for you

    SELECT Name, Coalesce(factor2.factor, factor1.factor)
    FROM work
    LEFT OUTER JOIN factor AS factor1 ON factor1.workId = 0
    LEFT OUTER JOIN factor AS factor2 ON factor2.workId = work.workId

    Good Luck...

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    no, jay, access doesn't support coalesce

    you must also parenthesize your joins if there are more than two tables

    and the first one should be a cross or inner join, not a left join

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Feb 2005
    Location
    London
    Posts
    19
    I have tested this query in SQL server 2000 and works fine with LEFT OUTER JOIN...

    my apologies, I didn't understand when "Structural" said he is using Jet...

    And for sure work without Parenthesis

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    yes it will work without parentheses in sql server

    no it will not work without parentheses in access
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •