[MySQL] Exluding results from 2 tables

[Sinzygy]

yeah, probably the topic doesnt' describe my question too well.

Here's the problem:

I have 2 tables: quests(ID,name,etc.) and quest_log(user,qID,status)

Now I want to do the following: I want to get the quests that are available but only those quests that haven't been accepted (inserted into the users quest_log) yet.

I tried something like the example below, but with no results

Code: [Select]

$sql = "SELECT quests.*
FROM quests,quest_log
WHERE quest_log.qID <> quests.ID
AND quest_log.user=".$user['ID'];


[beam]

Okay, I got it working. I didn't use your field names, but you can easily fit this to work.

Code: [Select]

--
-- Table structure for table `quest_log`
--

CREATE TABLE `quest_log` (
  `log_id` int(11) NOT NULL auto_increment,
  `user_id` int(11) NOT NULL default '0',
  `quest_id` int(11) NOT NULL default '0',
  `status` enum('U','F') NOT NULL default 'U',
  UNIQUE KEY `id` (`log_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=2 ;

--
-- Dumping data for table `quest_log`
--

INSERT INTO `quest_log` VALUES (1, 1, 1, 'U');

-- --------------------------------------------------------

--
-- Table structure for table `quests`
--

CREATE TABLE `quests` (
  `quest_id` int(11) NOT NULL auto_increment,
  `name` varchar(64) NOT NULL default '',
  UNIQUE KEY `id` (`quest_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=3 ;

--
-- Dumping data for table `quests`
--

INSERT INTO `quests` VALUES (1, 'Fetch Records');
INSERT INTO `quests` VALUES (2, 'Fix Errors');

Thats' the SQL debug code I used. It sets up two possible quests, one of which has already been started (the first one, Fetch Records). This is the SQL I used to select the quests that had not been inserted into the quest log:

Code: [Select]

SELECT
quests.*,
quest_log.*
FROM
quests, quest_log
WHERE
quest_log.quest_id != quests.quest_id AND quest_log.user_id = $playerid

If you run that query, it should return the second quest Fix Errors, which is the quest that had not yet been started.

[codestryke]

Or you can use a join

Code: [Select]

SELECT quest.*
FROM quest
LEFT JOIN quest_log
ON quest_log.questid = quest.questid and userid = 1
WHERE quest_log.questid is null

edit.. opps I'll add my table structures I used

Code: [Select]

CREATE TABLE `quest` (
  `questid` int(10) unsigned NOT NULL auto_increment,
  `name` varchar(255) NOT NULL default '',
  PRIMARY KEY  (`questid`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=5 ;

INSERT INTO `quest` VALUES (1, 'Kill Goblin');
INSERT INTO `quest` VALUES (2, 'Kill Snake');
INSERT INTO `quest` VALUES (3, 'Kill Bat');
INSERT INTO `quest` VALUES (4, 'Kill Dragon');

CREATE TABLE `quest_log` (
  `userid` int(10) unsigned NOT NULL default '0',
  `questid` int(10) unsigned NOT NULL default '0',
  `status` tinyint(4) NOT NULL default '0',
  KEY `userid` (`userid`,`questid`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

INSERT INTO `quest_log` VALUES (1, 1, 0);
INSERT INTO `quest_log` VALUES (1, 2, 0);
INSERT INTO `quest_log` VALUES (2, 1, 0);
INSERT INTO `quest_log` VALUES (2, 2, 0);
INSERT INTO `quest_log` VALUES (2, 3, 0);
INSERT INTO `quest_log` VALUES (2, 4, 0);


BTW Syn.. if you have a table called Users and they have an id call it userid not id.. Later down the road when the db gets more tables you'll play hell trying to remember which id your referencing in the queries It's not necessary just  good db practice.

[Sinzygy]

woha, 2 replies in less than 2 hrs is great for such a quiet forum

Thanks a lot you two!

@beam
It seems that we are using the almost identical query. I haven't tried yours yet, but from what I see there isn't much difference except for the SELECT part (I only included quests.* and you included quests.* and quest_log.*). Maybe I have a problem with my db. Gotta check it as soon as I have the motivation to do it.

@codestryke
Thanks for the id-tip. I'll consider it when I'm rewriting my code Or when I start yet another game.

I'll have to run both queries later on and see which one fits my need better.