Learn how easy it is to sync an existing GitHub or Google Code repo to a SourceForge project! See Demo

Close

Subselects & cross joins

Rob
2009-05-19
2013-04-15
  • Rob
    Rob
    2009-05-19

    Hey,

    This is a great app. No major ERD/SQL application seems to have this feature.

    I'm coming across queries with subselects that cause trouble. Rather than bother to dig through nested subselects, could RSJ grab the columns defined in the top-most sub-select and ignore and lower ones? Most of what you need from the lower ones is usually referenced in the top most ones like below:

    SELECT N1.ID, MAIN_TAB.TITLE FROM
      /* Take columns from this one */
      (SELECT ID, TITLE, DESCRIPTION FROM
          /* Completely ignore this one */
          (SELECT ID, TITLE, DESCRIPTION FROM SUB_TABLE1)
      ) MAIN_TAB
    LEFT JOIN
      /* Take columns from this one */
      (SELECT ID, NOTE FROM NOTES_1) N1 ON N1.ID = MAIN_TAB.ID

    Also, it handles cross joins as expected, but it gets tripped up when a cross joined table comes after a normal join:

    Doesn't work:
    ...
    FROM TABLE_1 T1
        LEFT  JOIN TABLE_1A T1A ON T1.ID=T1A.ID
    , TABLE_2
    ...

    Works:
    ...
    FROM TABLE_1 T1, TABLE_2
        LEFT  JOIN TABLE_1A T1A ON T1.ID=T1A.ID
    ...

    Best,
    Rob

     
    • alxtoth
      alxtoth
      2009-05-19

      Hi,

      Thanks for the feedback! These real-life queries are extremely valuable.

      Currently the tool only handles some "medium SQL". Therefore next will implement ORDER BY, SELECT DISTINCT and COUNT DISTINCT. This will hopefully cover "simple SQL " cases.

      Planning to solve the WHERE X IN (SELECT...) case beforehand, because it is needed to find the "output columns" (those between SELECT and WHERE). Same will help with the subselects you mention.

      Please be patient, I'm trying to concentrate on the basics, and fix existing bugs. For example the one you've sent about the mixed joins and tables.

      -Alex

       
    • alxtoth
      alxtoth
      2009-05-19

      Hi,

      This is fixed in version v0.27

      ...
      FROM TABLE_1 T1 
      LEFT JOIN TABLE_1A T1A ON T1.ID=T1A.ID
      , TABLE_2
      ...

      -Alex

       
    • alxtoth
      alxtoth
      2009-06-20

      Hi,

      There is some progress with  since version v0.30 with

      SELECT ID, TITLE, DESCRIPTION FROM 
      (SELECT ID, TITLE, DESCRIPTION FROM SUB_TABLE1) 
      ) MAIN_TAB
      LEFT JOIN ....

      Is it something in the right direction ?

      -Alex

       
  • alxtoth
    alxtoth
    2011-04-13

    Fixed in ver 0.44

    SELECT N1.ID, MAIN_TAB.TITLE FROM
    /* Take columns from this one */
    (SELECT ID, TITLE, DESCRIPTION FROM
    /* Completely ignore this one */
    (SELECT ID, TITLE, DESCRIPTION FROM SUB_TABLE1)
    ) MAIN_TAB
    LEFT JOIN
    /* Take columns from this one */
    (SELECT ID, NOTE FROM NOTES_1) N1 ON N1.ID = MAIN_TAB.ID

    -Alex