join

How to limit a LINQ left outer join to one row

I have a left outer join (below) returning results as expected. I need to limit the results from the 'right' table to the 'first' hit. Can I do that somehow? Currently, I get a result for every record in both tables, I only want to see one result from the table on the left (items) no matter how many results I have in the right table (pho...

Python join, why is it string.join(list) instead of list.join(string)?

This has always confused me. It seems like this would be nicer: my_list = ["Hello", "world"] print my_list.join("-") # Produce: "Hello-world" Than this: my_list = ["Hello", "world"] print "-".join(my_list) # Produce: "Hello-world" Is there a specific reason it does it like this? ...

LIMITing an SQL JOIN

Hi there, I am trying to limit the following SQL statement. SELECT expense.*, transaction.* FROM expense INNER JOIN transaction ON expense_id = transaction_expense_id What I want to do, is limit the number of 'parent' rows. IE. if I do a LIMIT 1, I would receive only one expense item, but still get all transactions associated with it....

How do I join the most recent row in one table to another table?

I have data that looks like this: entities id name 1 Apple 2 Orange 3 Banana Periodically, a process will run and give a score to each entity. The process generates the data and adds it to a scores table like so: scores id entity_id score date_added 1 1 10 1/2/09 2 2 ...

mysql join on two fields

I have two tables with date and id fields, and i need to join on both fields. I tried [code] JOIN t2 ON CONCAT(t1.id, t1.date)=CONCAT(t2.id, t2.date) [/code] that works, but it is very slow. is there a better way to do this? ...

Selecting multiple values different tables

Hi, I'm relatively new to MySql. I have 2 tabled with the following structure products { pid-autoincrement, pname p_desc } services { sid-autoincrement s_name s_desc } Im trying to select the products or services which have the name '%<somekeyword>%' I'm using the query: SELECT DISTINCT products.*, services.* ...

Linq-to-sql One-To-Many with a max

I'm having a heck of a time with this one. Might be the lack of sleep... or maybe I'm just getting dumb. I have 2 tables: a) Person {Key, Name, LastName} b) LogEntry {Key, PersonKey, Log, EntryTime} I'm trying to get a join of Person and LogEntry where LogEntry is the latest LogEntry for the given Person. I hope I don't go "duh..." i...

When to use STRAIGHT_JOIN with MySQL

I just had a fairly complex query I was working with and it was taking 8 seconds to run. EXPLAIN was showing a weird table order and my indexes were not all being used even with the FORCE INDEX hint. I came across the STRAIGHT_JOIN join keyword and started replacing some of my INNER JOIN keywords with it. I noticed considerable speed imp...

NHibernate - join without mapping

Hi, Is it possible to join two classes without specified mapping between them (using Criteria API)? I must join two classes and retrieve data from both but I can't mapping them. I know only foreign key SomeID in the first class and primary key ID in second. How to create Criteria to join them? Is it possible without mapping? Please, ...

sql left join and duplicates in result

Say I have 2 tables, A and B, each A entity can possibly have multiple B entities, in one case if I want to get all B's of some certain A's, I might do it with a simple left join select A.id aid,B.id bid from A left join B on B.aid = A.id where A.id = 1 and it will return a result set like aid bid 1 1 1 2 1 3 As you...

SQL "ON" Clause Optimization

Which query would run faster? SELECT * FROM topic_info LEFT JOIN topic_data ON topic_info.id = topic_data.id WHERE id = ? or SELECT * FROM topic_info LEFT JOIN topic_data ON topic_data.id = topic_info.id WHERE id = ? The difference is the order of expressions on the "ON" clause: the first query is checking topic_info.id aga...

"Distinct" results from SQL query using a mappings table

Hi, I have two tables tbl1 and tbl2, both with two columns, one for id and one for product. I want to extract the rows that are in both, i.e. rows where tbl1.id = tbl2.id and tbl1.product = tbl2.product and join the row from tbl1 and tbl2 into one row. I imagine this goes something like this: SELECT tbl1.\*, tbl2.\* FROM tbl1, tbl2 WH...

Relating mismatched column in an xsd?

I am using VS 2008 and trying to create a dataset for ms sql reporting services. I have two tables that I want to relate. One has a column with type of int, the other shortint. VS give me an error of "Parent and Child columns don't have type-matching columns." Is there any way to over ride this? In old sql reporting services I could...

MySQL correlated subquery in JOIN syntax

I would like to provide a WHERE condition on an inner query by specifying innertable.id = outertable.id. However, MySQL (5.0.45) reports "Unknown column 'outertable.id' in 'where clause'". Is this type of query possible? The inner query is pivoting rows to columns using a GROUP BY. This could be entirely be performed in the outer query,...

Display mysql join/union results differently based on table

Hello, I need to generate a list of activity from multiple tables in order of the date they were entered into the tables, but when i spit them out (with php), i want each result to look specific to a design that i have for each table. ie...i have the tables: listings, photos, comments -the results from listings should be designed...

MySQL: Finding rows that don't take part in a relationship

I have two tables: 'movies' and 'users'. There's an n:m relationship between those, describing what movies a user has seen. This is described with a table 'seen' Now i want to find out for a given user, all the movies he has not seen. My current solution is like this: SELECT * FROM movies WHERE movies.id NOT IN ( SELECT seen.movie...

How to optimize query looking for rows where conditional join rows do not exist?

I've got a table of keywords that I regularly refresh against a remote search API, and I have another table that gets a row each each time I refresh one of the keywords. I use this table to block multiple processes from stepping on each other and refreshing the same keyword, as well as stat collection. So when I spin up my program, it qu...

Retrieving All records - Inner join

Hi, I'm a bit of a database novice, so pardon my naivety here. I have following sql statement: SELECT DISTINCT dbo.tb_user.familyName, dbo.user_email.email FROM dbo.tb_user INNER JOIN dbo.user_email ON (dbo.tb_user.id = dbo.user_email.userID) This returns records where an email address exists. What I would like to do is retrieve a...

Is it possible to make ActiveRecord create objects for rows loaded using the :joins option?

I need to do something like this class User < ActiveRecord::Base has_many :abuse_reports end class AbuseReport < ActiveRecord::Base belongs_to :abuser, :class_name => 'User', :foreign_key => 'abuser_id' belongs_to :game end class Game < ActiveRecord::Base has_many :abuse_reports end @top_abusers = User.page(params[:page], ...

LINQ joins and ADO.NET Data Services

I have a Windows Service which exposes three sources of data via ADO.NET Data Services. These sources of data are read only XML files loaded into an XDocument and then exposed via .AsQueryable(); The sources contain fields with integer IDs which can be considered to be 'foreign keys' between the data sources. My client consumes this dat...