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

sql query

Expand Messages
  • dln_prasad
    hi frnds, how to select top 3 records of a table prasad
    Message 1 of 22 , May 7, 2005
    • 0 Attachment
      hi frnds,

      how to select top 3 records of a table

      prasad
    • Karl Schaubmair
      Hi, just like this: select top 3 * from table Or did you mean another thing? Charlie
      Message 2 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 3 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 4 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 5 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 6 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 7 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 8 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 9 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 10 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 11 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 12 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 13 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 14 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 15 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 16 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 17 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 18 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 19 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 20 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 21 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 22 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.