I'm wanting to select rows in a table where the primary key is in another table. I'm not sure if I should use a JOIN or the IN operator in SQL Server 2005. Is there any significant performance difference between these two SQL queries with a large dataset (i.e. millions of rows)? SELECT * FROM a WHERE a.c IN (SELECT d FROM b) SELECT a.*...
Why does the following rails statement User.find(:all, :joins => [:roles,:roles_users], :conditions => { :roles => { :name => 'subscriber', :authorizable_id => self.id, :authorizable_type => self.class.to_s }}) Translates into this (with 2x the ...
The use case here is to find some restricted set of Boys who have Kites with lengths in a certain range, and then optionally retrieve all of the Kites in that range belonging to those boys. Do not assume that I know much about SQL or Hibernate. I would like to form a Hibernate Criteria query where an associated collection has been res...
Hi, I have two separate SELECT statements which are both GROUP-BY'd separately e.g.: SELECT x, y, z FROM a GROUP BY x SELECT x, n, o FROM b GROUP BY x I would very much like to JOIN these two SELECTs together to combine their columns, such as: SELECT x as x1, y, z FROM a GROUP BY x LEFT JOIN ( SELECT x as x2, n, o FROM b GROUP BY...
I am a PHP/MYSQL developer, slowly venturing into the realm of C#/MSSQL and I am having a problem in C# when it comes to reading an MSSQL query that joins two tables. Given the two tables: TableA: int:id VARCHAR(50):name int:b_id TableB: int:id VARCHAR(50):name And given the query SELECT * FROM TableA,TableB WHERE TableA.b_id = ...
I am working with the following SQL query: SELECT a.AppointmentId, a.Status, a.Type, a.Title, b.Days, d.Description, e.FormId FROM Appointment a (nolock) LEFT JOIN AppointmentFormula b (nolock) ON a.AppointmentId = b.AppointmentId and b.RowStatus = 1 JOIN Type d (nolock) ON a.Type = d.TypeId LEFT JOIN AppointmentForm e (nolock) ON e.Ap...
Hello, I am executing the following code (names changed to protect the innocent, so the model structure might seem weird): memberships = models.Membership.objects.filter( degree__gt=0.0, group=request.user.get_profile().group ) exclude_count = memberships.filter( member__officerships__profile=req...
What I have is basically a problem which is easily solved with multiple tables, but I have only a single table to do it. Consider the following database table UserID UserName EmailAddress Source 3K3S9 Ben [email protected] user SF13F Harry [email protected] 3rd_party SF13F Harry [email protected] user 76D...
For simplicity, assume all relevant fields are NOT NULL. You can do: SELECT table1.this, table2.that, table2.somethingelse FROM table1, table2 WHERE table1.foreignkey = table2.primarykey AND (some other conditions) Or else: SELECT table1.this, table2.that, table2.somethingelse FROM table1 INNER JOIN table2 ...
I need help converting this query to linq to sql: select rd.RouteDispatchID, r.RouteNumber, s.ShortDescription Status, rd.DispatchDate, rd.CreationDate CreatedDate, e.FirstName, e.LastName, count(md.MachineDispatchID) NumMachines from dbo.RouteDispatch rd inner join dbo.Route r on rd.RouteID = r.Route...
I have an Order class that has_many :shipments. How can I use Order.find to return all the order objects whose newest shipment was created after a certain time (say, in the last hour)? ...
I have a list that I want to print: foo: list of string; I want to create a string bar that is the concatenation of the elements of foo. In Perl I would do: $bar = join " ", @foo; The only way I can think of to do this in specman is: var bar: string = ""; for each in foo { bar = appendf("%s %s", bar, it); }; This seems like...
This should be simple enough, but somehow my brain stopped working. I have two related tables: Table 1: ID (PK), Value1 Table 2: BatchID, Table1ID (FK to Table 1 ID), Value2 Example data: Table 1: ID Value1 1 A 2 B Table 2: BatchID Table1ID Value2 1 1 100 2 1 101 3 1 102 1 ...
Here is the situation,each page will show 30 topics,so I had execute 1 sql statements at least,besides,I also want to show how many relpies with each topic and who the author is,thus I have to use 30 statements to count the number of replpies and use other 30 statements to find the author.Finally,I got 61 statements,I really worry about ...
I cannot figure out this complex join.. I have 3 tables: I want to join threads id with posts thread_id and users id with posts poster and threads poster.. Threads: ID int(10) Subject varchar(100) Message text Poster int(10) Posts: ID int(10) Poster int(10) Message text() Thread_id int(10) Users: ID int(10) username varchar(...
In our database, a file is "Processing" if the column "Pending" is set to false on the FileTable and that files FileID doesn't exist in the Transaction table. How can I construct a LINQ query that basically says, where f.Pending == False && (f.ID !=exist in db.Transactions.FileID) The portion in the parenthesis is what I'm not sure ho...
I have some tables that benefit from many-to-many tables. For example the team table. Team member can hold more than one 'position' in the team, all the positions are listed in the position db table. The previous positions held are also stored for this I have a separate table, so I have member table (containing team details) positions...
Hello, I'm new to SobSonic and trying to create a join query for two tables. I found the options LeftInnerJoin, which generate the query that seems valid. When trying to run it on MSSQL 2005, it does not work since LEFT INNER JOIN is not valid, but LEFT JOIN. and I'm still looking for way to generate query for joining mutiples tables an...
Ruby on Rails is very new to me. I am trying to retrieve set of columns from 3 different tables. I thought I could use SQL view to retrieve my results but could not find a way to use views in Rails. Here are my tables. 1) User table --> user name, password and email 2) UserDetails table --> foreign key: user_id, name, address1, city etc....
Hi there! I have a problem that I can't figure out myself. I've tried using LEFT JOIN etc but nothing seems to work. I'm using MySQL so you know. I'm building a little blogportal for me and my friends and all users have their own blog. database: users: id, username, password, etc blog: id, title, text, user_id, etc relations fo...