Jump to content


Photo

Multiple results


  • Please log in to reply
15 replies to this topic

#1 running_out_of_imagination

running_out_of_imagination
  • Members
  • PipPip
  • Member
  • 14 posts
  • LocationSouthampton, UK

Posted 27 February 2006 - 06:38 PM

Hello,

I have this query:
SELECT players.id AS id, players.name AS name, players.surname AS surname, players.email AS email, calendar.start AS beginning, calendar.end AS finnish, calendar.name AS title, calendar.event AS description, calendar.id AS eventid FROM `calendar` , `attendance` , `players` WHERE (attendance.present = "yes" AND calendar.start BETWEEN "1141060879" AND "1141665679" AND attendance.event = calendar.id AND attendance.player = players.id) OR (calendar.added BETWEEN "1140974479" AND "1141060879") ORDER BY players.id,calendar.start ASC LIMIT 0 , 30

The query returns the values I want, but many times the same rows (6 times each), does anyone know why and/or how to fix it?

#2 wickning1

wickning1
  • Members
  • PipPipPip
  • Advanced Member
  • 405 posts

Posted 27 February 2006 - 07:43 PM

Your join conditions should always be true.

SELECT p.id, p.name, p.surname, p.email, c.start AS beginning, c.end AS finish, c.name AS title, c.event AS description, c.id AS eventid 
FROM `calendar` c, `attendance` a, `players` p 
WHERE (a.event = c.id AND a.player = p.id AND a.present = "yes") 
AND (
    c.start BETWEEN "1141060879" AND "1141665679"
    OR 
    c.added BETWEEN "1140974479" AND "1141060879"
)
ORDER BY p.id,c.start ASC LIMIT 0 , 30


#3 running_out_of_imagination

running_out_of_imagination
  • Members
  • PipPip
  • Member
  • 14 posts
  • LocationSouthampton, UK

Posted 27 February 2006 - 08:48 PM

Hey wickning1,

Your solution had 2 consequences. First of all it did stop the repeated entries from showing up, but it now doesn't really run the second part of the query:
[code=auto:0]
c.added BETWEEN "1140974479" AND "1141060879"/code]


Could it be something related with the OR?

#4 wickning1

wickning1
  • Members
  • PipPipPip
  • Advanced Member
  • 405 posts

Posted 27 February 2006 - 10:05 PM

I don't think I can offer any more advice until I know a little more about the background of the problem. Perhaps you should explain a bit about what your script does and what the different columns mean. What results are you looking to retreive?

#5 running_out_of_imagination

running_out_of_imagination
  • Members
  • PipPip
  • Member
  • 14 posts
  • LocationSouthampton, UK

Posted 28 February 2006 - 05:42 PM

Ok, this query is based on 3 databases. One that has the players and their details (e-mail), one called calendar which has events (trainings, games) and one called attendance (which stores information weather people attend or not the events). The query is supposed to retrieve rows with the players details and events details that:

were added in the last 24 hours
will start during the next week

This data is then processed in php and a script e-mails the people.

My initial script did that correctly but retrieved the same rows many times.

#6 wickning1

wickning1
  • Members
  • PipPipPip
  • Advanced Member
  • 405 posts

Posted 28 February 2006 - 09:27 PM

Ah ok, you wanted an AND instead of OR:
SELECT p.id, p.name, p.surname, p.email, c.start AS beginning, c.end AS finish, c.name AS title, c.event AS description, c.id AS eventid
FROM `calendar` c, `attendance` a, `players` p
WHERE a.event = c.id AND a.player = p.id AND a.present = "yes"
AND c.start BETWEEN "1141060879" AND "1141665679"
AND c.added BETWEEN "1140974479" AND "1141060879"
ORDER BY p.id,c.start ASC LIMIT 0 , 30


#7 running_out_of_imagination

running_out_of_imagination
  • Members
  • PipPip
  • Member
  • 14 posts
  • LocationSouthampton, UK

Posted 28 February 2006 - 11:07 PM

No, I want the events that were added in the last 24 hours and the events that will occur during the week. Not the events added during the last 24 hours that will occur during the next week. Hence I have to use OR, right?

#8 wickning1

wickning1
  • Members
  • PipPipPip
  • Advanced Member
  • 405 posts

Posted 28 February 2006 - 11:31 PM

If that's the case then the query I posted earlier should be working for you. Perhaps you should use mysql to do the date calculations:

SELECT p.id, p.name, p.surname, p.email, c.start AS beginning, c.end AS finish, c.name AS title, c.event AS description, c.id AS eventid
FROM `calendar` c, `attendance` a, `players` p
WHERE (a.event = c.id AND a.player = p.id AND a.present = "yes")
AND (
    c.start BETWEEN UNIX_TIMESTAMP(NOW()) AND UNIX_TIMESTAMP(NOW() + INTERVAL 7 DAY)
    OR
    c.added BETWEEN UNIX_TIMESTAMP(NOW()-INTERVAL 1 DAY) AND UNIX_TIMESTAMP(NOW())
)
ORDER BY p.id,c.start ASC LIMIT 30
Are you sure you want the LIMIT 30 on there? If you're sending out emails, it seems like you'd want all the rows returned.

