join

Is there an alternative to joins to increase performance?

Is there an alternative to joins to increase performance? Edit (gbn): related to join-or-correlated-subquery-with-exists-clause-which-one-is-better Why didn't anyone mention about nested loop joins? ...

problem using aggregate functions with multipul table joins

My goal is to make an aggregate function (sum) and group the elements , but there is an error this is all the steps that i have done 1- first step code SELECT ca.question_id , ca.choice_0 ,ca.choice_1 ,ca.choice_2 ,ca.choice_3 ,ca.choice_4 ,q.headline_id FROM closed_answers ca INNER JOIN questions q ON ca.question_id...

LINQ to SQL Insert Failing because of Join

I've got two tables in my database Users and HealthMonitor. In the HealthMonitor table I have a UserID field that is mapped to the ID field in the Users table. Pretty straight forward so far... I left the UserID field in the HealthMonitor table as "Nullable" so that I can have the system insert a NULL value into the table if there is ...

Doctrine : Hydrate doctrine collection with another model than the FROM clause

Hello, On my application, i frequently do doctrine query like this : $coms = Doctrine_Core::getTable('Comment') ->createQuery('c') ->join('c.article a') ->join('a.Writter w') ->where('w.something = ?', $something); I want to extract the comments from articles with a condition on the writter. Le query start from the Comment Table (...

Access count on another table doesn't work

I have a Posts table and PostComments table of a blog system. I want to count and sort the posts by comment count but my query won't work.: SELECT Posts.PostID, Posts.DateCreated, Posts.Title, Posts.Description, Posts.Hits, (SELECT Count(CommentID) FROM PostComments WHERE PostComments.PostID=Posts.PostID AND PostComments.IsApproved=Tru...

Joining multiple columns from table a to one on table b

I've spent a bit of time researching this on here and the mysql site but I'm a bit confused on two things: which sort of join to use and how (or if) to use an alias. The query: SELECT forum_threads.id, forum_threads.forum_id, forum_threads.sticky, forum_threads.vis_rank, forum_threads.locked, forum_threads.lock_rank, forum_threads.aut...

Need to order by properties in the reference property, any good solution?

Say I have 2 kind: class Account(db.Model): name = db.StringProperty() create_time = db.DataTimeProperty() last_login = db.DateTimeProperty() last_update = db.DataTimeProperty() class Relationship(db.Model) owner = db.ReferenceProperty(Account) target = db.ReferenceProperty(Account) type = db.IntegerProperty() I want...

MySQL performance, inner join, how to avoid Using temporary and filesort

Hi, I have a table 1 and table 2. Table 1 PARTNUM - ID_BRAND partnum is the primary key id_brand is "indexed" Table 2 ID_BRAND - BRAND_NAME id_brand is the primary key brand_name is "indexed" The table 1 contains 1 million of records and the table 2 contains 1.000 records. I'm trying to optimize some query using EXPLAIN and after a l...

MySQL Join Query

I need to create a join query for the following: Table supplier: id name Table supplier_vehicles id supplier_id vehicle_id A supplier can have multiple vehicles. On my front-end form I have a checkbox list - a user can select multiple vehicles. The back end script needs to do a search and bring back all suppliers that contain a...

SQL: Select records where ALL joined records satisfy some condition

How can I write an SQL query that returns a record only if ALL of the associated records in a joined table satisfy some condition. For example, if A has many B, I want to SELECT * FROM A WHERE all related B's for a given A have B.some_val > value I know this is probably a pretty basic question, so thanks for any help. Also, if it makes...

Left Join not working (MySQL)

I have a table Books, where I store Book data (ISBN's, Titles, Authors, etc.). To tell which books are editions of each other I have a field Edition_Group_ISBN, which is an arbitrary ISBN from the group. I'm having trouble getting this query, which is supposed to give the Book data and the number of other Editions based on the ISBN, to...

MySQL Join Query (possible two inner joins)

I currently have the following: Table Town: id name region Table Supplier: id name town_id The below query returns the number of suppliers for each town: SELECT t.id, t.name, count(s.id) as NumSupplier FROM Town t INNER JOIN Suppliers s ON s.town_id = t.id GROUP BY t.id, t.name I now wish to introduce another table in to th...

Manipulating Order of JOINS in CakePHP

I have the following problem with CakePHP: In my model, Deposit belongsTo Account, and Account belongsTo Customer. When querying Deposits, I get the Account information, but not the Customer's, by default. If I set Deposit->recursive to 2, I get the Customer information (and a whole lot more), but CakePHP esentially throws one SEL...

SQL: need only 1 row per particular timestamp

i have some SQL code that is inserting values from another (non sql-based) system. one of the values i get is a timestamp. i can get multiple inserts that have the same timestamp (albeit different values for other fields). my problem is that i am trying to get the first insert happening every day (based upon timestamp) since a parti...

ActiveRecord::Relation join, how to add a column of a join table to the query result with a new name?

To set the stage, I'm using rails 3 and I have these tables and relationships: user has_many lists list has_many tasks task has_many stints I would like to build a query that allows me to select all of the current users stints, and to have the list.id available as an attribute on each stint in the result. I would need to rename list.i...

joining views with previous left joins and not multiplying rows

I have 2 views of the form: --View1 SELECT foo.id AS id FROM foo LEFT JOIN bar ON foo.id = bar.id --Results id 1 1 1 2 2 ... --View2 SELECT foo.id AS id FROM foo LEFT JOIN manchu ON foo.id = manchu.id --Results id 1 1 1 2 2 ... Now I want to join the two views so that row #1 from View1 is joined to row #1 of View2. If I...

mysql right join with group by problem

I have 2 tables: LandingPages - contain landing pages per campaign. Reports - contain hits and conversion per landing page. I try to do query that bring the sum of hits and conversion per landing page, But i want that if the landing page has not received any hits and conversion (and not show in reports table) then i want that return...

MySQL JOIN with 3 tables and COUNT() not working

Hi, I'm having a problem with my MySQL statement. I need a query that counts the number of comments and the number of topics a user has created. My table structure is something like this: Table 'users' ------------- user_id user_name ... Table 'topics' -------------- topic_id topic_user_id ... Table 'topiccomments' -----------------...

Joining a NSArray of objects into a string, but need to be abke to specify property

I have a NSArray of Foo objects. @interface Foo : NSObject { } - (NSString *) name; @end I want to be able to join all these [Foo name] results into one NSString. In C# I would get a array of these by using LINQ, creating a Array of it, and feeding it to String.Join(): List<Foo> foo = [..]; String.Join(",", foo.select(F => ...

Optimize SQL Query

Hi, I have following query on a MySQL DB: SELECT * , r.id, x.real_name AS u_real_name, u.real_name AS v_real_name, y.real_name AS v_real_name2 FROM url_urlaube r LEFT JOIN g_users u ON ( r.v_id = u.id ) LEFT JOIN g_users x ON ( r.u_id = x.id ) LEFT JOIN g_users y ON ( r.v_id2 = y.id ) WHERE ( ( FROM_UNIXTIME( 1283205600 ) >= r.from AN...