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

Re: [SQLQueriesNoCode] sql query

Expand Messages
  • Karl Schaubmair
    Hi, just like this: select top 3 * from table Or did you mean another thing? Charlie
    Message 1 of 22 , May 8, 2005
    • 0 Attachment
      Hi,

      just like this:

      select top 3 * from table

      Or did you mean another thing?

      Charlie


      dln_prasad wrote:
      hi frnds,
      
       how to select top 3 records of a table
      
       prasad
      
      
      
      
      
      
       
      Yahoo! Groups Links
      
      <*> To visit your group on the web, go to:
          http://groups.yahoo.com/group/SQLQueriesNoCode/
      
      <*> To unsubscribe from this group, send an email to:
          SQLQueriesNoCode-unsubscribe@yahoogroups.com
      
      <*> Your use of Yahoo! Groups is subject to:
          http://docs.yahoo.com/info/terms/
       
      
      
      
      
        

    • dln_prasad
      is it possible to retrieve records based on pattern matching...
      Message 2 of 22 , May 9, 2005
      • 0 Attachment
        is it possible to retrieve records based on pattern matching...
      • Damhuis Anton
        Hi The closest I know of pattern matching in SQL is SOUNDEX, but this is only support in English. I suppose SQL 2005? (with dot Net CLR) should support it in
        Message 3 of 22 , May 9, 2005
        • 0 Attachment
          Hi

          The closest I know of "pattern matching" in SQL is SOUNDEX, but this is only support in English.

          I suppose SQL 2005? (with dot Net CLR) should support it in the future.

          Am interested in what the others have to say.

          Regards
          Anton

          -----Original Message-----
          From: SQLQueriesNoCode@yahoogroups.com
          [mailto:SQLQueriesNoCode@yahoogroups.com]On Behalf Of dln_prasad
          Sent: 09 May 2005 10:59
          To: SQLQueriesNoCode@yahoogroups.com
          Subject: [SQLQueriesNoCode] sql query


          is it possible to retrieve records based on pattern matching...

          Confidentiality Warning
          =======================
          The contents of this e-mail and any accompanying documentation
          are confidential and any use thereof, in what ever form, by anyone
          other than the addressee is strictly prohibited.
        • Michael Gerholdt
          I believe Oracle 10g does/will support RegExp. (We re still on 9i ...) ... From: SQLQueriesNoCode@yahoogroups.com [mailto:SQLQueriesNoCode@yahoogroups.com] On
          Message 4 of 22 , May 9, 2005
          • 0 Attachment
            I believe Oracle 10g does/will support RegExp. (We're still on 9i ...)

            -----Original Message-----
            From: SQLQueriesNoCode@yahoogroups.com
            [mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of dln_prasad
            Sent: Monday, May 09, 2005 4:59 AM
            To: SQLQueriesNoCode@yahoogroups.com
            Subject: [SQLQueriesNoCode] sql query

            is it possible to retrieve records based on pattern matching...







            Yahoo! Groups Links
          • faisal dar
            Simple Select Top 3 (Field name) from (Table name) ok, bye DAR ... __________________________________ Yahoo! Mail Mobile Take Yahoo! Mail with you! Check
            Message 5 of 22 , May 9, 2005
            • 0 Attachment
              Simple

              "Select Top 3 (Field name) from (Table name) "

              ok,

              bye

              DAR


              --- dln_prasad <dln_prasad@...> wrote:
              > hi frnds,
              >
              > how to select top 3 records of a table
              >
              > prasad
              >
              >
              >
              >
              >



              __________________________________
              Yahoo! Mail Mobile
              Take Yahoo! Mail with you! Check email on your mobile phone.
              http://mobile.yahoo.com/learn/mail
            • Marcel Buijs
              Dear @, I ve a problem, My SQL-Log (LDF) file is about 1,7 Gb big. (Drive is 2 Gb big, that will be a major problem) The MDF file is about 14 Mb big Just a few
              Message 6 of 22 , May 10, 2005
              • 0 Attachment
                Dear @,

                I've a problem,

                My SQL-Log (LDF) file is about 1,7 Gb big. (Drive is 2 Gb big, that will be
                a major problem)
                The MDF file is about 14 Mb big

                Just a few days ago, the logfile was 110 Mb.
                With the daily backup, it wouln't shrink

                Hou can I shrink it? When I use the shrink task, it reccomend it that I can
                shrink it to 110 MB.

                When I scedule it to every half hour, it didn't get smaller..

                Thanks in advance,

                Marcel
              • John Warner
                http://support.microsoft.com/default.aspx?scid=kb;EN-US;272318 John Warner
                Message 7 of 22 , May 10, 2005
                • 0 Attachment
                  http://support.microsoft.com/default.aspx?scid=kb;EN-US;272318

                  John Warner




                  > -----Original Message-----
                  > From: SQLQueriesNoCode@yahoogroups.com
                  > [mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of Marcel Buijs
                  > Sent: Tuesday, May 10, 2005 3:19 AM
                  > To: SQLQueriesNoCode@yahoogroups.com
                  > Subject: [SQLQueriesNoCode] SQL problem / file size
                  >
                  >
                  > Dear @,
                  >
                  > I've a problem,
                  >
                  > My SQL-Log (LDF) file is about 1,7 Gb big. (Drive is 2 Gb
                  > big, that will be a major problem) The MDF file is about 14 Mb big
                  >
                  > Just a few days ago, the logfile was 110 Mb.
                  > With the daily backup, it wouln't shrink
                  >
                  > Hou can I shrink it? When I use the shrink task, it reccomend
                  > it that I can shrink it to 110 MB.
                  >
                  > When I scedule it to every half hour, it didn't get smaller..
                  >
                  > Thanks in advance,
                  >
                  > Marcel
                  >
                  >
                  >
                  >
                  >
                  >
                  > Yahoo! Groups Links
                  >
                  >
                  >
                  >
                  >
                  >
                  >
                • manisha kadam
                  hi friends i need u r help for sql query my table structure is like for master table orderno, sheetno, custname for details table orderno, itemno, design,
                  Message 8 of 22 , Oct 10, 2006
                  • 0 Attachment
                    hi friends
                    i need u r help for sql query
                    my table structure is like
                    for master table
                    orderno, sheetno, custname
                    for details table
                    orderno, itemno, design, price, qty, deleted

                    i want to fetch all details from master and details table as per orderno
                    but from details table only those records whose deleted = '0'

                    if there is no record in details table only master data should show
                    so i use left join
                    i write
                    select o.orderno, o.sheetno, o.custname,
                    od.itemno, od.design, od.price, od.qty, od.deleted
                    from orders o join orderdetails od
                    left join on o.orderno = od.orderno
                    where o.orderno = @Orderno
                    this fetch records as per demand
                    but i want from details table only those record whose deleted = '0'
                    so i add condition
                    where o.orderno = @Orderno and od.deleted = '0'

                    now if in details table there is record it shows me properly
                    but if in details table there is no record it doesn't show any thing

                    but i want in this case data from master table should get

                    i hope i am able to explain my problem

                    pls give me any clue about this problem

                    thanks


                    ---------------------------------
                    How low will we go? Check out Yahoo! Messenger’s low PC-to-Phone call rates.

                    [Non-text portions of this message have been removed]
                  • Stephen Russell
                    ... Close. select o.orderno , o.sheetno , o.custname , od.itemno , od.design , od.price , od.qty --, od.deleted from orders o LEFT join orderdetails od on
                    Message 9 of 22 , Oct 10, 2006
                    • 0 Attachment
                      manisha kadam <mailto:l.manisha@...> wrote:
                      > hi friends
                      > i need u r help for sql query
                      > my table structure is like
                      > for master table
                      > orderno, sheetno, custname
                      > for details table
                      > orderno, itemno, design, price, qty, deleted
                      >
                      > i want to fetch all details from master and details table as per
                      > orderno but from details table only those records whose deleted = '0'
                      >
                      > if there is no record in details table only master data should show
                      > so i use left join i write
                      > select o.orderno, o.sheetno, o.custname,
                      > od.itemno, od.design, od.price, od.qty, od.deleted
                      > from orders o join orderdetails od
                      > left join on o.orderno = od.orderno
                      > where o.orderno = @Orderno
                      > this fetch records as per demand
                      > but i want from details table only those record whose deleted = '0'
                      > so i add condition
                      > where o.orderno = @Orderno and od.deleted = '0'
                      >
                      > now if in details table there is record it shows me properly but if
                      > in details table there is no record it doesn't show any thing
                      >
                      > but i want in this case data from master table should get
                      >
                      > i hope i am able to explain my problem
                      >
                      > pls give me any clue about this problem

                      Close.

                      select o.orderno
                      , o.sheetno
                      , o.custname
                      , od.itemno
                      , od.design
                      , od.price
                      , od.qty
                      --, od.deleted
                      from orders o
                      LEFT join orderdetails od
                      on o.orderno = od.orderno
                      where o.orderno = @Orderno
                      and od.deleted = '0'


                      You don't need the deleted flag because your forcing it to be '0'

                      Notice how the Left join is now set properly?


                      Stephen Russell
                      DBA / .Net Developer

                      Memphis TN 38115
                      901.246-0159

                      "Our scientific power has outrun our spiritual power. We have guided
                      missiles and misguided men." Dr. Martin Luther King Jr.

                      http://spaces.msn.com/members/srussell/

                      --
                      No virus found in this outgoing message.
                      Checked by AVG Free Edition.
                      Version: 7.1.408 / Virus Database: 268.13.1/470 - Release Date: 10/10/2006
                    • Arnie Rowland
                      Anytime you add a column from the second table of a LEFT JOIN to the WHERE clause, you effectively convert the JOIN into an EQUI-JOIN and it is NO longer a
                      Message 10 of 22 , Oct 10, 2006
                      • 0 Attachment
                        Anytime you add a column from the second table of a LEFT JOIN to the WHERE
                        clause, you effectively convert the JOIN into an EQUI-JOIN and it is NO
                        longer a LEFT JOIN.

                        Try instead putting the filter in the ON clause of the JOIN, something like:

                        select
                        o.OrderNo
                        , o.SheetNo
                        , o.CustName
                        , od.ItemNo
                        , od.Design
                        , od.Price
                        , od.Qty
                        , od.Deleted
                        FROM Orders o
                        LEFT JOIN OrderDetails od
                        ON ( o.OrderNo = od.OrderNo
                        AND od.Deleted = '0'
                        )
                        WHERE o.OrderNo = @OrderNo


                        - Arnie Rowland

                        "I am a great believer in luck, and I find that the harder I work, the more
                        I have of it." - Thomas Jefferson (1743-1826)


                        -----Original Message-----
                        From: SQLQueriesNoCode@yahoogroups.com
                        [mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of manisha kadam
                        Sent: Tuesday, October 10, 2006 7:15 AM
                        To: Arnie
                        Subject: [SQLQueriesNoCode] sql query

                        hi friends
                        i need u r help for sql query
                        my table structure is like
                        for master table
                        orderno, sheetno, custname
                        for details table
                        orderno, itemno, design, price, qty, deleted

                        i want to fetch all details from master and details table as per orderno
                        but from details table only those records whose deleted = '0'

                        if there is no record in details table only master data should show
                        so i use left join
                        i write
                        select o.orderno, o.sheetno, o.custname,
                        od.itemno, od.design, od.price, od.qty, od.deleted
                        from orders o join orderdetails od
                        left join on o.orderno = od.orderno
                        where o.orderno = @Orderno
                        this fetch records as per demand
                        but i want from details table only those record whose deleted = '0'
                        so i add condition
                        where o.orderno = @Orderno and od.deleted = '0'

                        now if in details table there is record it shows me properly
                        but if in details table there is no record it doesn't show any thing

                        but i want in this case data from master table should get

                        i hope i am able to explain my problem

                        pls give me any clue about this problem

                        thanks


                        ---------------------------------
                        How low will we go? Check out Yahoo! Messenger's low PC-to-Phone call
                        rates.

                        [Non-text portions of this message have been removed]






                        Yahoo! Groups Links












                        Disclaimer - October 10, 2006
                        This email and any files transmitted with it are confidential and intended solely for SQLQueriesNoCode@yahoogroups.com. If you are not the named addressee you should not disseminate, distribute, copy or alter this email. Any views or opinions presented in this email are solely those of the author and might not represent those of Westwood Consulting, Inc. Warning: Although Westwood Consulting, Inc has taken reasonable precautions to ensure no viruses are present in this email, the company cannot accept responsibility for any loss or damage arising from the use of this email or attachments.
                        This disclaimer was added by Policy Patrol: http://www.policypatrol.com/


                        [Non-text portions of this message have been removed]
                      • Stephen Russell
                        ... Arnie is correct here, as long as your index includes BOTH COLUMNS orderNo and deleted. That is why I avoid this in starter T-SQL explanations. But
                        Message 11 of 22 , Oct 10, 2006
                        • 0 Attachment
                          Arnie Rowland <mailto:arnie@...> wrote:
                          > Anytime you add a column from the second table of a LEFT JOIN to the
                          > WHERE clause, you effectively convert the JOIN into an EQUI-JOIN and
                          > it is NO longer a LEFT JOIN.
                          >
                          > Try instead putting the filter in the ON clause of the JOIN,
                          > something like:
                          >
                          > select
                          > o.OrderNo
                          > , o.SheetNo
                          > , o.CustName
                          > , od.ItemNo
                          > , od.Design
                          > , od.Price
                          > , od.Qty
                          > , od.Deleted
                          > FROM Orders o
                          > LEFT JOIN OrderDetails od
                          > ON ( o.OrderNo = od.OrderNo
                          > AND od.Deleted = '0'
                          > )
                          > WHERE o.OrderNo = @OrderNo

                          Arnie is correct here, as long as your index includes BOTH COLUMNS orderNo
                          and deleted. That is why I avoid this in starter T-SQL explanations. But
                          having your exclusion take place in round 1, the join, is much better then
                          the where clause.





                          Stephen Russell
                          DBA / .Net Developer

                          Memphis TN 38115
                          901.246-0159

                          "Our scientific power has outrun our spiritual power. We have guided
                          missiles and misguided men." Dr. Martin Luther King Jr.

                          http://spaces.msn.com/members/srussell/

                          --
                          No virus found in this outgoing message.
                          Checked by AVG Free Edition.
                          Version: 7.1.408 / Virus Database: 268.13.1/470 - Release Date: 10/10/2006
                        • Arnie Rowland
                          ... From: SQLQueriesNoCode@yahoogroups.com Sent: Tuesday, October 10, 2006 12:25 PM To: Arnie Subject: RE: [SQLQueriesNoCode] sql query ... Arnie is correct
                          Message 12 of 22 , Oct 10, 2006
                          • 0 Attachment
                            -----Original Message-----
                            From: SQLQueriesNoCode@yahoogroups.com
                            Sent: Tuesday, October 10, 2006 12:25 PM
                            To: Arnie
                            Subject: RE: [SQLQueriesNoCode] sql query

                            ...

                            Arnie is correct here, as long as your index includes BOTH COLUMNS
                            orderNo and deleted. That is why I avoid this in starter T-SQL
                            explanations. But having your exclusion take place in round 1, the
                            join, is much better then the where clause.


                            Steven,

                            That is not 'totally' correct.

                            For the JOIN to work well, only OrderNo MUST be indexed in both tables.
                            Adding od.Deleted to the index will have no significant value since it is
                            most likely a binary value (probably 0 and 1). -And that is just my WAG.

                            About the filter in the WHERE clause. Adding od.Deleted to the WHERE clause
                            will require all rows in the final resultset to have a od.Deleted value = 0
                            -thereby negating the desire to return rows from Orders that do NOT have
                            matching rows in OrderDetails. (Any time a column from the second table is
                            in a WHERE clause, an OUTER JOIN is automatically evaluated as a EQUI-JOIN
                            -and the resultset will NOT be from a LEFT or RIGHT JOIN.

                            The ONLY way to accomplish the goal (using a JOIN, that is), listing ALL
                            rows from the Orders table filtered by the @OrderNo parameter, AND only
                            those matching rows from OrderDetails with a Deleted value of '0' is to
                            filter OrderDetails BEFORE the JOIN.

                            I've added some example code to illustrate the issue.

                            - Arnie Rowland

                            "I am a great believer in luck, and I find that the harder I work, the more
                            I have of it." - Thomas Jefferson (1743-1826)


                            SET NOCOUNT ON

                            DECLARE @Orders table
                            ( OrderID int IDENTITY,
                            OrderDate datetime
                            )

                            DECLARE @OrderDetails table
                            ( OrderDetailID int IDENTITY,
                            OrderId int,
                            Item varchar(20)
                            )

                            INSERT INTO @Orders VALUES ( '9/1/2006' )
                            INSERT INTO @Orders VALUES ( '9/2/2006' )
                            INSERT INTO @Orders VALUES ( '9/3/2006' )
                            INSERT INTO @Orders VALUES ( '9/4/2006' )
                            INSERT INTO @Orders VALUES ( '9/5/2006' )

                            INSERT INTO @OrderDetails VALUES ( 1, 'Test Order 1' )
                            INSERT INTO @OrderDetails VALUES ( 3, 'Test Order 3' )
                            INSERT INTO @OrderDetails VALUES ( 5, 'Test Order 5' )

                            -- EQUI-JOIN
                            SELECT
                            O.OrderID,
                            o.OrderDate,
                            od.Item
                            FROM @Orders o
                            JOIN @OrderDetails od
                            ON o.OrderID = od.OrderID

                            -- LEFT JOIN with second table in WHERE clause
                            SELECT
                            O.OrderID,
                            o.OrderDate,
                            od.Item
                            FROM @Orders o
                            LEFT JOIN @OrderDetails od
                            ON o.OrderID = od.OrderID
                            WHERE od.Item LIKE 'Test%'

                            --LEFT JOIN with second table filtered before JOIN
                            SELECT
                            O.OrderID,
                            o.OrderDate,
                            od.Item
                            FROM @Orders o
                            LEFT JOIN @OrderDetails od
                            ON ( o.OrderID = od.OrderID
                            AND od.Item LIKE 'Test%'
                            )


                            Disclaimer - October 10, 2006
                            This email and any files transmitted with it are confidential and intended solely for SQLQueriesNoCode@yahoogroups.com. If you are not the named addressee you should not disseminate, distribute, copy or alter this email. Any views or opinions presented in this email are solely those of the author and might not represent those of Westwood Consulting, Inc. Warning: Although Westwood Consulting, Inc has taken reasonable precautions to ensure no viruses are present in this email, the company cannot accept responsibility for any loss or damage arising from the use of this email or attachments.
                            This disclaimer was added by Policy Patrol: http://www.policypatrol.com/


                            [Non-text portions of this message have been removed]
                          • Stephen Russell
                            ... Actually your impact will create the dreaded table scan if you only have a single on OrderID in Details. With only a few to a few hunderd rows, no biggie,
                            Message 13 of 22 , Oct 10, 2006
                            • 0 Attachment
                              Arnie Rowland <mailto:arnie@...> wrote:
                              > -----Original Message-----
                              > From: SQLQueriesNoCode@yahoogroups.com
                              > Sent: Tuesday, October 10, 2006 12:25 PM
                              > To: Arnie
                              > Subject: RE: [SQLQueriesNoCode] sql query
                              >
                              > ...
                              >
                              > Arnie is correct here, as long as your index includes BOTH COLUMNS
                              > orderNo and deleted. That is why I avoid this in starter T-SQL
                              > explanations. But having your exclusion take place in round 1, the
                              > join, is much better then the where clause.
                              >
                              >
                              > Steven,
                              >
                              > That is not 'totally' correct.
                              >
                              > For the JOIN to work well, only OrderNo MUST be indexed in both
                              > tables.
                              > Adding od.Deleted to the index will have no significant value since
                              > it is most likely a binary value (probably 0 and 1). -And that is
                              > just my WAG.

                              Actually your impact will create the dreaded table scan if you only have a
                              single on OrderID in Details. With only a few to a few hunderd rows, no
                              biggie, but put a massive couple of mill rows out there and you have job
                              security :)

                              Your breakdown of a query
                              1- join condition
                              2- where clause
                              3- group by
                              4- having
                              5- field list.

                              So the index that kicks butt here is :
                              OrderID, deleted



                              Stephen Russell
                              DBA / .Net Developer

                              Memphis TN 38115
                              901.246-0159

                              "Our scientific power has outrun our spiritual power. We have guided
                              missiles and misguided men." Dr. Martin Luther King Jr.

                              http://spaces.msn.com/members/srussell/

                              --
                              No virus found in this outgoing message.
                              Checked by AVG Free Edition.
                              Version: 7.1.408 / Virus Database: 268.13.1/470 - Release Date: 10/10/2006
                            • Arnie Rowland
                              Steven, I clearly disagree with the idea that filtering a JOIN and not having indexing on all elements of the filter will cause a table scan. If the OrderID
                              Message 14 of 22 , Oct 10, 2006
                              • 0 Attachment
                                Steven,

                                I clearly disagree with the idea that filtering a JOIN and not having
                                indexing on all elements of the filter will cause a table scan.

                                If the OrderID distribution is significantly sparse -as it 'should' be, I
                                believe that the query processor will first gather the index key values and
                                then seek only those rows from the table. (And in most situations, the user
                                has probably defined the OrderID as PK and not changed the clustered index
                                default so an 'index' seek of table rows will be done.

                                Concerning the idea of filtering in the JOIN, and whether or not [Deleted]
                                should be included in the indexing, I think that if you will examine the
                                Execution Plans, you will notice that WITHOUT any inclusion of the [Deleted]
                                column in the table indexing, the query processor will still do an
                                'Clustered Index Seek' or 'Index Seek' -NOT table scans. (And with such a
                                small set of data, if the QP considered a table scan to be efficient, I
                                believe that it would have chosen a table scan -but it does not.)

                                I've prepared some illustrative code below.

                                And, of course, you can clearly see that the results of the two queries are
                                very different.

                                - Arnie Rowland

                                "I am a great believer in luck, and I find that the harder I work, the more
                                I have of it." - Thomas Jefferson (1743-1826)


                                USE Northwind
                                GO

                                ALTER TABLE [Order Details]
                                ADD [Deleted] int

                                -- Approximate 5% (value = 1)
                                UPDATE [Order Details]
                                SET [Deleted] = CASE (OrderID % 19)
                                WHEN 1 THEN 1
                                ELSE 0
                                END

                                -- Check the data
                                SELECT
                                'Count 0' = CASE WHEN [Deleted] = 0 THEN sum(1) ELSE 0 END,
                                'Count 1' = CASE WHEN [Deleted] = 1 THEN sum(1) ELSE 0 END
                                FROM [Order Details]
                                GROUP BY [Deleted]

                                -- LEFT JOIN with second table in WHERE clause
                                SELECT
                                O.OrderID,
                                o.OrderDate,
                                od.ProductID
                                FROM Orders o
                                LEFT JOIN [Order Details] od
                                ON o.OrderID = od.OrderID
                                WHERE ( o.OrderID BETWEEN 10300 AND 10325
                                AND od.ProductID BETWEEN 10 AND 20
                                )

                                --LEFT JOIN with second table filtered before JOIN
                                SELECT
                                O.OrderID,
                                o.OrderDate,
                                od.ProductID
                                FROM Orders o
                                LEFT JOIN [Order Details] od
                                ON ( o.OrderID = od.OrderID
                                AND od.ProductID BETWEEN 10 AND 20
                                )
                                WHERE o.OrderID BETWEEN 10300 AND 10325

                                -- Clean Up
                                ALTER TABLE [Order Details]
                                DROP COLUMN [Deleted]


                                Disclaimer - October 10, 2006
                                This email and any files transmitted with it are confidential and intended solely for SQLQueriesNoCode@yahoogroups.com. If you are not the named addressee you should not disseminate, distribute, copy or alter this email. Any views or opinions presented in this email are solely those of the author and might not represent those of Westwood Consulting, Inc. Warning: Although Westwood Consulting, Inc has taken reasonable precautions to ensure no viruses are present in this email, the company cannot accept responsibility for any loss or damage arising from the use of this email or attachments.
                                This disclaimer was added by Policy Patrol: http://www.policypatrol.com/


                                [Non-text portions of this message have been removed]
                              • Arnie Rowland
                                And let me add for clarity s sake. I most certainly agree with you that in some situations, it may be more efficient to include [Deleted] in the index.
                                Message 15 of 22 , Oct 10, 2006
                                • 0 Attachment
                                  And let me add for clarity's sake.

                                  I most certainly agree with you that in some situations, it may be more
                                  efficient to include [Deleted] in the index. However, I imagine that the
                                  [Deleted] flag on the row may be changed at least once, and that will have a
                                  negative impact on index maintenance -whereas, I doubt that the OrderNo will
                                  change on a row. The more often the flags are flipped, the greater the index
                                  maintenance costs.

                                  One 'should' always evaluate the alternatives and select the best solution
                                  for the query.

                                  I just didn't agree that filtering in the JOIN criteria would cause a table
                                  scan.

                                  Regards,

                                  - Arnie Rowland

                                  "I am a great believer in luck, and I find that the harder I work, the more
                                  I have of it." - Thomas Jefferson (1743-1826)




                                  Disclaimer - October 10, 2006
                                  This email and any files transmitted with it are confidential and intended solely for SQLQueriesNoCode@yahoogroups.com. If you are not the named addressee you should not disseminate, distribute, copy or alter this email. Any views or opinions presented in this email are solely those of the author and might not represent those of Westwood Consulting, Inc. Warning: Although Westwood Consulting, Inc has taken reasonable precautions to ensure no viruses are present in this email, the company cannot accept responsibility for any loss or damage arising from the use of this email or attachments.
                                  This disclaimer was added by Policy Patrol: http://www.policypatrol.com/


                                  [Non-text portions of this message have been removed]
                                • shiv kumar
                                  you should use outer join (+) to display record as per your need. skg ... _________________________________________________________________ Discover. Explore.
                                  Message 16 of 22 , Oct 10, 2006
                                  • 0 Attachment
                                    you should use outer join (+) to display record as per your need.
                                    skg


                                    >From: manisha kadam <l.manisha@...>
                                    >Reply-To: SQLQueriesNoCode@yahoogroups.com
                                    >To: SQL <SQLQueriesNoCode@yahoogroups.com>
                                    >Subject: [SQLQueriesNoCode] sql query
                                    >Date: Tue, 10 Oct 2006 00:36:24 -0700 (PDT)
                                    >
                                    >hi friends
                                    > i need u r help for sql query
                                    > my table structure is like
                                    > for master table
                                    > orderno, sheetno, custname
                                    > for details table
                                    >orderno, itemno, design, price, qty, deleted
                                    >
                                    > i want to fetch all details from master and details table as per orderno
                                    >but from details table only those records whose deleted = '0'
                                    >
                                    > if there is no record in details table only master data should show
                                    >so i use left join
                                    > i write
                                    > select o.orderno, o.sheetno, o.custname,
                                    > od.itemno, od.design, od.price, od.qty, od.deleted
                                    > from orders o join orderdetails od
                                    > left join on o.orderno = od.orderno
                                    > where o.orderno = @Orderno
                                    > this fetch records as per demand
                                    >but i want from details table only those record whose deleted = '0'
                                    >so i add condition
                                    > where o.orderno = @Orderno and od.deleted = '0'
                                    >
                                    > now if in details table there is record it shows me properly
                                    >but if in details table there is no record it doesn't show any thing
                                    >
                                    > but i want in this case data from master table should get
                                    >
                                    >i hope i am able to explain my problem
                                    >
                                    > pls give me any clue about this problem
                                    >
                                    > thanks
                                    >
                                    >
                                    >---------------------------------
                                    >How low will we go? Check out Yahoo! Messenger�s low PC-to-Phone call
                                    >rates.
                                    >
                                    >[Non-text portions of this message have been removed]
                                    >
                                    >
                                    >

                                    _________________________________________________________________
                                    Discover. Explore. Connect-Windows Live Spaces. Check out!
                                    http://www.msnspecials.in/windowslive/livespaces.asp
                                  • manisha kadam
                                    DECLARE @CatalogueNo varchar(50) SELECT @CatalogueNo = CatalogueNo + , FROM ProductCatalogue WHERE productid = 117 in table ProductCatalogue i have 5
                                    Message 17 of 22 , Jun 11, 2007
                                    • 0 Attachment
                                      DECLARE @CatalogueNo varchar(50)
                                      SELECT @CatalogueNo = CatalogueNo + ', ' FROM ProductCatalogue
                                      WHERE productid = 117

                                      in table ProductCatalogue
                                      i have 5 different CatalogueNo like 1, 2, 3, 4, 5

                                      i want to store each value of CatalogueNo in a variable @CatalogueNo
                                      but it just gives me 5

                                      how can i do it
                                      thanks


                                      ---------------------------------
                                      Looking for a deal? Find great prices on flights and hotels with Yahoo! FareChase.

                                      [Non-text portions of this message have been removed]
                                    • N K
                                      Hi, Use a cursor and then fetch individual rows. Nipun manisha kadam wrote: DECLARE @CatalogueNo varchar(50) SELECT @CatalogueNo =
                                      Message 18 of 22 , Jun 12, 2007
                                      • 0 Attachment
                                        Hi,

                                        Use a cursor and then fetch individual rows.

                                        Nipun

                                        manisha kadam <l.manisha@...> wrote:
                                        DECLARE @CatalogueNo varchar(50)
                                        SELECT @CatalogueNo = CatalogueNo + ', ' FROM ProductCatalogue
                                        WHERE productid = 117

                                        in table ProductCatalogue
                                        i have 5 different CatalogueNo like 1, 2, 3, 4, 5

                                        i want to store each value of CatalogueNo in a variable @CatalogueNo
                                        but it just gives me 5

                                        how can i do it
                                        thanks

                                        ---------------------------------
                                        Looking for a deal? Find great prices on flights and hotels with Yahoo! FareChase.

                                        [Non-text portions of this message have been removed]






                                        ---------------------------------
                                        Boardwalk for $500? In 2007? Ha!
                                        Play Monopoly Here and Now (it's updated for today's economy) at Yahoo! Games.

                                        [Non-text portions of this message have been removed]
                                      • John Warner
                                        Is it not giving you the period, is that your problem? John Warner
                                        Message 19 of 22 , Jun 12, 2007
                                        • 0 Attachment
                                          Is it not giving you the period, is that your problem?

                                          John Warner




                                          > -----Original Message-----
                                          > From: SQLQueriesNoCode@yahoogroups.com
                                          > [mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of manisha kadam
                                          > Sent: Tuesday, June 12, 2007 2:05 AM
                                          > To: SQL; asp; asp; ASP; c_sharp_world@yahoogroups.com; l
                                          > Subject: [SQLQueriesNoCode] sql query
                                          >
                                          >
                                          > DECLARE @CatalogueNo varchar(50)
                                          > SELECT @CatalogueNo = CatalogueNo + ', ' FROM
                                          > ProductCatalogue WHERE productid = 117
                                          >
                                          > in table ProductCatalogue
                                          > i have 5 different CatalogueNo like 1, 2, 3, 4, 5
                                          >
                                          > i want to store each value of CatalogueNo in a variable @CatalogueNo
                                          > but it just gives me 5
                                          >
                                          > how can i do it
                                          > thanks
                                        • Muhammad Ahmed
                                          Hi, This is one idea that may resolve your issue. I think their is a built-in function of SQL Server as well that generates the required output. ... Declare
                                          Message 20 of 22 , Jun 12, 2007
                                          • 0 Attachment
                                            Hi,

                                            This is one idea that may resolve your issue. I think their is a built-in
                                            function of SQL Server as well that generates the required output.
                                            --------------------------------------------------------------------------------------------------------------------------------------------
                                            Declare @inv table(invs varchar(50))
                                            Declare @invs varchar(500)
                                            Set @invs = ''
                                            Declare @temp varchar(50)

                                            INSERT INTO @inv
                                            SELECT CatalogueNo FROM ProductCatalogue

                                            DECLARE cl CURSOR FOR SELECT * from @inv

                                            OPEN cl

                                            FETCH NEXT FROM cl INTO @temp

                                            IF @@fetch_status = 0
                                            BEGIN
                                            SET @invs = @temp
                                            FETCH NEXT FROM cl INTO @temp

                                            WHILE @FETCH_STATUS = 0
                                            BEGIN
                                            SET @invs = @invs + ',' + @temp
                                            FETCH NEXT FROM cl INTO @temp
                                            END
                                            END

                                            Close cl
                                            Deallocate cl

                                            SELECT @invs

                                            --------------------------------------------------------------------------------------------------------------------------------------------

                                            Regards,
                                            Ahmed

                                            On 6/12/07, manisha kadam <l.manisha@...> wrote:
                                            >
                                            > DECLARE @CatalogueNo varchar(50)
                                            > SELECT @CatalogueNo = CatalogueNo + ', ' FROM ProductCatalogue
                                            > WHERE productid = 117
                                            >
                                            > in table ProductCatalogue
                                            > i have 5 different CatalogueNo like 1, 2, 3, 4, 5
                                            >
                                            > i want to store each value of CatalogueNo in a variable @CatalogueNo
                                            > but it just gives me 5
                                            >
                                            > how can i do it
                                            > thanks
                                            >
                                            >
                                            > ---------------------------------
                                            > Looking for a deal? Find great prices on flights and hotels with Yahoo!
                                            > FareChase.
                                            >
                                            > [Non-text portions of this message have been removed]
                                            >
                                            >
                                            >
                                            >
                                            > Yahoo! Groups Links
                                            >
                                            >
                                            >
                                            >


                                            --
                                            Regards,

                                            Muhammad Ahmed


                                            [Non-text portions of this message have been removed]
                                          • Arnie Rowland
                                            Manisha, Please clarify: You want ALL five CatalogueNos in the one @CatalogueNo variable? (Like an array?) Which version of SQL Server are you using
                                            Message 21 of 22 , Jun 12, 2007
                                            • 0 Attachment
                                              Manisha,

                                              Please clarify:

                                              You want ALL five CatalogueNos in the one @CatalogueNo variable? (Like an
                                              array?)

                                              Which version of SQL Server are you using (2000/2005)?

                                              - Arnie Rowland

                                              "I am a great believer in luck, and I find that the harder I work, the more
                                              I have of it." - Thomas Jefferson (1743-1826)


                                              -----Original Message-----
                                              From: SQLQueriesNoCode@yahoogroups.com
                                              [mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of kadam
                                              Sent: Tuesday, June 12, 2007 4:44 AM
                                              To: Arnie
                                              Subject: [SQLQueriesNoCode] sql query

                                              DECLARE @CatalogueNo varchar(50)
                                              SELECT @CatalogueNo = CatalogueNo + ', ' FROM ProductCatalogue
                                              WHERE productid = 117

                                              in table ProductCatalogue
                                              i have 5 different CatalogueNo like 1, 2, 3, 4, 5

                                              i want to store each value of CatalogueNo in a variable @CatalogueNo
                                              but it just gives me 5

                                              how can i do it
                                              thanks


                                              ---------------------------------
                                              Looking for a deal? Find great prices on flights and hotels with Yahoo!
                                              FareChase.

                                              [Non-text portions of this message have been removed]




                                              Yahoo! Groups Links







                                              Disclaimer - June 12, 2007
                                              This email and any files transmitted with it are confidential and intended solely for SQLQueriesNoCode@yahoogroups.com. If you are not the named addressee you should not disseminate, distribute, copy or alter this email. Any views or opinions presented in this email are solely those of the author and might not represent those of Westwood Consulting, Inc. Warning: Although Westwood Consulting, Inc has taken reasonable precautions to ensure no viruses are present in this email, the company cannot accept responsibility for any loss or damage arising from the use of this email or attachments.
                                              This disclaimer was added by Policy Patrol: http://www.policypatrol.com/


                                              [Non-text portions of this message have been removed]
                                            Your message has been successfully submitted and would be delivered to recipients shortly.