FRIHOST • FORUMS • SEARCH • FAQ • TOS • BLOGS • DIRECTORY
You are invited to Log in or Register a Frihost Account!

MySQL query help

   Frihost Forum Index -> Scripting -> Php and MySQL
 


richard270384
Hi Guys,

I'm having trouble getting my head around this (maybe I've just been at my pc too long), looking for some solutions or some ideas to point me in the right direction.

Let's say I have a table listing some people:
Quote:
PersonID Name
=============
1 Bob
2 George
3 Cindy
4 Brianna


Then I have a table that lists some pc games
Quote:
GameID Game Featured?
===========
1 Jill of the Jungle NO
2 Commander Keen YES
3 Alex Kidd NO
4 Pitfall NO


I have a third table that lists the games each person has:
Quote:

PersonID GameID
===============
1 2
1 3
2 2
3 3
3 4
3 2
4 1

I want to get a list of all the people who do not own the featured game:
So the output would give me PersonID 3,4, and 5.

I'm having trouble trying to figure out how to make sure number 1 and 2 dont come up...

I am normally pretty good with SQL so I dont expect you to completely write the SQL - just give me a push in the right direction..

Cheers,
Richard
rvec
I think you should have a look at these functions:
http://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html

Not 100% sure if it works with that, but I think it's possible with those.
bernardnapoleon
You could use sub query(query within a query)...
kv
Code:


SELECT PersonID, Name FROM Person_table p WHERE NOT EXISTS (SELECT * FROM game_table g, game_person_table gp WHERE g.GameID=gp.GameID and p.PersonID=gp.PersonID AND g.Featured='YES')

AOP Web Development
i don't think that the personID 3 would be include as it still have gameID 2 which being featured so the output would be 4 and 5 only..... why don't you try to join them and add a condition for featured.
richard270384
Thanks for the help guys,

All sorted now.
Related topics

Help with mysql query
MySQL Query help
MySQL Query Cache
Error connecting to mysql ; please help
What is wrong with this MySQL query?
whats wrong with this mysql query?
what is wrong with this query? Please please help!
mysql/php help please
MySQL connection
mysql gives the wrong result [solved]
mySQL Query/PHP code - get the highest value...
[Edit] mySQL Count
PHP MySQL Question
mysql insert into multiple tables at once?
Looping through multiple mysql results
Reply to topic    Frihost Forum Index -> Scripting -> Php and MySQL

FRIHOST HOME | FAQ | TOS | ABOUT US | CONTACT US | SITE MAP
© 2005-2007 Frihost, forums powered by phpBB.