Results 1 to 10 of 10
  1. #1
    Join Date
    Dec 2008
    Posts
    9

    Unanswered: how to solve this query??

    category table
    id name date duration
    1 aaa 12/12/07 1
    2 bbb 10/10/08 1
    .........

    user_table

    id name date
    1 aaa 12/12/07
    2 bbb 10/10/08
    .......

    rating table
    id user_id name rating date

    1 7 aaa 4 12/12/07
    2 8 bbb 3 10/10/08

    write now i am displaying these user in order of rating ...i.e high rating first display...
    if rating match then with date i.e minimum date....


    so it display like...

    aaa 4 12/12/07
    bbb 3 10/10/08
    ccc 3 11/10/08
    ddd 2 11/09/08
    .....


    i want show after 1 week


    ddd 2 11/09/08
    aaa 4 12/12/07
    bbb 3 10/10/08
    ccc 3 11/10/08 .....

    again after 1 week

    ccc 3 11/10/08
    ddd 2 11/09/08
    aaa 4 12/12/07
    bbb 3 10/10/08


    How to do this???

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    you've posted the exact same question in both the mysql and oracle forums

    i'm going to assume that your first post was a mistake, so i've removed the oracle post

    by the way, your question is not clear at all

    please try to explain in words what the query should do
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Dec 2008
    Posts
    9
    I am creating 3 tables category, user_table, rating_table

    I am creating this for my collage project... On my website first page
    write now i am displaying data .... using these 3 tables..
    who has maximum rating , if user rating match check which user has older date..

    And on display 2nd page I want to show

    Last data take the first position after 1 week ....

    e.g.
    name rating date
    aaa 4 12/12/07
    bbb 3 10/10/08
    ccc 3 11/10/08
    ddd 2 11/09/08
    .....


    i want show after 1 week


    ddd 2 11/09/08
    aaa 4 12/12/07
    bbb 3 10/10/08
    ccc 3 11/10/08 ..... here ddd take place of aaa

    again after 1 week

    ccc 3 11/10/08
    ddd 2 11/09/08
    aaa 4 12/12/07
    bbb 3 10/10/08 here ccc takes place of ddd

    how to do that??

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    sorry, i have no idea what you are doing
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Dec 2008
    Posts
    9
    Table-> cat_users

    id cat_id user_id
    81 7 12
    160 7 22
    193 7 26
    213 7 29
    223 7 30

    Table-> categories

    id parent_id cat
    7 NULL Stive
    11 0 Akshay
    15 NULL Shooting
    24 NULL Fighting
    52 NULL Puzzle

    Table-> Users
    id valid name date_added
    1 1 Holly NULL
    3 1 Tom 2007-11-01
    7 1 Stive 2007-11-02
    10 1 Elite 2007-11-02
    11 1 Akshay 2007-11-02


    Table-> ratings

    id user_id rating date_added
    1 7 4 2008-07-18
    2 7 3 2008-07-18
    3 3 4 2008-07-18
    4 5 3 2008-07-18
    5 10 2 2008-07-18
    6 6 4 2008-07-18
    7 1922 5 2008-07-18
    15 1322 3 2008-08-02
    16 2055 3 2008-08-07
    81 74 4 2008-08-25
    160 169 5 2008-08-25


    $sql1 = "select users.*, round(avg(rating)) as rating
    , date_format(date_added,'%Y-%m-%d') as `date`
    from users
    left join ratings on (ratings.user_id = users.id)
    inner join cat_users on cat_users.user_id = users.id
    where
    valid = 1
    and users.id in (
    select user_id
    from cat_users
    where cat_id = $cat_id
    )
    and users.date_added <= now()
    group by users.id
    order by date_added desc, rating desc, name
    ";
    $max = GetSQL("select count(*) from ($sql1) counter");


    I am not giving all database it's very large
    so got output like ....

    id user_id rating date_created
    123 1 4 2008-12-09
    211 1 3 2008-12-08
    33 1 4 2008-11-30
    45 1 3 2008-10-31
    57 1 2 2008-10-31
    666 1 4 2008-08-25
    723 1 5 2008-08-25
    15 1 3 2008-08-25
    16 1 3 2008-08-25
    81 1 4 2008-08-25
    160 1 5 2008-08-25

    after that i want to display...after 1 week
    like...

    id user_id rating date_created
    160 1 5 2008-08-25 .....change
    123 1 4 2008-12-09 last data take 1st position
    211 1 3 2008-12-08
    33 1 4 2008-11-30
    45 1 3 2008-10-31
    57 1 2 2008-10-31
    666 1 4 2008-08-25
    723 1 5 2008-08-25
    15 1 3 2008-08-25
    16 1 3 2008-08-25
    81 1 4 2008-08-25

    again after 1 week ...
    id user_id rating date_created
    81 1 4 2008-08-25 .....change
    160 1 5 2008-08-25 last data take 1st position
    123 1 4 2008-12-09
    211 1 3 2008-12-08
    33 1 4 2008-11-30
    45 1 3 2008-10-31
    57 1 2 2008-10-31
    666 1 4 2008-08-25
    723 1 5 2008-08-25
    15 1 3 2008-08-25
    16 1 3 2008-08-25


    How to do that??
    can i add column like postion_id so that change position of data??

    Reason behind change position is old user viewed there profile by another users.

  6. #6
    Join Date
    Dec 2008
    Posts
    9

    How store data with increment value in column??

    I have table

    id user_id rating date_created change_id
    123 1 4 2008-12-09
    211 1 3 2008-12-08
    33 1 4 2008-11-30
    45 1 3 2008-10-31
    57 1 2 2008-10-31
    666 1 4 2008-08-25

    how to modify like this...

    id user_id rating date_created change_id
    123 1 4 2008-12-09 1
    211 1 3 2008-12-08 2
    33 1 4 2008-11-30 3
    45 1 3 2008-10-31 4
    57 1 2 2008-10-31 5
    666 1 4 2008-08-25 6

    how to put data for change_id with increment value in column??

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    "last data take 1st position"

    i am sorry, i have no idea what you're doing
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  8. #8
    Join Date
    Dec 2008
    Posts
    9

    convert merge oracle syntax to mysql??

    Hi...
    i am looking for How store data with increment value in column??
    so i got answer here http://forums.oracle.com/forums/mess...167957#3167957

    i am trying a lot ....
    But not able to covert in mysql syntax...

    Plz help me??

  9. #9
    Join Date
    Dec 2008
    Posts
    9

    Update query problem??

    i want to update the change_id of selected data

    here is query

    select games.*,change_id, round(avg(rating)) as rating
    , date_format(date_added,'%Y-%m-%d') as `date`
    from games
    left join ratings on (ratings.game_id = games.id)
    inner join cat_games on cat_games.game_id = games.id
    where
    valid = 1
    and games.id in (
    select game_id
    from cat_games
    where cat_id = 7
    )
    and games.date_added <= now()
    group by games.id
    order by date_added desc, rating desc, name

    it gives me data ...
    so i want to update change_id like


    update games set change_id=change_id+1 where (select games.*,change_id, round(avg(rating)) as rating
    , date_format(date_added,'%Y-%m-%d') as `date`
    from games
    left join ratings on (ratings.game_id = games.id)
    inner join cat_games on cat_games.game_id = games.id
    where
    valid = 1
    and games.id in (
    select game_id
    from cat_games
    where cat_id = 7
    )
    and games.date_added <= now()
    group by games.id
    order by date_added desc, rating desc, name )

    how to change_id of this particular selected data??

  10. #10
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    you will have to specify correct SQL

    right now, you have this, which is invalid --
    ...where (select games.*,change_id, ...
    the WHERE clause requires a condition, e.g. WHERE foo = 'something'

    you've got WHERE table
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •