join

Optimize multiple joins with table functions

I would like to join several times with the same table function for different input variables in the same query. But this turns in my case out to be much slower than using table variables and selecting from the table functions separately. How can I avoid table variables and still have a fast query? For example, we have a SQL query like...

left join with special condition on right table

don't know if this is possible.. I'm using sqlite3 schema: CREATE TABLE docs (id integer primary key, name string); CREATE TABLE revs (id integer primary key, doc_id integer, number integer); I want to select every job joined with only one of its revisions, the one with the highest number. How can I achieve this? Right now I'm doing a ...

MYSQL MyIsaM How to Join 2 statement select + select count

table: postid|userid|post|replyto post sql SELECT * FROM table WHERE postid=12 total replies sql SELECT COUNT(*) AS total FROM table WHERE replyto=12 the expected result is the "post table" + how many replies to the post. replyto field is the target postid. somehing like : postid|userid|post|replyto|totalreplies Is there a possibi...

Rails: Can joins be merged when chaining scopes?

In a class A I have two scopes, s1 and s2 which both join over a table T using the exact same join columns: named_scope :s1 :joins => "JOIN T on T.id = A.t_id", ...some conditions named_scope :s2 :joins => "JOIN T on T.id = A.t_id", ...some other conditions Now, doing this fails: A.s1.s2.all Error: ActiveRecord::StatementInvali...

How to make LEFT JOIN work with grouping/count in MySQL?

I have a query like this: SELECT type.id, type.name, COUNT(*) AS tot FROM page LEFT JOIN type ON page.type=type.id GROUP BY type.id However, this doesn't select all the types: it misses out any types that are not in the page table yet. I just want it to list every type with a number representing how many pages have that type, includin...

MySQL: Use 2 keys, one to join, one to sort?

I have a simple where clause on a keyed field, a join between two tables on a different field, and then a sort on another field of the second table. I have keys on everything, but it seems that the describe is giving me a filesort and a 30 second query execution : mysql> describe SELECT * FROM `alias` LEFT OUTER JOIN `aliaspoint` ON (`...

SQL JOIN+COUNT QUERY

Hi, I'm trying to display a table showing a list of courses. I would like to include a column which shows the number of people who have completed the course and if possible, also the number who have yet to complete it. My table structure is like so: Courses courseid | title | etc Studying courseid | studentid | some other fields | ha...

Rails ActiveRecord :joins with LEFT JOIN instead of INNER JOIN

I have this code User.find(:all, :limit => 10, :joins => :user_points, :select => "users.*, count(user_points.id)", :group => "user_points.user_id") which generates following sql SELECT users.*, count(user_points.id) FROM `users` INNER JOIN `user_points` ON user_points.user_id = users.id GROUP BY u...

Java: function for arrays like PHP's join() ?

I want to join a String[] with a glue string. Is there a function for this? ...

What kind of SQL join is this?

Say for some reason I have employees in two separate tables, employee1 and employee2 I just want to add them together, as if they are stacked on top of each other. something like: select all from employee1 and employee2 where name = bubba i know im generalizing, this will be in postgres eventually so if there are any specifics there...

Hibernate default joining for nullable many-to-one

Hi all, I have a hibernate mapping like this in a ProductDfn class @ManyToOne( fetch = FetchType.LAZY, optional = true ) @JoinColumn( name = "productTypeFk", nullable = true ) public ProductType getProductType() { return productType; } Note that the relationship is defined as optional (and the column is nullable). When doing HQL...

SQL command for conditional join for PROGRESS database

Please bear with me new to SQL- I am trying to write an SQL command with a join in a PROGRESS db. I would like to then select only the first matching record from the join. I thought to use LIMIT but PROGRESS does not support that. MIN or TOP would also work I think but having trouble with the syntax. Something like this?- SELECT table1....

SQL command for PROGRESS database

Please bear with me new to SQL- I am trying to write an SQL command with a join in a PROGRESS db. I would like to then select only the first matching record from the join. I thought to use LIMIT but PROGRESS does not support that. MIN or TOP would also work I think but having trouble with the syntax. Here is current syntax: SELECT esth...

How to use join in an HQL update?

I know how to join tables in an SQL update but how do I do it in HQL? Long story: I have items which I process in a run. Each run as an ID and I have a many-to-many relation between items and runs (which is in an extra table). Now I want to set the state of all items used in a certain run. The naive approach looks like this: update It...

Iterating 1 row at a time with massive amounts of links/joins

Ok, basically what is needed is a way to have row numbers while using a lot of joins and having where clauses using these rownumbers. such as something like select ADDRESS.ADDRESS FROM ADDRESS INNER JOIN WORKHISTORY ON WORKHISTORY.ADDRESSRID=ADDRESS.ADDRESSRID INNER JOIN PERSON ON PERSON.PERSONRID=WORKHISTORY.PERSONRID WHERE PERSONRID...

How do constant values effect the ON clause of Joins?

I've recently discovered that the ON clause of a LEFT JOIN may contain values such as (1 = 1). This is upsetting to me, as it breaks my perception of how joins function. I've encountered a more elaborate version of the following situation: SELECT DISTINCT Person.ID, ... FROM Person LEFT JOIN Manager ON (Manager.ID = Person.ID OR Mana...

SQL join puzzler

Given pricetable sym(k) pricedate(k) price msft 1/2/2009 33 msft 1/3/2009 34 msft 1/4/2009 35 ibm 1/2/2009 66 ibm 1/3/2009 65 ibm 1/4/2009 64 and datestable pricedate(k) 1/1/2009 1/2/2009 1/3/2009 1/4/2009 I would like to get this result set sym pricedate price msft 1/1/2009 null...

Join 3 tables in 1 LINQ-EF

I have to fill warehouse table cOrders with program using Ado.NET EF. I have SQL command but i don't know how to do this with LINQ. static void Main(string[] args) { var SPcontex = new PI_NorthwindSPEntities(); var contex = new NorthwindEntities(); dCustomers dimenzijaCustomers = new dCusto...

fetch=join to tables to one class NHibernate

I have read some post about fetch=join - http://nhforge.org/blogs/nhibernate/archive/2009/04/09/nhibernate-mapping-lt-many-to-one-gt.aspx (ser4ik.livejournal.com/2505.html) So I have some question, Forexample I have class <class name="AttributesInf" table="attr_inf"> <id name="Id"> <generator class="identity"/> </id> <proper...

Tricky SQL query

Hello guys! Really tricky SQL statement I have here. Trying to build this query for about hour. Maybe You can help me. We have a table with 3 columns: gamename | user | times_played The query should select top three games (depending total times_played) and top three user who has played most times in this game => 9 rows. The result is...