Strange crosstable/logic problem!

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Strange crosstable/logic problem!

Author

Topic

JF1980
Starting Member

3 Posts

Posted - 2004-07-19 : 04:44:01

Basically, I want to ask a user if a record refers to them. In my first table I have the records which I want to use as reference. In the second table I put the users id and the id of the record which I am asking them about, once I have asked them.

My query basically says, pick a random record from the first table, IF the fields match some of the users information, AND there is no record in teh second table which has both the users id, and the id of the row in the first table which I am trying to match them with. Sounds ok huh. Once I have asked them if a record refers to them, I put a new record in the second table with the id of the record in the first table, and their user id. This means I won't ask them about the same record again.

The problem is, first of all, the query brings two results, second, the query does not bring back a null result when there is only one matching record in the first table, but also a record in the second table because they have already been asked about this one.

The query is:
[CODE]
MYSQL:
SELECT missingfriend.id FROM missingfriend, missingask
WHERE (missingask.userid<>'7' AND missingfriend.id<>missingask.missingid)
AND missingfriend.firstname LIKE '%ka%'
AND ((missingfriend.lastname LIKE '%asa%') OR (missingfriend.lastname LIKE '%rk%'))
AND missingfriend.gender='2'
AND missingfriend.isfound='0'
AND missingfriend.dbstatus<'3'
GROUP BY missingfriend.id
ORDER BY RAND()
LIMIT 1

[/CODE]

I stuck group by in there to try and solve the double result problem, but that is just trying to hide the problem rather than fix it so I guess it should be the first thing to go.

My table structures are:
[CODE]
MYSQL:
# Server version 4.0.12-nt

#
# Table structure for table 'missingask'
#

CREATE TABLE `missingask` (
`id` int(10) unsigned NOT NULL auto_increment,
`userid` int(10) unsigned NOT NULL default '0',
`missingid` int(10) unsigned NOT NULL default '0',
`recorddatetime` datetime NOT NULL default '0000-00-00 00:00:00',
`found` tinyint(1) unsigned NOT NULL default '0',
PRIMARY KEY (`id`)
) TYPE=MyISAM;

#
# Table structure for table 'missingfriend'
#

CREATE TABLE `missingfriend` (
`id` int(10) unsigned NOT NULL auto_increment,
`userid` int(10) unsigned NOT NULL default '0',
`schoolid` int(10) unsigned NOT NULL default '0',
`returnuserid` int(10) unsigned NOT NULL default '0',
`firstname` varchar(30) NOT NULL default '',
`lastname` varchar(30) NOT NULL default '',
`age` int(2) default NULL,
`gender` char(1) default NULL,
`isfound` int(3) NOT NULL default '0',
`infotext` text,
`recorddatetime` datetime NOT NULL default '0000-00-00 00:00:00',
`updatedatetime` datetime NOT NULL default '0000-00-00 00:00:00',
`views` int(10) unsigned NOT NULL default '0',
`displays` int(10) unsigned NOT NULL default '0',
`remark` varchar(120) default NULL,
`dbstatus` int(2) NOT NULL default '0',
`photoid` int(10) unsigned NOT NULL default '0',
PRIMARY KEY (`id`)
) TYPE=MyISAM;

[/CODE]

I have spent the last 4 hours boggling over this, it seems so simple in my mind, but translating that in to working logic seems impossible!!! Please, can someone suggest what I have done wrong, a solution would be magic too!

Kristen
Test

22859 Posts

Posted - 2004-07-19 : 06:57:08

MySQL? you may not get much of a response here I'm afraid 'coz we're basically MicroSofties ...

Kristen

JF1980
Starting Member

3 Posts

Posted - 2004-07-19 : 08:58:09

Dam, I would have thought though that this problem would apply to all SQL systems?

Kristen
Test

22859 Posts

Posted - 2004-07-19 : 09:32:33

The dialects are fairly different, so I can't just throw your example as at MSSQL to test it I'm afraid ...

LIMIT 1, auto_increment, TYPE=MyISAM, int(2), tinyint(1), unsigned - all not recognised by MSSQL, as well as the single quotes around the column names.

Edit: Fixed typos

Kristen

JF1980
Starting Member

3 Posts

Posted - 2004-07-19 : 10:04:39

Heh, I guess it's microsoft who are not following SQL standards, not mySQL.

Kristen
Test

22859 Posts

Posted - 2004-07-19 : 12:53:30

Oh for crying out loud, what is it with you MySQL people?

Are you familiar with the SQL standard that Microsoft is supposedly not following?

Perhaps my copy of ANSI-92 is out of date, but if not:

Where is LIMIT? (Yeah, it's a permitted reserve word, but I don't think that constitutes defining its behaviour)

auto_increment? Nope

TYPE=MyISAM? I didn't bother to look that one up

int(n) or tinyint(n)? Nope, can't find an integer datatype that has a precision component

unsigned? Can't find that, but I suppose I might be looking in the wrong place.

Single quote delimiting an identifier? Nope.

Who cares, anyway? Whatever your system can do is what you're stuck with. And in my case the reason that I don't use MySQL is because I couldn't live with the things MySQL cannot do.

Kristen

Sitka
Aged Yak Warrior

571 Posts

Posted - 2004-07-19 : 15:41:40

quote:
FROM missingfriend, missingask

ex.

http://certcities.com/editorial/columns/story.asp?EditorialsID=106

It is a rich creamy color with a high fat content of 5-7 percent. Being so high in fat, it is usually processed into butter, cheese, or yogurt. An average cow will produce 110 kg. Milk in a lactation period of an average of 149 days

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources