Im trying to convert a SQL join to LINQ. I need some help in getting the nested join working in LINQ. This is my SQL query, Ive cut it short just to show the nested join in SQL: select distinct txtTaskStatus as TaskStatusDescription, txtempfirstname+ ' ' + txtemplastname as RaisedByEmployeeName, txtTaskPriorityDescription as...
Is is possible in rails to setup on model which is dependant on a join from two tables? This would mean that for the the model record to be found/updated/destroyed there would need to be both records in both database tables linked together in a join. The model would just be all the columns of both tables wrapped together which may then b...
I have 3 tables: listing photo calendar "photo" and "calendar" both have a "listing_id" column in them and every "listing" has a "photo". But I only want to select rows that have no entry in the "calendar" table with the matching "listing_id". I'm not sure if I'm saying it correctly, but any help would be greatly appreciated. And if...
I shortened the code quite a bit, but hopefully someone will get the idea of what i am tryign to do. Need to sum totals from two different selects, i tried putting each of them in Left Outer Joins(tried Inner Joins too). If i run wiht either Left Outer Join commented out, I get the correct data, but when i run them together, i get really...
Similar to this question, how do I set a property on the join model just before save in this context? class Post < ActiveRecord::Base has_many :post_assets has_many :assets, :through => :post_assets has_many :featured_images, :through => :post_assets, :class_name => "Asset", :source => :asset, :conditions => ['post_assets.context ...
Hi, I guess this query is a little basic and I should know more about SQL but haven't done much with joins yet which I guess is the solution here. What I have is a table of people and a table of job roles they hold. A person can have multiple jobs and I wish to have one set of results with a row per person containing their details and...
Hi, I need some help joining two table. I've got: my_type_table, which has columns: type (VARCHAR) latitude (decimal) longitude (decimal) ...and neighborhood_shapes, which has columns: neighborhoods (VARCHAR) neighborhood_polygons (geometry) I've got a function called myWithin which checks the latitude and longitude to see whet...
Hello. I've tried in 1 hour now, trying to find out how I could extract something from the database, in a INNER JOIN with a simple SQL syntax. It works, nearly and my question is how can i select more than only one row from the table. My SQL syntax: SELECT topics.topic_id, topics.topic_subject, COUNT(posts.post_topic) AS comments FRO...
I am having trouble with a SQL query. SELECT t.topicname, m. *, ms.avatar FROM `messages` m INNER JOIN topics t ON m.topicid = t.topicid inner join users u on m.author=u.username inner join misc ms on u.userid=ms.userid ORDER BY postdate DESC LIMIT 5 what i want to do is get the topicname from the topics table, every...
Hi, I have this two tables: mysql> desc vat_rates; +-------------+---------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------------+---------------+------+-----+---------+-------+ | rate_id | varchar(5) | NO | PRI | NULL | | | name | varchar(255) | ...
I use example with Rails 3, but I believe that this is true for Rails 2.3 as well. Suppose, I have model City that has many Locations. I try to find Cities that have locations. I use following code: City.joins(:locations) But output array is: => [#<City id: 5, name: "moscow", created_at: "2010-07-02 15:09:16", updated_at: "2010-07-...
I want to extract rows of group by rls_id but with latest/recent date SELECT * FROM `tbl_revisions` where `date` in (SELECT MAX(`date`) FROM `tbl_revisions` group by `rls_id`) group by `rls_id` The above query works well but i dont want to use subqeuries .I need some other way around. CREATE TABLE IF NOT EXISTS `tbl_revisions` ( `i...
Hello, I am trying to set up a normalised MySQL database containing the three following tables. The first table contains a list of items which can be described by various tags. The third table contains the various tags used to describe the items in the first table. The middle table relates the other two tables to each other. In each...
I have three tables, 'A', 'B' and 'C'. I have query on 'A' and 'B', but I want to add a field that tells me whether or not there is one or more (I dont' care how many) 'C' that are foreign keyed to 'A'. Here's what I have: SELECT A.A_id, A.col_2, col_3, B.col_2, A.col_4 count(C.id) as C_count FROM A JOIN B ON (A...
I have 2 tables: Table 1. options_ethnicity with the following entries: ethnicity_id ethnicity_name 1 White 2 Hispanic 3 African/American Table 2. inquiries with the following entries: inquiry_id ethnicity_id 1 1 2 1 3 1 4 2 5 2 I want to generate a table that shows the number of inquires by ethnicity. My que...
I have two tables. Table A has a list of employee names. Table B is a complex table with information about phone calls made by employees. My goal is to make a table with columns 'name' and 'callCount'. I am aiming to do this with a 'left join' and a 'group by', but I keep missing the employees that have made no calls. How can I just get...
Does anyone know if it's somehow possible to setup an alias for an ActiveRecord table join? Something like: User.find(:all, :alias => "Users as u", :joins => "Friends as f", :select => "u.id,f.name") Any ideas? ...
Possible Duplicate: In MySQL queries, why use join instead of where? Joins are always confusing for me..can anyone tell me what is difference between different joins and which one is quickest and when to use which join and what is difference between join and where clause ?? Plz give ur answer in detail as i already read about ...
I just found this irregularitry in a query i made, why does the result differ when using inner and left joins? for one, the result is ordered both in the temporary hit table and in the final query? I made a little example to show the problem: # cleanup drop temporary table if exists ids; drop temporary table if exists arts; # create t...
I have 3 tables for storing information about books: books ----- id title authors ------- id name books_to_authors ---------------- book_id author_id When a book's information is displayed, I then want to be able to select any other books by the same authors. I have the current book id available from the first query, but I can't fi...