CODEWITHSUNDEEP
t-sqlouter-join
VSP
VSP

Reputation: 2393

SQL Query correct way of doing a right outer join?

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..

Upvotes: 0

Views: 53

Answers (1)

Martin Smith
Martin Smith

Reputation: 452978

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

Upvotes: 1

Related Questions