#9 running_out_of_imagination

running_out_of_imagination
  • Members
  • PipPip
  • Member
  • 14 posts
  • LocationSouthampton, UK

Posted 01 March 2006 - 12:08 AM

[!--quoteo(post=350433:date=Feb 28 2006, 11:31 PM:name=wickning1)--][div class=\'quotetop\']QUOTE(wickning1 @ Feb 28 2006, 11:31 PM) View Post[/div][div class=\'quotemain\'][!--quotec--]
a.event = c.id

[/quote]

Those are not the (major) issues. With the query you gave me I get an empty set, something due with the part above. Because if I run the query without that bit in particular I get results.

#10 wickning1

wickning1
  • Members
  • PipPipPip
  • Advanced Member
  • 405 posts

Posted 01 March 2006 - 05:25 AM

I took that join condition from your original query... Do those columns actually match up or not? Does event in the attendance table match up to an id in the calendar table?

Maybe you could post some relevant information about your table structures.

#11 running_out_of_imagination

running_out_of_imagination
  • Members
  • PipPip
  • Member
  • 14 posts
  • LocationSouthampton, UK

Posted 01 March 2006 - 06:55 PM

[!--quoteo(post=350515:date=Mar 1 2006, 05:25 AM:name=wickning1)--][div class=\'quotetop\']QUOTE(wickning1 @ Mar 1 2006, 05:25 AM) View Post[/div][div class=\'quotemain\'][!--quotec--]
Maybe you could post some relevant information about your table structures.
[/quote]

Here is the structure with some data:


CREATE TABLE `attendance` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `event` int(10) unsigned NOT NULL default '0',
  `player` tinyint(3) unsigned NOT NULL default '0',
  `present` enum('yes','no') NOT NULL default 'no',
  PRIMARY KEY  (`id`)
) TYPE=MyISAM PACK_KEYS=0 AUTO_INCREMENT=11;

-- 
-- Dumping data for table `attendance`
-- 

INSERT INTO `attendance` VALUES (10, 1, 2, 'yes');

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

-- 
-- Table structure for table `calendar`
-- 

CREATE TABLE `calendar` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `name` varchar(128) NOT NULL default '',
  `author` tinyint(3) unsigned NOT NULL default '0',
  `event` text NOT NULL,
  `start` int(16) NOT NULL default '0',
  `end` int(16) NOT NULL default '0',
  `added` int(16) NOT NULL default '0',
  PRIMARY KEY  (`id`)
) TYPE=MyISAM PACK_KEYS=1 AUTO_INCREMENT=12;

-- 
-- Dumping data for table `calendar`
-- 

INSERT INTO `calendar` VALUES (11, 'Event', 1, 'This is an event', 1141207200, 1141221600, 1141061678);

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

-- 
-- Table structure for table `players`
-- 

CREATE TABLE `players` (
  `id` tinyint(3) unsigned NOT NULL auto_increment,
  `name` varchar(128) NOT NULL default '',
  `surname` varchar(128) NOT NULL default '',
  `email` varchar(128) NOT NULL default '',
  `password` varchar(128) NOT NULL default '',
  `homephone` varchar(128) NOT NULL default '',
  `mobile` varchar(128) NOT NULL default '',
  PRIMARY KEY  (`id`)
) TYPE=MyISAM PACK_KEYS=1 AUTO_INCREMENT=10;

-- 
-- Dumping data for table `players`
-- 

INSERT INTO `players` VALUES (8, 'John', 'Doe', 'john@doe.com', 'md5password', '123456789', '8768686865');
INSERT INTO `players` VALUES (9, 'Bob', 'the Builder', 'bob@builder.com', 'thisismypassword', '987654321', '989797976');

I hope it helps. It has 2 people and one event to which Bob is attending.

#12 wickning1

wickning1
  • Members
  • PipPipPip
  • Advanced Member
  • 405 posts

Posted 01 March 2006 - 07:22 PM

Everything I see tells me the query is good. However, the test data you just gave me is not good data. The row that you insert into attendance has two bad references. If Bob is attending "Event" then it should be

INSERT INTO `attendance` VALUES (10, 9, 11, 'yes');

Because Bob's id column is set to 9, and "Event" has its id column set to 11. The attendance table needs to link 9 in the player table to 11 in the calendar table. A table is not an array. You cannot use 1 and 2.

#13 running_out_of_imagination

running_out_of_imagination
  • Members
  • PipPip
  • Member
  • 14 posts
  • LocationSouthampton, UK

Posted 01 March 2006 - 07:29 PM

