join

Efficient Pagination from Join in Django

Hi. I've got a problem here with a join and pagination. I have 3 Models: Pad Tag TagRelation I use Tag relation to manage a ManyToMany relationship betweent Pads and Tags (with the through attribute of te ManyToMany field). Now when i do a join on the Pad and Tag tables it gives me something like this... pad.name tag.name etc ...

NHibernate Joining on non-mapped property

Hi, I have two classes SystemInvitation and User. User has a property called Email and SystemInvitation has a property called InviteesEmailAddress. There is no relationship in the domain between these properties. Is it possible using the Criteria API to produce a query like: select si.InviteesEmailAddress , si.Identifier ...

Joining tables based on the maximum value

Here's a simplified example of what I'm talking about: Table: students exam_results _____________ ____________________________________ | id | name | | id | student_id | score | date | |----+------| |----+------------+-------+--------| | 1 | Jim | | 1 | 1 | 73 | 8/1/09 | | 2 | Joe | ...

combining linq queries

Hi, i have a couple of linq queries which I wanted to join var IDs = from p in ctx.bam_Zending_AllInstances where p.Zender == "RT30" select new { Identificatie = p.Identificatie }; var latestIDs = from p in ctx.bam_Zending_AllInstances w...

Missing rows in outer join have zero value for float column instead of null - why?

I'm using Microsoft's SQL Server 2000 and have a query that is essentially this (except that Z is a further subquery, not a table): select A.key1, A.key2, B.value1 from A left join (select * from Z where value1 > 0) B on A.key1 = B.key1 and A.key2 = B.key2 order by A.key1, B.key1 Column value1 is of type float. For some reas...

SQL Server optional joins

I have several tables (to be exact, 7) tables I cross join in one another. This part gives me some problems; Table "Actions" ----------------------------------------- | ID | Package ID | Action Type | Message | ----------------------------------------- | 40 | 100340 | 0 | OK | | 41 | 100340 | 12 | Erro...

Help with a MySQL join

I have a Skills table which has 14 records in it. It is structured like this: | id | name | I then have a User_Skills table which I want to join to the skills table. Here's is structure: | User_id | Skill_id | Basically I want to be able to do a join which always results in 14 records and shows NULL in any fields the user isn't...

I need help with a Rails ActiveRecord Join - I know how to do it in SQL

I have 2 tables I need to join but the user_id column value is not the same in both tables. So I want to do something like this: In my controller 4 will be substituted with current_user.id select * from sites join pickups on sites.id = pickups.site_id where sites.user_id = '4' But using an ActiveRecord Find. Here are my association...

MySQL Join from multiple options to select one value

I am putting together a nice little database for adding values to options, all these are setup through a map (Has and Belongs to Many) table, because many options are pointing to a single value. So I am trying to specify 3 option.ids and a single id in a value table - four integers to point to a single value. Three tables. And I am runn...

SQL Join vs Separate Query in Code without Join - Performance

I would like to know if there's a really performance gain between those two options : Option 1 : I do a SQL Query with a join to select all User and their Ranks. Option 2 : I do one SQL Query to select all User I fetch all user and do another SQL Query to get the Ranks of this User. In code, option two is easier to realize for...

SQL join that displays left table even if right table doesn't match where

I have 2 tables, defined as such: CREATE TABLE `product` ( `pid` SMALLINT( 5 ) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, `name` VARCHAR( 50 ) NOT NULL, `description` TEXT, `qty` SMALLINT( 5 ) UNSIGNED NOT NULL DEFAULT '0', `category` ENUM( '1', '2', '3', '4', '5', '6', '7', '8' ) NOT NULL DEFAULT '1', `price` DECIMAL( 7, 2 ) UNSIGNE...

How to create a mysql query for the following?

I have the following query: SELECT m.*, COUNT(c.chapter_nr) as chapters, MAX(c.chapter_nr) as latest_chapter FROM comic_series AS m JOIN chapters as c on c.comic_id = m.id WHERE m.title_en = 'test' This allows to me to find the chapters from a comic book by just giving the name of the comic. The query selects all the columns from the ...

Update a table with records from another table

I am trying to update my table 1 with some field value from table 2 based on a condition. But am unable to get the proper query. Here's the condition: I have a table 1 with date field which should be updated from table 2 date field. The condition is that the id of table 1 should be equal to id of table 2 (Table 2 has id of table 1 as FK...

Comparing SQL Table to itself (Self-join)

I'm trying to find duplicate rows based on mixed columns. This is an example of what I have: CREATE TABLE Test ( id INT PRIMARY KEY, test1 varchar(124), test2 varchar(124) ) INSERT INTO TEST ( id, test1, test2 ) VALUES ( 1, 'A', 'B' ) INSERT INTO TEST ( id, test1, test2 ) VALUES ( 2, 'B', 'C' ) Now if I run this query: SELE...

How might I improve the execution time of my join-heavy query?

I am joining multiple tables in a MySQL query, and the execution of this query is very slow. I badly need to improve the execution time! I did some optimization, but still it loads slowly. Any suggestions? ...

What does this SQL code (joining on the same view twice) do?

I'm working my way through this explanation of Common Table Expressions at MSDN and I came across the code reproduced below. Can anyone explain to me what it does and how it does it? I'm pretty sure I understand the concept of the view that is created in the first half. But the SELECT that joins on that view twice is confusing me. CREA...

MySQL join problem

I want to join a pages table and menu table. CREATE TABLE IF NOT EXISTS `pages` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(255) NOT NULL DEFAULT '', `keywords` varchar(255) NOT NULL DEFAULT '', `description` varchar(255) NOT NULL DEFAULT '', `path` varchar(255) NOT NULL DEFAULT '', `content` text NOT NULL, `sta...

Linq, emulating joins, and the Include method

Hello, I'm looking into an issue that is related to... http://stackoverflow.com/questions/416847/join-and-include-in-entity-framework Basically the following query returns the list of "Property" objects the current user has permissions ( ACLs ) to view. IQueryable<Property> currPropList = from p in ve.Property ...

C# WPF SQL SELECT INNER JOIN

Im trying to join a user table to retrieve the users login name. I wish to have TWO INNER joins one for CreatedByUser_loginname and ModifiedByUser_loginname But at the moment Im just trying to get the SQL query string syntax right. BUT, When I change the name of the INNER JOIN with an AS 'name' I get an exception thrown when sqlreader()...

Understanding join()

Suppose a thread A is running. I have another thread, B, who's not. B has been started, is on runnable state. What happens if I call: B.join()? Will it suspend the execution of A or will it wait for A's run() method to complete? ...