Results 1 to 5 of 5

Thread: What Join type?

  1. #1
    Join Date
    Nov 2008
    Posts
    42

    Unanswered: What Join type?

    Good Morning!

    I have 2 tables, one table with results (eo_ot_results) and one table with locations and job codes (uxlu). I am trying to create a query that will give me all locations and job codes when there are results and when there are no results. I have 12 sites and 6 job codes and I would like my results to return 72 rows. I have tried right join, left join, full outer join and I am not able to get the results I would like. Here is what I have so far:

    Code:
    select 
    --week,
    b.site, 
    b.code,
    (sum(convert(float,minutes))/60) as hours
    
    from eo_ot_results a right join uxlu b
    on a.site = b.site and a.code = b.code
    
    where week = '12/29/08'
    
    group by b.site, b.code
    
    order by b.site, b.code
    Thanks for reading.

    Drew

  2. #2
    Join Date
    Dec 2008
    Posts
    135
    then use cross join for that i think u will get the result by using that

  3. #3
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Yup - what you are describing is a CARTESIAN PRODUCT, essentially joining every row in one table to every row in the other. bklr is spot on.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  4. #4
    Join Date
    Jun 2007
    Location
    Ohio, USA
    Posts
    142
    Not too sure about that cross join. What's up with the [week] column? Does the eo_ot_results column hold more than one week?

    Posting the DDL for your tables, along with your current and desired results, would help a lot. I'm making a lot of assumptions, but I'll take a guess, anyway: I'm thinking derived table.

    Code:
    CREATE TABLE #uxlu (
      site char(1), 
      code char(1)
    )
    GO
     
    CREATE TABLE #eo_ot_results (
      site char(1),
      code char(1),
      week tinyint,
      minutes smallint
    )
    GO
     
    INSERT INTO #uxlu
    SELECT 'a', 'a' UNION ALL
    SELECT 'a', 'b' UNION ALL
    SELECT 'b', 'a' UNION ALL
    SELECT 'b', 'b' UNION ALL
    SELECT 'c', 'a' UNION ALL
    SELECT 'c', 'b' 
    GO
     
    INSERT INTO #eo_ot_results
    SELECT 'a', 'a', 1, 40 UNION ALL
    SELECT 'b', 'b', 1, 50 UNION ALL
    SELECT 'c', 'a', 1, 60 UNION ALL
    SELECT 'c', 'b', 1, 120
    GO
     
    SELECT 
      b.site
     ,b.code
     ,(SUM(CONVERT(FLOAT, minutes)) / 60) AS hours
    FROM #uxlu b
    LEFT OUTER JOIN (
        SELECT 
          site, 
          code, 
          minutes
        FROM #eo_ot_results
        WHERE week = 1
        ) AS a
      ON b.site = a.site
     AND b.code = a.code
    GROUP BY b.site ,b.code
    ORDER BY b.site ,b.code
    GO
    David Maxwell
    Data Integrity? Yeah, I've heard of that...

  5. #5
    Join Date
    Nov 2008
    Posts
    42
    Quote Originally Posted by ReadySetStop
    Not too sure about that cross join. What's up with the [week] column? Does the eo_ot_results column hold more than one week?

    Posting the DDL for your tables, along with your current and desired results, would help a lot. I'm making a lot of assumptions, but I'll take a guess, anyway: I'm thinking derived table.

    Code:
    CREATE TABLE #uxlu (
      site char(1), 
      code char(1)
    )
    GO
     
    CREATE TABLE #eo_ot_results (
      site char(1),
      code char(1),
      week tinyint,
      minutes smallint
    )
    GO
     
    INSERT INTO #uxlu
    SELECT 'a', 'a' UNION ALL
    SELECT 'a', 'b' UNION ALL
    SELECT 'b', 'a' UNION ALL
    SELECT 'b', 'b' UNION ALL
    SELECT 'c', 'a' UNION ALL
    SELECT 'c', 'b' 
    GO
     
    INSERT INTO #eo_ot_results
    SELECT 'a', 'a', 1, 40 UNION ALL
    SELECT 'b', 'b', 1, 50 UNION ALL
    SELECT 'c', 'a', 1, 60 UNION ALL
    SELECT 'c', 'b', 1, 120
    GO
     
    SELECT 
      b.site
     ,b.code
     ,(SUM(CONVERT(FLOAT, minutes)) / 60) AS hours
    FROM #uxlu b
    LEFT OUTER JOIN (
        SELECT 
          site, 
          code, 
          minutes
        FROM #eo_ot_results
        WHERE week = 1
        ) AS a
      ON b.site = a.site
     AND b.code = a.code
    GROUP BY b.site ,b.code
    ORDER BY b.site ,b.code
    GO

    Sorry to be so vague.

    When using the cross join, I am getting the desired results for columns site and code, but the hours value is the same for each row.

    The week column is in the query for times when I pull more than one week's worth of data. The table has several weeks of data.

    The result set I am getting now has 31 rows (only rows that have a value for hours). The result set that is desired would have 72 rows (it would include every site and every code displaying the value 0 if there are no hours).

    Thanks
    Drew

Posting Permissions

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