[!--quoteo(post=350744:date=Mar 1 2006, 07:22 PM:name=wickning1)--][div class=\'quotetop\']QUOTE(wickning1 @ Mar 1 2006, 07:22 PM) View Post[/div][div class=\'quotemain\'][!--quotec--]
Everything I see tells me the query is good. However, the test data you just gave me is not good data. The row that you insert into attendance has two bad references. If Bob is attending "Event" then it should be

INSERT INTO `attendance` VALUES (10, 9, 11, 'yes');

Because Bob's id column is set to 9, and "Event" has its id column set to 11. The attendance table needs to link 9 in the player table to 11 in the calendar table. A table is not an array. You cannot use 1 and 2.
[/quote]

Sorry, auto increment mistake. The ids are supposed to be 1 and 2 for John and Bob respectively.

#14 wickning1

wickning1
  • Members
  • PipPipPip
  • Advanced Member
  • 405 posts

Posted 01 March 2006 - 07:56 PM

Are you sure the query is malfunctioning? The one I posted with MySQL date calculations looks fine to me no matter how many times I look at it. I guess the next step is to develop some test data, tell me what you expect to get from the query, and what you're actually getting.

#15 running_out_of_imagination

running_out_of_imagination
  • Members
  • PipPip
  • Member
  • 14 posts
  • LocationSouthampton, UK

Posted 08 March 2006 - 02:24 PM

Hey,

I made some progress, I found out this is called cartesian product (expalined in [a href=\"http://en.wikipedia.org/wiki/Cartesian_product\" target=\"_blank\"]wikipedia[/a] and [a href=\"http://www.fluffycat.com/SQL/Cartesian-Joins/\" target=\"_blank\"]sql[/a])

So the code looks like this now:
SELECT p.id , p.name , p.surname , p.email , c.start AS beginning , c.end AS finnish , c.name AS title , c.event AS description , c.id AS eventid 
FROM calendar c 
INNER JOIN attendance a ON a.event = c.id 
INNER JOIN players p ON p.id = a.player 
WHERE 
((a.present = "yes" 
AND c.start >= '1141826733' 
AND c.start <= '1142431533') 
OR 
(c.added >= '1141740333' 
AND c.added <= '1141826733')) 
ORDER BY p.id ASC , c.start ASC

But the second part of the OR statement doesn't work. That is if I remove the c.start part, it will return an empty set. I was expecting to get all results added in the previous 24 hours and all the events that people said they were coming that will occur in the next week. If anyone could help that would be great, there's the db below:
-- 
-- Table structure for table `attendance`
-- 

CREATE TABLE `attendance` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `event` int(10) unsigned NOT NULL default '0',
  `player` tinyint(3) unsigned NOT NULL default '0',
  `present` enum('yes','no') NOT NULL default 'no',
  PRIMARY KEY  (`id`)
) TYPE=MyISAM PACK_KEYS=0 AUTO_INCREMENT=2;

-- 
-- Dumping data for table `attendance`
-- 

INSERT INTO `attendance` VALUES (1, 1, 2, 'yes');

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

-- 
-- Table structure for table `calendar`
-- 

CREATE TABLE `calendar` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `name` varchar(128) NOT NULL default '',
  `author` tinyint(3) unsigned NOT NULL default '0',
  `event` text NOT NULL,
  `start` int(16) NOT NULL default '0',
  `end` int(16) NOT NULL default '0',
  `added` int(16) NOT NULL default '0',
  PRIMARY KEY  (`id`)
) TYPE=MyISAM PACK_KEYS=1 AUTO_INCREMENT=2;

-- 
-- Dumping data for table `calendar`
-- 

INSERT INTO `calendar` VALUES (1, 'Birthday', 1, 'It''s my birthday!', 1141999200, 1142006400, 1141826592);

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

-- 
-- Table structure for table `players`
-- 

CREATE TABLE `players` (
  `id` tinyint(3) unsigned NOT NULL auto_increment,
  `name` varchar(128) NOT NULL default '',
  `surname` varchar(128) NOT NULL default '',
  `email` varchar(128) NOT NULL default '',
  `password` varchar(128) NOT NULL default '',
  `homephone` varchar(128) NOT NULL default '',
  `mobile` varchar(128) NOT NULL default '',
  PRIMARY KEY  (`id`)
) TYPE=MyISAM PACK_KEYS=1 AUTO_INCREMENT=3;

-- 
-- Dumping data for table `players`
-- 

INSERT INTO `players` VALUES (1, 'John', 'Doe', 'john@doe.com', 'md5password', '123456789', '8768686865');
INSERT INTO `players` VALUES (2, 'Bob', 'the Builder', 'bob@builder.com', 'thisismypassword', '987654321', '989797976');


#16 wickning1

wickning1
  • Members
  • PipPipPip
  • Advanced Member
  • 405 posts

Posted 09 March 2006 - 04:47 AM

There's nothing wrong with ANY of the queries in this thread save the first ones you posted. I loaded your test data and get expected results with my query, your query, and without the "c.start stuff".

Somehow when you "remove the c.start stuff" you are doing something very wrong and confusing yourself. I don't know what else to tell you. The query is correct.




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users