Hi, I have 3 tables - venues, users, and updates (which have a integer for rating) - and I want to write a query that will return a list of all my venues as well as their average ratings using only the most recent update for each person, venue pair. For example, if user 1 rates venue A once at 9 am with a 4, and then rates it again at 5...
Hi guys, I wonder if it would be possible to query two tables as pictured bellow, and achieve the following result. The two tables are (note that the page_id in the pages_contents table corresponds to the id column in the pages table.): Wanted result (the two tables are joined by the biggest version number): Thanks in advance for...
I have 2 tables. 1 is music and 2 is listenTrack. listenTrack tracks the unique plays of each song. I am trying to get results for popular songs of the month. I'm getting my results but they are just taking too long. Below is my tables and query 430,000 rows CREATE TABLE `listentrack` ( `id` int(11) NOT NULL AUTO_INCREMENT, `se...
How can I retrieve the following results? user | date | login time | logoff time I wish to print out every day in a given time period with matching login and logoff time which is written is table history. If there's a multiple login for same user and for same day then SQL should select minimal date for login and maximum date for logof...
I want to create a MATERIALIZED VIEW from a LEFT JOIN of 2 tables. However the following gives me an error: SELECT field1 FROM table_1 a LEFT JOIN table_2 b ON a.field1=b.field2 ORA-12054: cannot set the ON COMMIT refresh attribute for the materialized view However the following works: SELECT field1 FROM tabl...
Hello, I am new to rails and would appreciate some help optimizing my database usage. Is there a way to load two models associated with each other with one DB query? I have two models Person and Image: class Person < ActiveRecord::Base has_many :images end class Image < ActiveRecord::Base belongs_to :person end I would like to ...
Hi, I'm having troubles with subcategories. I want to display this on my page with 1 sql query: Category 1: Sub cat 1 Sub cat 2 ... Sub cat n Category 2: Sub cat 1 Sub cat 2 ... Sub cat n Category 3: Sub cat 1 Sub cat 2 ... Sub cat n ... Table schema: categories id | catname | description subcategory id | pare...
I have a table like this: create table foo ( a number, b number ) I want to update all the columns from a with the value that is in another table create table bar ( x number, y number ) So, if this would be a procedural programing language I would: foreach foo_item in foo foreach bar_item in bar if( foo_item.b =...
I have two tables, one for invoices and one for incoming payments. An incoming payment can be joined to an invoice by a foreign key like so: from invoices t1 inner join incoming_payments t2 on t1.receiptnum = t2.docnum The question: I want to return all invoices which have more than one payment posted against them. For each invoice...
I'm hoping I'm doing something wrong here and someone can point me in the right direction... I have written a method inside of one of my models, here is the code: def self.find_by_user_id(user_id, *args) self.find(:all, :joins => :case_map, :conditions => ['case_maps.uer_id = ? and case_maps.case_id = cases.id', user_id], ...
Hi - I am trying to join 3 models together while using :conditions , and have hit a snag. Basically, I'm trying to limit the returned Items to those with a specific zipcode (zipcode is a field of a Location, each Item can have many Locations), AND those WITHOUT a specific Tag.name, if the Tag was created by the User doing the search (...
Background I have a table with columns id, imageId, propertyId. There could be many images associated with a single property and there could be many properties associated with a single image. imageId - many-to-many - propertyId I have a list of propertyIds and I want to find out all the images associated with all the propertyIds usi...
I'm trying to join a subset of data from one table with data in another table (example below), and from a performance standpoint, i'm wondering what the best way to do this is, and what is most scalable. The table I am trying to join looks like this (the other tables are already in the query). vid kid uid 1 ...
I have this situation: Stories has many Tasks Tasks have an integer called hours_left I need a named scope to find Stories which all its tasks has more than 0 hours left. Based on this post. I wrote this: class Story has_many :tasks named_scope :uncompleted, { :joins=>["INNER JOIN tasks ON tasks.story_id = stories.id"], ...
I have a problem getting this sql statemen to return what I want: I want it to return a list of properties both the employee or Job_Profile. If one of them do not have the property it should return NULL in that row/column Now the sql looks like: SELECT Parameter.Patameter_Description ParamName, Job_Profile.Title, Job_Property.Ma...
is it possible to specify parameters for :joins similar way as for :conditions? here is an example (sql code is irrelevant) named_scope :threads, { :joins => [" LEFT JOIN groups_messages gm ON messages.id=gm.message_id AND gm.group_id IN (?) ",@group_ids_array], :conditions => ["creator_id=? AND messages.id IN (?)", current_user_i...
Lots of "BAW"s (big ass-websites) are using data storage and retrieval techniques that rely on huge tables with indexes, and using queries that won't/can't use JOINs in their queries (BigTable, HQL, etc) to deal with scalability and sharding databases. How does that work when you have lots and lots of data that is very related? I can on...
I'm looking at programming/design patterns for the model layer of my application, and am wondering which one fits best with the situation where you are doing a retrieval that involves joins across multiple tables. For example, suppose you have the following tables/relations: Customer --> 1..n Accounts --> 0..n Features where a Feature...
Hi everyone, I'm using MySQL 5, and I need to do this sentence to get the results. It's the first attempt when I think about it. I know there are several ways to improve it, but I want to get your opinions first: SELECT p.id, p.image, p.lat, p.lng, p.category_id, p2.title, p2.description, c2.name FROM place p LEFT JOIN place_tr...
Setup: Contact database using 4 tables Contacts Cities States Zips Structure: CREATE TABLE `contacts` ( `id` int(11) NOT NULL AUTO_INCREMENT, `last` varchar(100) CHARACTER SET latin1 COLLATE latin1_general_ci DEFAULT NULL, `first` varchar(100) CHARACTER SET latin1 COLLATE latin1_general_ci DEFAULT NULL, `prefix` varchar(50) ...