How to duplicate certain results in query

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
jraede
Forum Contributor
Posts: 254
Joined: Tue Feb 16, 2010 5:39 pm

How to duplicate certain results in query

Post by jraede »

Say I have 6 columns of type DATE in a table of events, which correspond to three sets of start/end. If I search for a certain date and it falls between two sets of start/ends, I want to return that result twice, and if possible, also figure out which set that it matched. Is this possible, or am I better off making a copy of the event for each start/end set that it has?
User avatar
califdon
Jack of Zircons
Posts: 4484
Joined: Thu Nov 09, 2006 8:30 pm
Location: California, USA

Re: How to duplicate certain results in query

Post by califdon »

That's too vague a description for anyone to answer your question. What do those dates represent? It sounds suspiciously like your table is not normalized, in which case you need to restructure it. I suggest that you read http://en.wikipedia.org/wiki/Database_normalization and a bunch of other tutorials on normalization.
jraede
Forum Contributor
Posts: 254
Joined: Tue Feb 16, 2010 5:39 pm

Re: How to duplicate certain results in query

Post by jraede »

Let me be more descriptive. The table has events, and say the events can have up to 3 occurrences. For one event, the first occurrence is Jan 12-15th, the second is April 1-3, and the third is April 5-7. These are all stored on the same row, because they're for the same event. If some one searches for events in April, I want two instances of the row to show up, because it happens twice in April.

I think I have it figured out - do a union of select queries, one for possible set of start/end dates. Is there a better way?
jraede
Forum Contributor
Posts: 254
Joined: Tue Feb 16, 2010 5:39 pm

Re: How to duplicate certain results in query

Post by jraede »

Keep in mind that I would also have this issue if I were to separate the dates into their own table - for all intents and purposes it's the same as having them on the same table as the event.
User avatar
califdon
Jack of Zircons
Posts: 4484
Joined: Thu Nov 09, 2006 8:30 pm
Location: California, USA

Re: How to duplicate certain results in query

Post by califdon »

jraede wrote:Keep in mind that I would also have this issue if I were to separate the dates into their own table - for all intents and purposes it's the same as having them on the same table as the event.
Wrong. From your description, I would say that your data is not structured in accordance with the classic relational model on which SQL is based. You may find it possible to write some tortured query to obtain your results, but the simple fact is that your data does not meet the conditions expected by the relational model. You are combining multiple instances in a single row, so you can't take advantage of the logic represented by relational algebra, which is basically what SQL is.

My best advice is for you to restructure your data. You will then find that the queries are straightforward. I have seen this literally hundreds of times here on the forums.
jraede
Forum Contributor
Posts: 254
Joined: Tue Feb 16, 2010 5:39 pm

Re: How to duplicate certain results in query

Post by jraede »

Yeah, as I was falling asleep last night I figured it out - I hadn't thought of starting the query with the schedules table and right-joining the events table.
User avatar
califdon
Jack of Zircons
Posts: 4484
Joined: Thu Nov 09, 2006 8:30 pm
Location: California, USA

Re: How to duplicate certain results in query

Post by califdon »

I'm glad you see the difference. The way to avoid this fuzziness is to begin a database by identifying all the entities that will be represented in your data model. Once you define them properly, you're done--each entity requires a table. Simple. The trick is to understand what an entity is. In your case, "occurrences" describes an entity. You may have another entity that is a group of related occurences (which perhaps is called "events"), but it should be very clear that each occurence is a separate "thing", and that is what demands that it have its own table. In other words, those start and end dates are not attributes of an event, but of the occurrence of an event, so the data needs to be organized that way. Of course, each occurrence record will have a foreign key to relate it to the primary key of the event. Then the relational algebra that we call SQL can handle pretty much any logical extraction of information from the tables.
jraede
Forum Contributor
Posts: 254
Joined: Tue Feb 16, 2010 5:39 pm

Re: How to duplicate certain results in query

Post by jraede »

