i'm sorry if this kind of question was already posted, i'm a newbie and i hope you will find anyway the time to answer me.
I've installed mysql version 4.0.12-nt on my Windows XP Professional notebook and mysql version 4.0.12-standard on a Solaris 8 machine.
Some antefacts:
I need a very very small database to keep track of a set of discussion topics, a set of users and the subscriptions of the users to the discussion topics.
This is my database (a poor designed one, i know):
-- MySQL dump 9.07 -- -- Host: localhost Database: buddyfinder --------------------------------------------------------- -- Server version 4.0.12-nt
-- -- Current Database: buddyfinder --
CREATE DATABASE /*!32312 IF NOT EXISTS*/ buddyfinder;
USE buddyfinder;
-- -- Table structure for table 'subscriptions' --
CREATE TABLE subscriptions (
SipUri varchar(80) NOT NULL default '',
TopicName varchar(40) NOT NULL default '',
Alias varchar(40) default NULL,
PRIMARY KEY (SipUri,TopicName),
KEY SipUri_ind (SipUri),
KEY TopicName_ind (TopicName),
FOREIGN KEY (`SipUri`) REFERENCES `users` (`SipUri`) ON DELETE CASCADE ON UPDATE CASCADE,
FOREIGN KEY (`TopicName`) REFERENCES `topics` (`TopicName`) ON DELETE CASCADE ON UPDATE CASCADE
) TYPE=InnoDB;
-- -- Table structure for table 'topics' --
CREATE TABLE topics ( TopicName varchar(40) NOT NULL default '0', TopicType smallint(6) NOT NULL default '0', PRIMARY KEY (TopicName) ) TYPE=InnoDB;
-- -- Table structure for table 'users' --
CREATE TABLE users ( SipUri varchar(80) NOT NULL default '', PRIMARY KEY (SipUri) ) TYPE=InnoDB;
what i'd need is to present the user a list of the topics that he's not yet subscribed.
I read the MySQL manual and I found that NOT IN and the nested SELECTs are not supported in this version, so I ended up to use temporary tables.
These are the queries that i use:
CREATE TEMPORARY TABLE user03 (TopicName varchar(40));
INSERT INTO user03 SELECT TopicName FROM subscriptions WHERE SipUri='[EMAIL PROTECTED]';
SELECT topics.TopicName FROM topics,user03 WHERE topics.TopicName != user03.TopicName;
They are working fine on XP, the result is the list of topics at which user03 isn't subscribed yet. But on Solaris, with the same database, the result is a list of all the topics, repeated a certain number of times.
I'm stucked on this, is it possible that the problem is the 4.0.12-standard version? Should I use the 4.0.12-max version? Or am i missing something?
Thank you in advance for any help you'd like to give me, all the best Giovanna
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
