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


MySQL query help





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
mysql/php help please
Help with mysql query
MySQL connection
mysql gives the wrong result [solved]
Error connecting to mysql ; please help
mySQL Query/PHP code - get the highest value...
What is wrong with this MySQL query?
[Edit] mySQL Count
whats wrong with this mysql query?
MySQL Query help
MySQL Query Cache
what is wrong with this query? Please please help!
mysql query based on 2 fields using 3 different tables
PHP and Javascript Issue handling data from a mysql query
Reply to topic    Frihost Forum Index -> Scripting -> Php and MySQL

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