Cool, I appreciate the help. On a related note, say I want to do a search for any events within a two-week time frame, and an event could be repeating daily. How would I structure the databases so the query returns 14 versions of that event, one for each day? Or in this case would it be better to duplicate the event with php, or maybe do a separate query for each day?
User avatar
califdon
Jack of Zircons
Posts: 4484
Joined: Thu Nov 09, 2006 8:30 pm
Location: California, USA

Re: How to duplicate certain results in query

Post by califdon »

Now you are asking a question about queries. Don't confuse that with structuring your data. The decisions you make about your data model are completely independent of what kinds of queries you may later want to run. Otherwise you would be constantly restructuring your data! So, separate these issues in your mind. First structure your database in accordance with the rules of data normalization (do a Google search on database normalization). Don't even think about how you will use your data, it isn't relevant. If you normalize your data properly, the rest is just a matter of learning SQL. If you don't normalize your data properly, you will have trouble doing almost any queries.

With proper data structures, you can do anything that makes logical sense. Queries like you described are routine, using operators like <, >, <=, >=, Between.

If you haven't already done so, you should read some SQL tutorials, then build a demo database and try writing queries. Hands-on experience is the best way for most people to learn something like this.

In your example, your 2 entities are Events (which may be scheduled several times each) and Occurrences (each of which has its start date and end date). That's all you need to know to structure your data. This will give you a schema like this:

Code: Select all

+--------------+       +---------------+
| tblEvents    |       | tblOccurences |
+--------------+       +---------------+
| E_id  (pk)   |<--+   | O_id   (pk)   |
| Description  |   +-->| E_id   (fk)   |
| ....         |       | StartDate     |
+--------------+       | EndDate       |
                       +---------------+
So if an Event is scheduled 3 times, there will be one row in tblEvents and 3 rows in tblOccurences.

You want all the Events that were scheduled between June 1 and June 15?

Code: Select all

SELECT tblEvents.*, StartDate, EndDate 
FROM tblEvents INNER JOIN tblOccurences ON tblEvents.E_id = tblOccurences.E_id 
WHERE (StartDate <= '2010-06-01' AND EndDate >= '2010-06-01') OR (StartDate BETWEEN '2010-06-01' AND '2010-06-15');
The point is that if your data is properly structured and normalized, any logical request is routine SQL.
jraede
Forum Contributor
Posts: 254
Joined: Tue Feb 16, 2010 5:39 pm

Re: How to duplicate certain results in query

Post by jraede »

Yeah, sorry, I worded that wrong. Assuming I have the database structured as you describe (a table for occurrences joined with a table for events), there has to be a better way to create a daily event than to add one occurrence to the occurrences table for each day after the first occurrence. If I do a greater than/less than DATE query, I would only get 14 instances of the event if there are 14 occurrences linked to that event in the occurrences table that fall within the date range. Surely there's an easier way of returning duplicate events, if, say, there's a column on the attached occurrence that marks it as a daily event.
mikosiko
Forum Regular
Posts: 757
Joined: Wed Jan 13, 2010 7:22 pm

Re: How to duplicate certain results in query

Post by mikosiko »

jraede wrote:there has to be a better way to create a daily event than to add one occurrence to the occurrences table for each day after the first occurrence. If I do a greater than/less than DATE query, I would only get 14 instances of the event if there are 14 occurrences linked to that event in the occurrences table that fall within the date range
:?: :?:

very hard to follow your logic.... why you don't post an real example of your data and tell us what exactly are the results that you want to obtain.

Califdon gave you a lot of excellent advices that you should read again and apply them to your problem/model...
jraede
Forum Contributor
Posts: 254
Joined: Tue Feb 16, 2010 5:39 pm

Re: How to duplicate certain results in query

Post by jraede »

I fully understand Califdon's model... my problem is if I want to do a search for a span of 14 days, and I have an event that I want to repeat daily, it would need to appear 14 times in that search. Is there a better/more efficient way than adding one occurrence to the occurrences table for each day that the event repeats? That could end up being tens of thousands of entries, which could add up.
Post Reply