0

Having the following query as example:

SELECT t1.itemid,
       t2.yearcreated
FROM   (SELECT '100051' AS 'itemid',
               '2012'   AS yearcreated
        UNION
        SELECT '100051' AS 'itemid',
               '2013'   AS yearcreated
        UNION
        SELECT '100052' AS 'itemid',
               '2011'   AS yearcreated
        UNION
        SELECT '100052' AS 'itemid',
               '2012'   AS yearcreated
        UNION
        SELECT '100052' AS 'itemid',
               '2013'   AS yearcreated) t1
       RIGHT OUTER JOIN (SELECT '2011' AS yearcreated
                         UNION
                         SELECT '2012'
                         UNION
                         SELECT '2013') t2
         ON t1.yearcreated = t2.yearcreated
ORDER  BY t1.itemid,
          t2.yearcreated

It gives this result:

100051  2012
100051  2013
100052  2011
100052  2012
100052  2013

What i need to change in order to get 1 row per year like this?

100051  2011(desired new row generated by correct outer join)
100051  2012
100051  2013
100052  2011
100052  2012
100052  2013

Take into acount that the real query will have more columns that need grouping by or min() function to be shown..

Martin Smith
  • 438,706
  • 87
  • 741
  • 845
VSP
  • 2,367
  • 8
  • 38
  • 59

1 Answers1

1

Your explanation is somewhat unclear.

To get your desired results in this instance you can use a CROSS JOIN rather than a RIGHT JOIN

SELECT DISTINCT  t1.itemid,
       t2.yearcreated
FROM   (SELECT '100051' AS 'itemid',
               '2012'   AS yearcreated
        UNION
        SELECT '100051' AS 'itemid',
               '2013'   AS yearcreated
        UNION
        SELECT '100052' AS 'itemid',
               '2011'   AS yearcreated
        UNION
        SELECT '100052' AS 'itemid',
               '2012'   AS yearcreated
        UNION
        SELECT '100052' AS 'itemid',
               '2013'   AS yearcreated) t1
       CROSS JOIN (SELECT '2011' AS yearcreated
                         UNION
                         SELECT '2012'
                         UNION
                         SELECT '2013') t2
ORDER  BY t1.itemid,
          t2.yearcreated
Martin Smith
  • 438,706
  • 87
  • 741
  • 845
  • The problem is i cant use a CROSS JOIN becouse the adaptor for executing the sql querys doesnt admit it :S. Alternatives? The simplified explanation is this: itemid 100052 has 2011, 2012 and 2013 so i want to force sql to show the 3 years for itemid 100051 too. – VSP Jun 14 '13 at 12:06
  • 1
    @ase69s - Well `A CROSS JOIN B` is equivalent to `A INNER JOIN B ON 1=1`. – Martin Smith Jun 14 '13 at 12:09
  • Thanks for the tip, but tried it and it generated too many rows in the case i added a third column, and if i did a group by with a min(thidcolumn) the column data would not be correct. Can you add the inner join solution on the awnser sample? – VSP Jun 14 '13 at 12:20
  • @ase69s - Edit your question to include something more representative of what you are actually trying to do. – Martin Smith Jun 14 '13 at 12:22