#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2010
    Posts
    95
    Rep Power
    0

    Cool How to make a conditional SQL statement


    Hello,

    I want to make a conditional SQL. I have one table "users" and it has two columns (id, status)

    I need to search for users IDs based on their status. For example.

    Every 5 seconds I am querying from the database (by AJAX) as per the below description:

    Let us say that I have the below results on my wall (on html):
    id1: 1
    status2: "A"

    id2: 2
    status2: "B"

    I need to get IDs from table "users" but exclude the IDs from database if their statuses haven't changed on my wall.

    So the algorithm should be:

    Get all IDs from table "users" but exclude the IDs (1,2) from database (if and only if) their status = ("A", "B").

    I would greatly appreciate if the solution provides an excellent performance when the database records are huge and the check every 5 seconds.

    Thanks in advance.

    Cheers!
  2. #2
  3. No Profile Picture
    Problem Solver
    Devshed Specialist (4000 - 4499 posts)

    Join Date
    Jan 2001
    Location
    Stockholm, Sweden
    Posts
    4,495
    Rep Power
    534
    I think you will have to explain what rows you want a bit more, preferably with a small example.

    This:
    Originally Posted by Moderns
    So the algorithm should be:

    Get all IDs from table "users" but exclude the IDs (1,2) from database (if and only if) their status = ("A", "B").
    makes it sound like you want all users that doesn't have status "A" or "B" which sounds like it would be a lot of them.

    When you formulate a query think about what rows you want and not which rows that you don't want.
    An important distinction that can do wonders for database performance.
    /Stefan
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2010
    Posts
    95
    Rep Power
    0
    Originally Posted by sr
    I think you will have to explain what rows you want a bit more, preferably with a small example.

    This:

    makes it sound like you want all users that doesn't have status "A" or "B" which sounds like it would be a lot of them.

    When you formulate a query think about what rows you want and not which rows that you don't want.
    An important distinction that can do wonders for database performance.
    Thanks for your reply.

    Simply, I need to retrieve all IDs including the IDs (1,2). But for the IDs (1,2): I need to retrieve them in case their corresponding statuses are NOT EQUAL ("A", "B") respectively.

    But for IDs other than (1,2) like (3,4,5,...), I need to retrieve them without any condition.

    Hope it is clear.

    Thanks.
  6. #4
  7. No Profile Picture
    Problem Solver
    Devshed Specialist (4000 - 4499 posts)

    Join Date
    Jan 2001
    Location
    Stockholm, Sweden
    Posts
    4,495
    Rep Power
    534
    So what you are saying is that if:
    1. id=1 has status = 'A' then you don't want it
    2. id=2 has status = 'B' then you don't want it
    3. You want all else

    Code:
    SELECT
      *
    FROM
      yourTable
    WHERE
      NOT (id=1 AND status = 'A')
      AND NOT (id=2 AND status = 'B')
    But the performance for this query might very well be quite terrible if you have any size on your table since it must go through and return ALL rows not matching the ones that should be thrown away.
    But then again if you only have about 1000 users then you will probably not really notice anything.
    /Stefan
  8. #5
  9. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2010
    Posts
    95
    Rep Power
    0
    Originally Posted by sr
    So what you are saying is that if:
    1. id=1 has status = 'A' then you don't want it
    2. id=2 has status = 'B' then you don't want it
    3. You want all else

    Code:
    SELECT
      *
    FROM
      yourTable
    WHERE
      NOT (id=1 AND status = 'A')
      AND NOT (id=2 AND status = 'B')
    But the performance for this query might very well be quite terrible if you have any size on your table since it must go through and return ALL rows not matching the ones that should be thrown away.
    But then again if you only have about 1000 users then you will probably not really notice anything.
    Thanks for your reply. Any better performance query? Records are more than 500,000.

    Thanks.
  10. #6
  11. No Profile Picture
    Problem Solver
    Devshed Specialist (4000 - 4499 posts)

    Join Date
    Jan 2001
    Location
    Stockholm, Sweden
    Posts
    4,495
    Rep Power
    534
    Originally Posted by Moderns
    Thanks for your reply. Any better performance query? Records are more than 500,000.
    Not as long as you want 499,998 entries returned.
    If you however want to get only 20 of these entries based on a good search criteria then we have something we can work with.
    /Stefan
  12. #7
  13. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2010
    Posts
    95
    Rep Power
    0
    Originally Posted by sr
    Not as long as you want 499,998 entries returned.
    If you however want to get only 20 of these entries based on a good search criteria then we have something we can work with.
    I don't want to return 500,000 records at once. I need to search through 500,000 records and return just 10 to 20 records each 5 seconds. Is that possible?

    Thank you very much!
  14. #8
  15. No Profile Picture
    Problem Solver
    Devshed Specialist (4000 - 4499 posts)

    Join Date
    Jan 2001
    Location
    Stockholm, Sweden
    Posts
    4,495
    Rep Power
    534
    And which 10-20 records do you want?
    /Stefan

IMN logo majestic logo threadwatch logo seochat tools logo