check colum which have multiple values in mysql

[goldenswarna]

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

[PeejAvery]

Can you provide an example of some data from your database?

[goldenswarna]

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

[PeejAvery]

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.

[ajhampson]

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:

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.

[goldenswarna]

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

[PeejAvery]

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.

[goldenswarna]

thanks