Loading ...
Sorry, an error occurred while loading the content.

Query a column twice?

Expand Messages
  • John Warner
    I have a column in a table with several different values. I can tell the values apart based on a second field; MYTABLE Amount: 12 14 23 45 34 43 44 Type SALES
    Message 1 of 21 , Jun 5, 2007
    • 0 Attachment
      I have a column in a table with several different values. I can tell the
      values apart based on a second field;

      MYTABLE
      Amount:
      12
      14
      23
      45
      34
      43
      44

      Type
      SALES
      COGS
      SALES
      COGS
      SALES
      SALES
      COGS

      InvNum
      1
      1
      4
      4
      7
      8
      8

      Note there is one more SALES then COGS. OK What would I'm stuck on is the
      self join. I can get SALES and COGS into two columns, but I lose the extra
      SALES because there isn't a matching COGS on the given Invoice
      What I need is

      Inv sales cogs
      1 12 14
      4 23 45
      7 34 NULL or 0 (ISNULL setting it to zero is fine or just plain NULL or
      blank
      8 43 44

      DBMS: SQL Server

      Thanks

      John Warner
    • Trevor North
      I think what you want is an left outer join (to make up rows in the left table (in this case cogs). Excuse the syntax if it s wrong as i m an oracle developer
      Message 2 of 21 , Jun 5, 2007
      • 0 Attachment
        I think what you want is an left outer join (to make up rows in the
        left table (in this case cogs). Excuse the syntax if it's wrong as
        i'm an oracle developer so dont have access to SQL Server but try
        this or if not, look up the correct syntax for left outer joins.

        select sales.invnum, sales.amount, cogs.amount
        from mytable as sales left outer join mytable as cogs
        on sales.invnum=cogs.invnum
        where sales.type='SALES'
        and cogs.type='COGS'

        Hope that helps.
        Trev


        --- In SQLQueriesNoCode@yahoogroups.com, "John Warner" <john@...>
        wrote:
        >
        > I have a column in a table with several different values. I can
        tell the
        > values apart based on a second field;
        >
        > MYTABLE
        > Amount:
        > 12
        > 14
        > 23
        > 45
        > 34
        > 43
        > 44
        >
        > Type
        > SALES
        > COGS
        > SALES
        > COGS
        > SALES
        > SALES
        > COGS
        >
        > InvNum
        > 1
        > 1
        > 4
        > 4
        > 7
        > 8
        > 8
        >
        > Note there is one more SALES then COGS. OK What would I'm stuck on
        is the
        > self join. I can get SALES and COGS into two columns, but I lose
        the extra
        > SALES because there isn't a matching COGS on the given Invoice
        > What I need is
        >
        > Inv sales cogs
        > 1 12 14
        > 4 23 45
        > 7 34 NULL or 0 (ISNULL setting it to zero is fine or just
        plain NULL or
        > blank
        > 8 43 44
        >
        > DBMS: SQL Server
        >
        > Thanks
        >
        > John Warner
        >
      • Databasedude.com
        Post the query code please? I think I have an idea, but won t know til I see what you re doing now :) Dude ... is the ... extra ... NULL or
        Message 3 of 21 , Jun 5, 2007
        • 0 Attachment
          Post the query code please? I think I have an idea, but won't know til
          I see what you're doing now :)

          Dude

          --- In SQLQueriesNoCode@yahoogroups.com, "John Warner" <john@...> wrote:
          >
          > I have a column in a table with several different values. I can tell the
          > values apart based on a second field;
          >
          > MYTABLE
          > Amount:
          > 12
          > 14
          > 23
          > 45
          > 34
          > 43
          > 44
          >
          > Type
          > SALES
          > COGS
          > SALES
          > COGS
          > SALES
          > SALES
          > COGS
          >
          > InvNum
          > 1
          > 1
          > 4
          > 4
          > 7
          > 8
          > 8
          >
          > Note there is one more SALES then COGS. OK What would I'm stuck on
          is the
          > self join. I can get SALES and COGS into two columns, but I lose the
          extra
          > SALES because there isn't a matching COGS on the given Invoice
          > What I need is
          >
          > Inv sales cogs
          > 1 12 14
          > 4 23 45
          > 7 34 NULL or 0 (ISNULL setting it to zero is fine or just plain
          NULL or
          > blank
          > 8 43 44
          >
          > DBMS: SQL Server
          >
          > Thanks
          >
          > John Warner
          >
        • Tim Mitchell
          John, The following query will give you what you need. Note that if you have more than just a few discrete values, you should probably consider using a pivot
          Message 4 of 21 , Jun 5, 2007
          • 0 Attachment
            John,



            The following query will give you what you need. Note that if you have
            more than just a few discrete values, you should probably consider using
            a pivot instead.



            select distinct InvNum

            , isnull((select amount from mytable where invnum = t.invnum and
            [type] = 'SALES'), 0)

            , isnull((select amount from mytable where invnum = t.invnum and
            [type] = 'COGS'), 0)

            from mytable t





            ________________________________

            From: SQLQueriesNoCode@yahoogroups.com
            [mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of John Warner
            Sent: Tuesday, June 05, 2007 9:12 AM
            To: SQLQueriesNoCode@yahoogroups.com
            Subject: [SQLQueriesNoCode] Query a column twice?



            I have a column in a table with several different values. I can tell the
            values apart based on a second field;

            MYTABLE
            Amount:
            12
            14
            23
            45
            34
            43
            44

            Type
            SALES
            COGS
            SALES
            COGS
            SALES
            SALES
            COGS

            InvNum
            1
            1
            4
            4
            7
            8
            8

            Note there is one more SALES then COGS. OK What would I'm stuck on is
            the
            self join. I can get SALES and COGS into two columns, but I lose the
            extra
            SALES because there isn't a matching COGS on the given Invoice
            What I need is

            Inv sales cogs
            1 12 14
            4 23 45
            7 34 NULL or 0 (ISNULL setting it to zero is fine or just plain NULL or
            blank
            8 43 44

            DBMS: SQL Server

            Thanks

            John Warner





            [Non-text portions of this message have been removed]
          • Michael Weiss
            Can you use a left outer (self) join John? Michael ________________________________ From: SQLQueriesNoCode@yahoogroups.com
            Message 5 of 21 , Jun 5, 2007
            • 0 Attachment
              Can you use a left outer (self) join John?

              Michael



              ________________________________

              From: SQLQueriesNoCode@yahoogroups.com
              [mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of John Warner
              Sent: Tuesday, June 05, 2007 7:12 AM
              To: SQLQueriesNoCode@yahoogroups.com
              Subject: [SQLQueriesNoCode] Query a column twice?



              I have a column in a table with several different values. I can tell the
              values apart based on a second field;

              MYTABLE
              Amount:
              12
              14
              23
              45
              34
              43
              44

              Type
              SALES
              COGS
              SALES
              COGS
              SALES
              SALES
              COGS

              InvNum
              1
              1
              4
              4
              7
              8
              8

              Note there is one more SALES then COGS. OK What would I'm stuck on is
              the
              self join. I can get SALES and COGS into two columns, but I lose the
              extra
              SALES because there isn't a matching COGS on the given Invoice
              What I need is

              Inv sales cogs
              1 12 14
              4 23 45
              7 34 NULL or 0 (ISNULL setting it to zero is fine or just plain NULL or
              blank
              8 43 44

              DBMS: SQL Server

              Thanks

              John Warner





              [Non-text portions of this message have been removed]
            • John Warner
              Yes, but the extra SALES row is not included. There can be other values in the Type column (should have said this in the original, my bad) so a WHERE clause is
              Message 6 of 21 , Jun 5, 2007
              • 0 Attachment
                Yes, but the extra SALES row is not included. There can be other values in
                the Type column (should have said this in the original, my bad) so a WHERE
                clause is needed to insure only SALES and COGS are examined. I'm sure the
                WHERE clause is what gets the extra SALES row to drop out.

                John Warner




                > -----Original Message-----
                > From: SQLQueriesNoCode@yahoogroups.com
                > [mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of Michael Weiss
                > Sent: Tuesday, June 05, 2007 12:07 PM
                > To: SQLQueriesNoCode@yahoogroups.com
                > Subject: RE: [SQLQueriesNoCode] Query a column twice?
                >
                >
                > Can you use a left outer (self) join John?
                >
                > Michael
                >
                >
                >
                > ________________________________
                >
                > From: SQLQueriesNoCode@yahoogroups.com
                > [mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of John Warner
                > Sent: Tuesday, June 05, 2007 7:12 AM
                > To: SQLQueriesNoCode@yahoogroups.com
                > Subject: [SQLQueriesNoCode] Query a column twice?
                >
                >
                >
                > I have a column in a table with several different values. I
                > can tell the values apart based on a second field;
                >
                > MYTABLE
                > Amount:
                > 12
                > 14
                > 23
                > 45
                > 34
                > 43
                > 44
                >
                > Type
                > SALES
                > COGS
                > SALES
                > COGS
                > SALES
                > SALES
                > COGS
                >
                > InvNum
                > 1
                > 1
                > 4
                > 4
                > 7
                > 8
                > 8
                >
                > Note there is one more SALES then COGS. OK What would I'm
                > stuck on is the self join. I can get SALES and COGS into two
                > columns, but I lose the extra SALES because there isn't a
                > matching COGS on the given Invoice What I need is
                >
                > Inv sales cogs
                > 1 12 14
                > 4 23 45
                > 7 34 NULL or 0 (ISNULL setting it to zero is fine or just
                > plain NULL or blank 8 43 44
                >
                > DBMS: SQL Server
                >
                > Thanks
                >
                > John Warner
                >
                >
                >
              • Michael MacDonald
                How about the following: SELECT inv.InvNum , sales.amount sales , cogs.amount cogs FROM ( SELECT DISTINCT InvNum FROM MYTABLE WHERE Type in ( SALES , COGS ) )
                Message 7 of 21 , Jun 5, 2007
                • 0 Attachment
                  How about the following:

                  SELECT
                  inv.InvNum
                  , sales.amount sales
                  , cogs.amount cogs
                  FROM
                  (
                  SELECT DISTINCT InvNum
                  FROM MYTABLE
                  WHERE Type in ('SALES','COGS')
                  ) inv
                  left join
                  (
                  SELECT InvNum,AMOUNT
                  FROM MYTABLE
                  WHERE Type='SALES'
                  ) sales on inv.invnum=sales.invnum
                  left join
                  (
                  SELECT InvNum,AMOUNT
                  FROM MYTABLE
                  WHERE Type='COGS'
                  ) cogs on inv.invnum=cogs.invnum

                  John Warner wrote:
                  > Yes, but the extra SALES row is not included. There can be other values in
                  > the Type column (should have said this in the original, my bad) so a WHERE
                  > clause is needed to insure only SALES and COGS are examined. I'm sure the
                  > WHERE clause is what gets the extra SALES row to drop out.
                  >
                  > John Warner
                  >
                  >
                  >
                  >
                  >> -----Original Message-----
                  >> From: SQLQueriesNoCode@yahoogroups.com
                  >> [mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of Michael Weiss
                  >> Sent: Tuesday, June 05, 2007 12:07 PM
                  >> To: SQLQueriesNoCode@yahoogroups.com
                  >> Subject: RE: [SQLQueriesNoCode] Query a column twice?
                  >>
                  >>
                  >> Can you use a left outer (self) join John?
                  >>
                  >> Michael
                  >>
                  >>
                  >>
                  >> ________________________________
                  >>
                  >> From: SQLQueriesNoCode@yahoogroups.com
                  >> [mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of John Warner
                  >> Sent: Tuesday, June 05, 2007 7:12 AM
                  >> To: SQLQueriesNoCode@yahoogroups.com
                  >> Subject: [SQLQueriesNoCode] Query a column twice?
                  >>
                  >>
                  >>
                  >> I have a column in a table with several different values. I
                  >> can tell the values apart based on a second field;
                  >>
                  >> MYTABLE
                  >> Amount:
                  >> 12
                  >> 14
                  >> 23
                  >> 45
                  >> 34
                  >> 43
                  >> 44
                  >>
                  >> Type
                  >> SALES
                  >> COGS
                  >> SALES
                  >> COGS
                  >> SALES
                  >> SALES
                  >> COGS
                  >>
                  >> InvNum
                  >> 1
                  >> 1
                  >> 4
                  >> 4
                  >> 7
                  >> 8
                  >> 8
                  >>
                  >> Note there is one more SALES then COGS. OK What would I'm
                  >> stuck on is the self join. I can get SALES and COGS into two
                  >> columns, but I lose the extra SALES because there isn't a
                  >> matching COGS on the given Invoice What I need is
                  >>
                  >> Inv sales cogs
                  >> 1 12 14
                  >> 4 23 45
                  >> 7 34 NULL or 0 (ISNULL setting it to zero is fine or just
                  >> plain NULL or blank 8 43 44
                  >>
                  >> DBMS: SQL Server
                  >>
                  >> Thanks
                  >>
                  >> John Warner
                • Trevor North
                  Not so sure of the SQL Server syntax as I m an Oracle developer/designer but this should be what you want - think it s a bit clearer just by giving the
                  Message 8 of 21 , Jun 5, 2007
                  • 0 Attachment
                    Not so sure of the SQL Server syntax as I'm an Oracle
                    developer/designer but this should be what you want - think it's a
                    bit clearer just by giving the statement.

                    select sales.invnum, sales.amount, cogs.amount
                    from mytable as sales left outer join mytable as cogs
                    on sales.invnum=cogs.invnum
                    where sales.type='SALES'
                    and cogs.type='COGS'

                    Regards
                    Trevor
                    Freelance Oracle Developer/Designer

                    --- In SQLQueriesNoCode@yahoogroups.com, "John Warner" <john@...>
                    wrote:
                    >
                    > Yes, but the extra SALES row is not included. There can be other
                    values in
                    > the Type column (should have said this in the original, my bad) so
                    a WHERE
                    > clause is needed to insure only SALES and COGS are examined. I'm
                    sure the
                    > WHERE clause is what gets the extra SALES row to drop out.
                    >
                    > John Warner
                    >
                    >
                    >
                    >
                    > > -----Original Message-----
                    > > From: SQLQueriesNoCode@yahoogroups.com
                    > > [mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of Michael
                    Weiss
                    > > Sent: Tuesday, June 05, 2007 12:07 PM
                    > > To: SQLQueriesNoCode@yahoogroups.com
                    > > Subject: RE: [SQLQueriesNoCode] Query a column twice?
                    > >
                    > >
                    > > Can you use a left outer (self) join John?
                    > >
                    > > Michael
                    > >
                    > >
                    > >
                    > > ________________________________
                    > >
                    > > From: SQLQueriesNoCode@yahoogroups.com
                    > > [mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of John
                    Warner
                    > > Sent: Tuesday, June 05, 2007 7:12 AM
                    > > To: SQLQueriesNoCode@yahoogroups.com
                    > > Subject: [SQLQueriesNoCode] Query a column twice?
                    > >
                    > >
                    > >
                    > > I have a column in a table with several different values. I
                    > > can tell the values apart based on a second field;
                    > >
                    > > MYTABLE
                    > > Amount:
                    > > 12
                    > > 14
                    > > 23
                    > > 45
                    > > 34
                    > > 43
                    > > 44
                    > >
                    > > Type
                    > > SALES
                    > > COGS
                    > > SALES
                    > > COGS
                    > > SALES
                    > > SALES
                    > > COGS
                    > >
                    > > InvNum
                    > > 1
                    > > 1
                    > > 4
                    > > 4
                    > > 7
                    > > 8
                    > > 8
                    > >
                    > > Note there is one more SALES then COGS. OK What would I'm
                    > > stuck on is the self join. I can get SALES and COGS into two
                    > > columns, but I lose the extra SALES because there isn't a
                    > > matching COGS on the given Invoice What I need is
                    > >
                    > > Inv sales cogs
                    > > 1 12 14
                    > > 4 23 45
                    > > 7 34 NULL or 0 (ISNULL setting it to zero is fine or just
                    > > plain NULL or blank 8 43 44
                    > >
                    > > DBMS: SQL Server
                    > >
                    > > Thanks
                    > >
                    > > John Warner
                    > >
                    > >
                    > >
                    >
                  • John Warner
                    Turns out it requires a LEFT JOIN (inner). Boy do I feel silly... John Warner
                    Message 9 of 21 , Jun 5, 2007
                    • 0 Attachment
                      Turns out it requires a LEFT JOIN (inner). Boy do I feel silly...

                      John Warner


                      > -----Original Message-----
                      > From: SQLQueriesNoCode@yahoogroups.com
                      > [mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of Michael Weiss
                      > Sent: Tuesday, June 05, 2007 12:07 PM
                      > To: SQLQueriesNoCode@yahoogroups.com
                      > Subject: RE: [SQLQueriesNoCode] Query a column twice?
                      >
                      >
                      > Can you use a left outer (self) join John?
                      >
                      > Michael
                      >
                      >
                      >
                      > ________________________________
                      >
                      > From: SQLQueriesNoCode@yahoogroups.com
                      > [mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of John Warner
                      > Sent: Tuesday, June 05, 2007 7:12 AM
                      > To: SQLQueriesNoCode@yahoogroups.com
                      > Subject: [SQLQueriesNoCode] Query a column twice?
                      >
                      >
                      >
                      > I have a column in a table with several different values. I
                      > can tell the values apart based on a second field;
                      >
                      > MYTABLE
                      > Amount:
                      > 12
                      > 14
                      > 23
                      > 45
                      > 34
                      > 43
                      > 44
                      >
                      > Type
                      > SALES
                      > COGS
                      > SALES
                      > COGS
                      > SALES
                      > SALES
                      > COGS
                      >
                      > InvNum
                      > 1
                      > 1
                      > 4
                      > 4
                      > 7
                      > 8
                      > 8
                      >
                      > Note there is one more SALES then COGS. OK What would I'm
                      > stuck on is the self join. I can get SALES and COGS into two
                      > columns, but I lose the extra SALES because there isn't a
                      > matching COGS on the given Invoice What I need is
                      >
                      > Inv sales cogs
                      > 1 12 14
                      > 4 23 45
                      > 7 34 NULL or 0 (ISNULL setting it to zero is fine or just
                      > plain NULL or blank 8 43 44
                      >
                      > DBMS: SQL Server
                      >
                      > Thanks
                      >
                      > John Warner
                      >
                      >
                      >
                      >
                      >
                      > [Non-text portions of this message have been removed]
                      >
                      >
                      >
                      >
                      > Yahoo! Groups Links
                      >
                      >
                      >
                      >
                      > No virus found in this incoming message.
                      > Checked by AVG Free Edition.
                      > Version: 7.5.472 / Virus Database: 269.8.9/832 - Release
                      > Date: 6/4/2007 6:43 PM
                      >
                      >
                    • John Warner
                      No the outer join doesn t work, tried it all morning yesterday, in my case it was Left Join (or Right depending on how you order the tables). I was like you
                      Message 10 of 21 , Jun 6, 2007
                      • 0 Attachment
                        No the outer join doesn't work, tried it all morning yesterday, in my
                        case it was Left Join (or Right depending on how you order the tables).
                        I was like you and was convinced it required an outer join. If you can
                        get a copy of the Guru's Guide to TSQL, he explains why this works. I
                        had to look it up myself.

                        Thanks for the suggestion though.

                        John Warner


                        > -----Original Message-----
                        > From: SQLQueriesNoCode@yahoogroups.com
                        > [mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of Trevor North
                        > Sent: Tuesday, June 05, 2007 10:38 AM
                        > To: SQLQueriesNoCode@yahoogroups.com
                        > Subject: [SQLQueriesNoCode] Re: Query a column twice?
                        >
                        >
                        > I think what you want is an left outer join (to make up rows in the
                        > left table (in this case cogs). Excuse the syntax if it's wrong as
                        > i'm an oracle developer so dont have access to SQL Server but try
                        > this or if not, look up the correct syntax for left outer joins.
                        >
                        > select sales.invnum, sales.amount, cogs.amount
                        > from mytable as sales left outer join mytable as cogs
                        > on sales.invnum=cogs.invnum
                        > where sales.type='SALES'
                        > and cogs.type='COGS'
                        >
                        > Hope that helps.
                        > Trev
                        >
                        >
                        > --- In SQLQueriesNoCode@yahoogroups.com, "John Warner" <john@...>
                        > wrote:
                        > >
                        > > I have a column in a table with several different values. I can
                        > tell the
                        > > values apart based on a second field;
                        > >
                        > > MYTABLE
                        > > Amount:
                        > > 12
                        > > 14
                        > > 23
                        > > 45
                        > > 34
                        > > 43
                        > > 44
                        > >
                        > > Type
                        > > SALES
                        > > COGS
                        > > SALES
                        > > COGS
                        > > SALES
                        > > SALES
                        > > COGS
                        > >
                        > > InvNum
                        > > 1
                        > > 1
                        > > 4
                        > > 4
                        > > 7
                        > > 8
                        > > 8
                        > >
                        > > Note there is one more SALES then COGS. OK What would I'm stuck on
                        > is the
                        > > self join. I can get SALES and COGS into two columns, but I lose
                        > the extra
                        > > SALES because there isn't a matching COGS on the given
                        > Invoice What I
                        > > need is
                        > >
                        > > Inv sales cogs
                        > > 1 12 14
                        > > 4 23 45
                        > > 7 34 NULL or 0 (ISNULL setting it to zero is fine or just
                        > plain NULL or
                        > > blank
                        > > 8 43 44
                        > >
                        > > DBMS: SQL Server
                        > >
                        > > Thanks
                        > >
                        > > John Warner
                      • John Warner
                        Turns out my error was using LEFT OUTER JOIN when I should have been using plain old LEFT JOIN. Thanks though for the offer of help. John Warner
                        Message 11 of 21 , Jun 6, 2007
                        • 0 Attachment
                          Turns out my error was using LEFT OUTER JOIN when I should have been
                          using plain old LEFT JOIN. Thanks though for the offer of help.

                          John Warner


                          > -----Original Message-----
                          > From: SQLQueriesNoCode@yahoogroups.com
                          > [mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of
                          > Databasedude.com
                          > Sent: Tuesday, June 05, 2007 11:49 AM
                          > To: SQLQueriesNoCode@yahoogroups.com
                          > Subject: [SQLQueriesNoCode] Re: Query a column twice?
                          >
                          >
                          > Post the query code please? I think I have an idea, but won't
                          > know til I see what you're doing now :)
                          >
                          > Dude
                          >
                          > --- In SQLQueriesNoCode@yahoogroups.com, "John Warner"
                          > <john@...> wrote:
                          > >
                          > > I have a column in a table with several different values. I
                          > can tell
                          > > the values apart based on a second field;
                          > >
                          > > MYTABLE
                          > > Amount:
                          > > 12
                          > > 14
                          > > 23
                          > > 45
                          > > 34
                          > > 43
                          > > 44
                          > >
                          > > Type
                          > > SALES
                          > > COGS
                          > > SALES
                          > > COGS
                          > > SALES
                          > > SALES
                          > > COGS
                          > >
                          > > InvNum
                          > > 1
                          > > 1
                          > > 4
                          > > 4
                          > > 7
                          > > 8
                          > > 8
                          > >
                          > > Note there is one more SALES then COGS. OK What would I'm stuck on
                          > is the
                          > > self join. I can get SALES and COGS into two columns, but I lose the
                          > extra
                          > > SALES because there isn't a matching COGS on the given
                          > Invoice What I
                          > > need is
                          > >
                          > > Inv sales cogs
                          > > 1 12 14
                          > > 4 23 45
                          > > 7 34 NULL or 0 (ISNULL setting it to zero is fine or just plain
                          > NULL or
                          > > blank
                          > > 8 43 44
                          > >
                          > > DBMS: SQL Server
                          > >
                          > > Thanks
                          > >
                          > > John Warner
                          > >
                        • John Warner
                          You hit the solution, left join instead of left outer join. Took me the better part of yesterday to get that right (and the help of a keen eyed friend. Thanks.
                          Message 12 of 21 , Jun 6, 2007
                          • 0 Attachment
                            You hit the solution, left join instead of left outer join. Took me the
                            better part of yesterday to get that right (and the help of a keen eyed
                            friend.

                            Thanks.

                            John Warner


                            > -----Original Message-----
                            > From: SQLQueriesNoCode@yahoogroups.com
                            > [mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of
                            > Michael MacDonald
                            > Sent: Tuesday, June 05, 2007 12:27 PM
                            > To: SQLQueriesNoCode@yahoogroups.com
                            > Subject: Re: [SQLQueriesNoCode] Query a column twice?
                            >
                            >
                            > How about the following:
                            >
                            > SELECT
                            > inv.InvNum
                            > , sales.amount sales
                            > , cogs.amount cogs
                            > FROM
                            > (
                            > SELECT DISTINCT InvNum
                            > FROM MYTABLE
                            > WHERE Type in ('SALES','COGS')
                            > ) inv
                            > left join
                            > (
                            > SELECT InvNum,AMOUNT
                            > FROM MYTABLE
                            > WHERE Type='SALES'
                            > ) sales on inv.invnum=sales.invnum
                            > left join
                            > (
                            > SELECT InvNum,AMOUNT
                            > FROM MYTABLE
                            > WHERE Type='COGS'
                            > ) cogs on inv.invnum=cogs.invnum
                            >
                            > John Warner wrote:
                            > > Yes, but the extra SALES row is not included. There can be other
                            > > values in the Type column (should have said this in the
                            > original, my
                            > > bad) so a WHERE clause is needed to insure only SALES and COGS are
                            > > examined. I'm sure the WHERE clause is what gets the extra
                            > SALES row
                            > > to drop out.
                            > >
                            > > John Warner
                            > >
                            > >
                            > >
                            > >
                            > >> -----Original Message-----
                            > >> From: SQLQueriesNoCode@yahoogroups.com
                            > >> [mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of
                            > Michael Weiss
                            > >> Sent: Tuesday, June 05, 2007 12:07 PM
                            > >> To: SQLQueriesNoCode@yahoogroups.com
                            > >> Subject: RE: [SQLQueriesNoCode] Query a column twice?
                            > >>
                            > >>
                            > >> Can you use a left outer (self) join John?
                            > >>
                            > >> Michael
                            > >>
                            > >>
                            > >>
                            > >> ________________________________
                            > >>
                            > >> From: SQLQueriesNoCode@yahoogroups.com
                            > >> [mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of John Warner
                            > >> Sent: Tuesday, June 05, 2007 7:12 AM
                            > >> To: SQLQueriesNoCode@yahoogroups.com
                            > >> Subject: [SQLQueriesNoCode] Query a column twice?
                            > >>
                            > >>
                            > >>
                            > >> I have a column in a table with several different values. I
                            > >> can tell the values apart based on a second field;
                            > >>
                            > >> MYTABLE
                            > >> Amount:
                            > >> 12
                            > >> 14
                            > >> 23
                            > >> 45
                            > >> 34
                            > >> 43
                            > >> 44
                            > >>
                            > >> Type
                            > >> SALES
                            > >> COGS
                            > >> SALES
                            > >> COGS
                            > >> SALES
                            > >> SALES
                            > >> COGS
                            > >>
                            > >> InvNum
                            > >> 1
                            > >> 1
                            > >> 4
                            > >> 4
                            > >> 7
                            > >> 8
                            > >> 8
                            > >>
                            > >> Note there is one more SALES then COGS. OK What would I'm
                            > >> stuck on is the self join. I can get SALES and COGS into two
                            > >> columns, but I lose the extra SALES because there isn't a
                            > >> matching COGS on the given Invoice What I need is
                            > >>
                            > >> Inv sales cogs
                            > >> 1 12 14
                            > >> 4 23 45
                            > >> 7 34 NULL or 0 (ISNULL setting it to zero is fine or just
                            > >> plain NULL or blank 8 43 44
                            > >>
                            > >> DBMS: SQL Server
                            > >>
                            > >> Thanks
                            > >>
                            > >> John Warner
                          • John Warner
                            Hmm, haven t tried that. Might work, will try it this afternoon when I m back in front of the data. Interesting approach. Thanks John Warner
                            Message 13 of 21 , Jun 6, 2007
                            • 0 Attachment
                              Hmm, haven't tried that. Might work, will try it this afternoon when I'm
                              back in front of the data. Interesting approach.

                              Thanks

                              John Warner


                              > -----Original Message-----
                              > From:
                              > [mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of Tim Mitchell
                              > Sent: Tuesday, June 05, 2007 11:53 AM
                              > To: SQLQueriesNoCode@yahoogroups.com
                              > Subject: RE: [SQLQueriesNoCode] Query a column twice?
                              >
                              >
                              > John,
                              >
                              >
                              >
                              > The following query will give you what you need. Note that
                              > if you have more than just a few discrete values, you should
                              > probably consider using a pivot instead.
                              >
                              >
                              >
                              > select distinct InvNum
                              >
                              > , isnull((select amount from mytable where invnum =
                              > t.invnum and [type] = 'SALES'), 0)
                              >
                              > , isnull((select amount from mytable where invnum =
                              > t.invnum and [type] = 'COGS'), 0)
                              >
                              > from mytable t
                              >
                              >
                              >
                              >
                              >
                              > ________________________________
                              >
                              > From: SQLQueriesNoCode@yahoogroups.com
                              > [mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of John Warner
                              > Sent: Tuesday, June 05, 2007 9:12 AM
                              > To: SQLQueriesNoCode@yahoogroups.com
                              > Subject: [SQLQueriesNoCode] Query a column twice?
                              >
                              >
                              >
                              > I have a column in a table with several different values. I
                              > can tell the values apart based on a second field;
                              >
                              > MYTABLE
                              > Amount:
                              > 12
                              > 14
                              > 23
                              > 45
                              > 34
                              > 43
                              > 44
                              >
                              > Type
                              > SALES
                              > COGS
                              > SALES
                              > COGS
                              > SALES
                              > SALES
                              > COGS
                              >
                              > InvNum
                              > 1
                              > 1
                              > 4
                              > 4
                              > 7
                              > 8
                              > 8
                              >
                              > Note there is one more SALES then COGS. OK What would I'm
                              > stuck on is the self join. I can get SALES and COGS into two
                              > columns, but I lose the extra SALES because there isn't a
                              > matching COGS on the given Invoice What I need is
                              >
                              > Inv sales cogs
                              > 1 12 14
                              > 4 23 45
                              > 7 34 NULL or 0 (ISNULL setting it to zero is fine or just
                              > plain NULL or blank 8 43 44
                              >
                              > DBMS: SQL Server
                              >
                              > Thanks
                              >
                              > John Warner
                              >
                              >
                            Your message has been successfully submitted and would be delivered to recipients shortly.