Multiple results

[running_out_of_imagination]

Hello,

I have this query:
[code]
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[/code]

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?

[wickning1]

Your join conditions should always be true.

[code]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[/code]

[running_out_of_imagination]

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]
c.added BETWEEN "1140974479" AND "1141060879"/code]


Could it be something related with the OR?

[wickning1]

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?

[running_out_of_imagination]

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.

[wickning1]

Ah ok, you wanted an AND instead of OR:
[code]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[/code]

[running_out_of_imagination]

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?

[wickning1]

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:

[code]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[/code]
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.

[running_out_of_imagination]

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

[/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.

[wickning1]

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.

[running_out_of_imagination]

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

Here is the structure with some data:

[code]

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');[/code]

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

[wickning1]

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.

[running_out_of_imagination]

[!--quoteo(post=350744:date=Mar 1 2006, 07:22 PM:name=wickning1)--][div class=\'quotetop\']QUOTE(wickning1 @ Mar 1 2006, 07:22 PM) [snapback]350744[/snapback][/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.

[wickning1]

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.

[running_out_of_imagination]

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:
[code]
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[/code]

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:
[code]
--
-- 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');
[/code]

[wickning1]

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.