Hello. I have a problem with executing a little bit more complicated query from elixir, but I'm not really sure how to do this, taking into account performance requirements. I'm no expert in SQL and quite frankly I'm stuck after looking for an answer for a week now. If you could point me to right direction what to look for I'd greatly appreciate it. It's always best to ask someone who has the necessary knowledge.
Here's my problem. I have three tables like User, Place and Visits. Each of those tables have id key. Visits table has one field with logdate (datetime) and two relations: place_id to Place table and user_id to User table. I'd like to make selects from Visits table based upon a field in relation and also select only unique elements based on field value in the other relation. To explain it further. Each visit to place by a user is saved in table Visits with logdate datetime. I'd like to select only Visits to specific Place by a User. However User is only available at this Place if he doesn't have any never Visits to other Place. To illustrate this. Let's suppose we have: User = ['John', 'Jack', 'George'] Place = ['Paris', 'London', 'Madrid'] and Visits (user, place, logdate) like (suppose it's ordered by ascending date): Visits = [ ['John', 'London', date1], ['Jack', 'Paris', date2], ['John', 'Paris', date3], ['George', 'Madrid', date4], ['Jack', 'London', date5], ['John', 'London', date6], ['George', 'London', date7], ['John', 'Madrid', date8] ] I want to get all Users who are currently visiting London. The result should be: (['Jack', 'London', date5], ['George', 'London', date7]). John is not at London because his newest visit is in Madrid. When passing argument to query I know ONLY of Place. Nothing more. How can I do this? Do I need some kind of join? Do I need to create other table? Do I need a subquery? Should I create another table? If so what query would I need to construct? Mind you that this Visits table can have millions of records so I'm really looking for a solution that would give me the best performance and this query can be issued many times a second. Could you please help me a little bit with this issue? I'd greatly appreciate your help. Thank you very much in advance, -- Karol Tomala --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "SQLElixir" group. To post to this group, send email to [email protected] To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlelixir?hl=en -~----------~----~----~----~------~----~------~--~---
