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

RE: [SQLQueriesNoCode] sql server not exits or access denied - plz help

Expand Messages
  • John Warner
    Then your box is not listening. John Warner ... From: SQLQueriesNoCode@yahoogroups.com [mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of kamran Sorathia
    Message 1 of 21 , Sep 28, 2005
      Message
      Then your box is not listening.
       
       

      John Warner

      -----Original Message-----
      From: SQLQueriesNoCode@yahoogroups.com [mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of kamran Sorathia
      Sent: Tuesday, September 27, 2005 9:53 PM
      To: SQLQueriesNoCode@yahoogroups.com
      Subject: RE: [SQLQueriesNoCode] sql server not exits or access denied - plz help

      hez getting request timeout ..
       
      i already try this 202.63.197.63 1444
       
      but nathing works ..

      John Warner <john@...> wrote:
      Great, firewall off, now, can he ping your server? Is he using ports 1433/1434?
       
       

      John Warner

      -----Original Message-----
      From: SQLQueriesNoCode@yahoogroups.com [mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of kamran Sorathia
      Sent: Tuesday, September 27, 2005 4:55 AM
      To: SQLQueriesNoCode@yahoogroups.com
      Subject: RE: [SQLQueriesNoCode] sql server not exits or access denied - plz help

      Sir John i already disabled the my XP firewall ..

      John Warner <john@...> wrote:
      Also be sure you are using port 1433 to send to SQL Server and listen on 1434 for replies. Can he ping your server? How many hops between you using tracert?
       

      John Warner

      -----Original Message-----
      From: SQLQueriesNoCode@yahoogroups.com [mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of Tim Mitchell
      Sent: Monday, September 26, 2005 5:34 PM
      To: SQLQueriesNoCode@yahoogroups.com
      Subject: Re: [SQLQueriesNoCode] sql server not exits or access denied - plz help

      Make sure your authentication is set to "Mixed Mode" instead of "Windows Authentication Only".


      kamran Sorathia wrote:
      Hello every one
       
      I have my own network 2 pc only & have dedicated connection (fixed ip)
       
      i installed the Sql server 2000 on my server which have fixed ip & was using the sql server on my other network (development) pc & every thing was working fine
       
      the prb is my frdz want to access the my sql server 2000..my frd is on the other network  
       
      i gave him my server IP let say 202.63.199.90 7 uid=sa; pwd=somepassword
       
      but he cant able to connect to my sql server .. getting error "sql server not exits or access denied"
       
      i m using sql server 2000 personal Edition
      winxp sp2 (firewall is disabled)
       
      is there any thing related to personal edition ?
       
      thanx any
       
      Kamran Sorathia


      Yahoo! for Good
      Click here to donate to the Hurricane Katrina relief effort.


      --

      Tim Mitchell
      MCDBA MCSE
      Systems Analyst
      Sherman ISD
      903.891.6400 x289
      tmitchell@...

      __________________________________________________
      Do You Yahoo!?
      Tired of spam? Yahoo! Mail has the best spam protection around
      http://mail.yahoo.com

      __________________________________________________
      Do You Yahoo!?
      Tired of spam? Yahoo! Mail has the best spam protection around
      http://mail.yahoo.com

    • 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 2 of 21 , Jun 5 7:12 AM
        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 3 of 21 , Jun 5 7:37 AM
          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 4 of 21 , Jun 5 8:49 AM
            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 5 of 21 , Jun 5 8:52 AM
              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 6 of 21 , Jun 5 9:06 AM
                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 7 of 21 , Jun 5 9:13 AM
                  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 8 of 21 , Jun 5 9:26 AM
                    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 9 of 21 , Jun 5 2:06 PM
                      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 10 of 21 , Jun 5 3:49 PM
                        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 11 of 21 , Jun 6 1:29 AM
                          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 12 of 21 , Jun 6 1:31 AM
                            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 13 of 21 , Jun 6 1:34 AM
                              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 14 of 21 , Jun 6 1:36 AM
                                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.