-
July 1st, 2010, 01:57 AM
#1
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
-
July 2nd, 2010, 08:50 AM
#2
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.
-
July 5th, 2010, 11:20 PM
#3
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
-
July 6th, 2010, 04:41 PM
#4
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.
-
July 7th, 2010, 10:48 AM
#5
Re: check colum which have multiple values in mysql
Originally Posted by 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.
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.
-
July 9th, 2010, 12:07 AM
#6
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
-
July 9th, 2010, 07:36 AM
#7
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.
-
July 12th, 2010, 05:34 AM
#8
Re: check colum which have multiple values in mysql
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|