CodeGuru Home VC++ / MFC / C++ .NET / C# Visual Basic VB Forums Developer.com
Results 1 to 8 of 8
  1. #1
    Join Date
    Mar 2010
    Posts
    111

    Unhappy check colum which have multiple values in mysql

    Hi

    i want to check the column which have mulitple values and retrieve the row

    i have two tables one is games and another one is user

    In Games table contain the 3 fields id,games,game_date

    games(carrace,carrom,chess)

    In user table have username,interstedgame,and etc...

    so i want to retrieve the row from games table which is match the user interstedgame

    i use code like this

    select * from games g,user u where u.user in(g.games)


    It didnt retrieve any row, if the games field contain one value means it display the one row if the games field have mulitple value means it didnt display any row

    how could i code this please give any suggestion how to code


    Thanks
    sona

  2. #2
    Join Date
    May 2002
    Posts
    10,943

    Re: check colum which have multiple values in mysql

    Can you provide an example of some data from your database?
    If the post was helpful...Rate it! Remember to use [code] or [php] tags.

  3. #3
    Join Date
    Mar 2010
    Posts
    111

    Re: check colum which have multiple values in mysql

    Hi

    usertable have

    id name game hobby address......
    1 ajay carom tennis 56...
    2 malar chess fashiondesign 588...

    games table

    id competitiondate gamesconducted
    1 2010-8-6 carom,chess,drawing
    2 2010-8-10 chess,throwball,shortput
    3 2010-8-14 carom

    i want to compare these two tables games and retrieve the related records from the games table

    the user 1 have selected game carom so i want compare gamesconducted ,games and retrieve the row

    if i compare like this

    select * from games g ,user u where u.game in (g.gamesconducted) and u.name='ajay';

    it retrieve the records if the games conducted field have one value like in 3 row of the games table
    if the gamesconducted field have more than one value it didnt retrieve those row


    if any of my logic is wrong ,suggest me
    thanks
    sona

  4. #4
    Join Date
    May 2002
    Posts
    10,943

    Re: check colum which have multiple values in mysql

    You're matching your data all wrong. Great a game_id column in your usertable and match it with the id of the games table. Then it's a simple WHERE clause.
    If the post was helpful...Rate it! Remember to use [code] or [php] tags.

  5. #5
    Join Date
    Jun 2007
    Location
    Aurora CO USA
    Posts
    137

    Re: check colum which have multiple values in mysql

    Quote Originally Posted by PeejAvery View Post
    You're matching your data all wrong. Great a game_id column in your usertable and match it with the id of the games table. Then it's a simple WHERE clause.
    I agree with this. The problem is with your SQL statement, where you're comparing user.user to games.games. You'll never get a match. I think you want something like this:

    Code:
    select * from games g,user u where u.user in (g.name)
    I'm basing this on your last description which shows a user name in the games table.

  6. #6
    Join Date
    Mar 2010
    Posts
    111

    Re: check colum which have multiple values in mysql

    hi

    thanks ajhampson,peejavery

    i made change table


    now i have three tables

    games table

    id gamename
    1 carrom
    2 chess
    3 throwball
    4 shortput
    5 drawing

    user table

    id username interestedgame hobby
    1 ajay 1 tennis
    2 malar 2 fashiondesign


    competitiondate

    id competitiondate gamesconducted description
    1 2010-8-6 1,2,5 ...
    2 2010-8-10 2,3,4 ...
    3 2010-8-14 1 ...

    i want to display the records from competition.which is the game interested in usertable and equal to competition table for the user id=1

    so first and third record from competition table are display for the user ajay but it didnt display


    i run this query but didnt display any record , how will change

    select * from competition c,user u where c.gameconduted in (u.interestedgame) and u.name='ajay';

    is any my assumption or comparison is wrong sorry for that


    thanks
    sona

  7. #7
    Join Date
    May 2002
    Posts
    10,943

    Re: check colum which have multiple values in mysql

    Once again, your database structure is very poor. Your structure for the competitions needs to provide unique rows for each game. Nesting multiple games comma delimited within a table column will make your processing so much more difficult.

    And, I would suggest using nested SQL queries for the WHERE statements instead of IN.
    If the post was helpful...Rate it! Remember to use [code] or [php] tags.

  8. #8
    Join Date
    Mar 2010
    Posts
    111

    Re: check colum which have multiple values in mysql

    thanks

Posting Permissions

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





Click Here to Expand Forum to Full Width

Featured