Results 1 to 8 of 8
  1. #1
    Join Date
    Jan 2009
    Location
    Zoetermeer, Holland
    Posts
    746

    Question Unanswered: Yes I can, with a comma. But I waint to "join"

    Hai,
    I've got a simple table with a date column. I want 1 row per ultimo week. This SQL seems to work, but I like to use the JOIN syntax as much as possible but this will only work (for me) using the gool-old-comma syntax.
    Code:
    ---- only ultimo week rows
    select  b.* , (char(year(b.diskInfo_date)) concat char(week(b.diskInfo_date))) as year_week     
    from    table (select distinct year(diskInfo_date) as year, week(diskInfo_date) as week from diskInfo)
                     as a
       ,    diskInfo as b 
    where b.diskInfo_date = (select max (  diskInfo_date) 
                               from        diskInfo 
                              where year(b.diskInfo_date) = a.year  
    			    and week(b.diskInfo_date) = a.week
                            )
    
    order by year_week 
         , b.mountpoint
    ;
    Any suggestions?

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Your query would be written without join, like this:
    Code:
    SELECT a.*
         , char(YEAR(a.diskInfo_date)) concat char(WEEK(a.diskInfo_date)) as year_week     
      FROM diskInfo a
     WHERE a.diskInfo_date
           = (SELECT MAX(diskInfo_date) 
                FROM diskInfo b 
               WHERE YEAR(b.diskInfo_date) = YEAR(a.diskInfo_date)
                 AND WEEK(b.diskInfo_date) = WEEK(a.diskInfo_date)
             )
     OEDER BY
           year_week 
         , b.mountpoint
    ;
    As a general, if you want to include subquery in join condition(ON clause),
    you can include the subquery inside joined full-select.
    For ,example your query would be like this:
    Code:
    SELECT b.*
         , (char(YEAR(b.diskInfo_date)) concat char(WEEK(b.diskInfo_date))) AS year_week     
      FROM
           (SELECT distinct
                   YEAR(diskInfo_date) AS year
                 , WEEK(diskInfo_date) AS week
              FROM diskInfo
           ) AS a
      INNER JOIN
           TABLE
           (SELECT *
              FROM diskInfo AS b 
             WHERE b.diskInfo_date
                   = (SELECT MAX(diskInfo_date) 
                        FROM diskInfo AS c
                       WHERE YEAR(c.diskInfo_date) = a.year  
                         AND WEEK(c.diskInfo_date) = a.week
                     )
           ) AS b
       ON  0=0
     ORDER BY
           year_week 
         , b.mountpoint
    ;
    Last edited by tonkuma; 09-17-09 at 09:11.

  3. #3
    Join Date
    Jan 2009
    Location
    Zoetermeer, Holland
    Posts
    746
    Thanks Tonkuma. My original query does not work correctly. I do not like the syntax of your second (yes, I know: I asked for it), but you first is correct & compact and I am using it now.

  4. #4
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,369
    Quote Originally Posted by dr_te_z
    the gool-old-comma syntax.
    Sorry to ask a stupid question... I understand that the good-old-comma syntax creates a virtual table, but where can more information about this "comma syntax"?

  5. #5
    Join Date
    May 2009
    Posts
    508
    Provided Answers: 1
    db2girl, the the 'gool-old-comma syntax' syntax is referring to the way the join is coded. It actually has nothing to do with a Nested (or virtual) table.

    What (for lack of a better term), I will call the old fashioned or classic syntax for a join is:
    Code:
    FROM table1 , table2
    where tabl1.col1 = table2.col1
    or table1 'comma' table2.

    the newer (ANSI standard, I believe) form of the join is
    Code:
    FROM table1 INNER JOIN table2
             ON tabl1.col1 = table2.col1
    So the Subject of the post is referring to is Joining with a comma (with the join predicate in the Where clause) as opposed to Joining with an Inner Join (with the join predicate in an On clause).

    PS You could have one or more Nested (or Virtual) tables with either format:
    Code:
    'comma' join:
    FROM (SELECT COL1, COL2 FROM table1) as A
       , (SELECT COL1, COL2 FROM table2) as B
    WHERE A.COL1 = B.COL1
    
    Inner Join:
    FROM (SELECT COL1, COL2 FROM table1) as A
           INNER JOIN
         (SELECT COL1, COL2 FROM table2) as B
           ON A.COL1 = B.COL1

  6. #6
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,369
    I re-read the thread and I think I know what this "comma syntax" is - way to join tables. I was thinking about a different comma... Please disregard my question.

  7. #7
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,369
    Thanks, Stealth_DBA. I realized my mistake. Next time I'll think more before asking stupid questions

  8. #8
    Join Date
    Jan 2009
    Location
    Zoetermeer, Holland
    Posts
    746
    Quote Originally Posted by db2girl
    Thanks, Stealth_DBA. I realized my mistake. Next time I'll think more before asking stupid questions
    NO! it was not you, it was me causing confusion choosing the wrong words.
    I've come to "know" you (in this virtual world) as someone giving good asnwers instead of asking stupid questions.

    I started this thread to get tips and learn more about SQL. I alwasy try use code JOIN when more than 1 table is accessed because it keeps your "where clause" pure. All the relationsships between the tables are coded in the "on clause" of the join and the search-criteria are coded in the "where". In this case I could only solve my problem using the comma-syntax and that bugged me.
    Last edited by dr_te_z; 09-20-09 at 08:41.

Posting Permissions

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