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

dr_te_z · #1 (**permalink**) 09-17-09, 05:51

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?

tonkuma · #2 (**permalink**) 09-17-09, 08:06

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
;

dr_te_z · #3 (**permalink**) 09-18-09, 03:03

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.

db2girl · #4 (**permalink**) 09-18-09, 21:51

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"?

Stealth_DBA · #5 (**permalink**) 09-18-09, 22:37

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

db2girl · #6 (**permalink**) 09-18-09, 22:37

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.

db2girl · #7 (**permalink**) 09-18-09, 22:45

Thanks, Stealth_DBA. I realized my mistake. Next time I'll think more before asking stupid questions

dr_te_z · #8 (**permalink**) 09-20-09, 07:24

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.

Internet Services

Web Development

Digital Marketing

Consumer Tech