Assuming I have 2 large tables with well over 10 Million Rows in each table. Table 1 is a Movie table with a userID stating the user saw this movie, and Table 2 is a table of Music with a userID stating the user herd this song. What I want to do is be able to run a query whenever the user wants to know if there is a Song with the same ...
The site is already built from years. I am doing some modifications to it. It has controllers "Posts" and "Topics". On the topics page all recent posts are displayed. So it is a simple find of posts from "Posts" table. The fetch is not all assiciated to topic as we are showing all "Posts" and not "Topic" specific "Posts". In Topics cont...
I'm running the following query, which tells me which agents have ever been scheduled on a project at location 51: SELECT agents.agentid, agents.firstname, agents.lastname, schedule.projectid, projects.locationid FROM agents LEFT JOIN schedule USING (agentid) LEFT JOIN projects USING (projectid) WHERE (projects.locationid <=> 51)...
Hi all, I wish to know if I have a join query something like this - Select E.Id,E.Name from Employee E join Dept D on E.DeptId=D.Id and a subquery something like this - Select E.Id,E.Name from Employee Where DeptId in (Select Id from Dept) When I consider performance which of the two queries would be faster and why ? Also is the...
I want to do the equivalent of (A<y AND B>x) OR (C < y AND D>x). how can i do that? Right now, I am creating separate queries: @companies_with_email = Company.contact_emails_date_sent_gt(@monday). contact_emails_date_sent_lt(@friday). find(:all, :select => "distinct companie...
I have three SQL selects, the results of which I need to add together. Two of the three use fairly complex joins. select sum(field_one) from t_a join t_b on (t_a.bid = t_b.id) where t_b.user_id=:id select sum(field_two) from t_c join t_d on (t_c.did = t_d.id) where t_d.user_id=:id select sum(field_three) from t_e where t_e.user_id=:i...
This query executes in a fraction of a second: SELECT customers.customers_id, customers_firstname, customers_lastname, customers.customers_email_address, max(date_purchased) FROM customers join orders on customers.customers_id = orders.customers_id group by customers.customers_id; If I change the join to a left join, it seems to han...
I have an Author model which habtm :feeds. Using Rails 3 want to setup a scope that finds all authors that have no associated feeds. class Author < ActiveRecord::Base has_and_belongs_to_many :feeds scope :without_feed, joins(:feeds).where("authors_feeds.feed_id is null") end ...doesn't seem to work. It feels like a simple t...
Hi, i have a small problem where i have this setup... Table: trade names { trade_id : 1 trade_name : olivers guest house} Table Customer { name: me, trade_id: 1 : blah: blah} i do a left join to get the trade name into customers as if you are a guest house you will have a trade name but as a landlord you will not have a tr...
Does someone have a good idea/solution how to achieve this? Situation is: I have the tables 'releases' and 'ntags', related via 'releases_ntags' (containing 'release_id' and 'ntag_id') And I would like to fetch results for releases via the ntag's 'slug'. I manage to have this semi-working: sql SELECT r.id, r.name FROM releases r LE...
I have models moves, neighborhoods and communities. Neighborhood.rb has_many :communities has_many :moves, :through => :communities accepts_nested_attributes_for :communities Move.rb has_many :communities has_many :neighborhoods, :through => :communities accepts_nested_attributes_for :communities Community.rb belongs...
I am working on a rails project and am having some issues with the following join: @page = Page.find(params[:id], :joins => "LEFT JOIN page_translations ON page_translations.page_id = pages.id") For some reason its only pulling back everything from the Pages table. Here is my model for Page class Page < ActiveRecord::Base has_many...
Suppose I have two tables, one with blog posts and another with readers and their comments: Table 1: table name: BlogPosts: structure: id (int) title (string) Table 2: table name: Readers: id (int) blog_post_id (int) name (string) comment (string) in the Readers table there is a unique composite key on blog_post_id/name (i.e....
Ok this is something a bit simple but as I'm new to Linq and to SQL overall I guess I need a little help. Most tutorials assumed some sql knowledge and therefor weren't as helpful and also hard to search as I do not know this join name ( Inner left? ). Situation: TableParent with 2 primaryKeys, ParentKey1 and ParentKey2 TableChild with...
I am building a recipe app where a user can view recipes, list ingredients, get a shopping list, etc. etc. Each Recipe is made of steps, each step has ingredients, and each ingredient has a grocery. I was quite sure that the way to create these links was through the models, so my models look like this class Recipe < ActiveRecord::Bas...
This may have been asked before and I just can't find it. I have a one to many relationship in the database on a few tables. table1 table2 table3 table2 - table3 is the 1-many relationship here's a mock of what I have: select table1.id table1.Column table2.Column2 -- I want all entries here from table 3 here as well From table1 t...
My system consists of two user types - Students and Tutors. - Tutors can create classes and packs - Both Students and tutors can purchase classes and packs Following are the tables involved Groups Users- Contains common fields of both user types Tutor_Details- Tutor specific fields WebClasses - Classes created by tutors Learning_...
Im a little puzzled here, can someone just look over this query and tell me am i doing anything wrong? SELECT d.* FROM as_downloads d LEFT JOIN as_categories c ON (d.download_category_id = c.category_id) WHERE d.download_category_id != -1 LIMIT 30 Fetching the rows from the as_downloads table but not joining the categories table.. Th...
Hi All, As we know that there are different types of JOINs in any RDBMS, for eg:- Left join, outer join, inner join, left outer join etc. Invariably we use JOINs for lots of our business logics in our projects. However very few people have complete understanding or mastery over these JOINs. Usually people with half known knowledge on JO...
Linq allows to express inner joins by using the join keyword or by using SelectMany() (i.e. a couple of from keywords) with a where keyword: var personsToState = from person in persons join state in statesOfUS on person.State equals state.USPS select new { person, State = state.Name }; foreach (var item in per...