join

Problem implementing Join(...) extension method

I am trying to use the Join(...) extension method to build a query based on criteria passed to a method. I have an error in the following: public static IQueryable QueryItems(string param1, string param2, string param3) { IQueryable<tbl_Item> query = dataMap.tbl_ItemMap; //Join is giving me the error: Cannot implicitly convert ...

Rails problems with Join search. Please help!

Hello, I am developing a small user application in Rails 3 and is stuck on a search feature. I got a table called Profile which holds Firstname and Lastname. When searching this table I use: Profile.find_all_by_firstname(params[:keyword]) The thing is that I want the user to be able to search a table that holds contacts (profile_id a...

MySQL Join with 3 tables and empty rows

I'm giving up for now with my JOIN creating a view for search reasons - I need help :/ Here are my tables: Profiles id company user_id 1 ACME 2 2 Joe 4 3 Wolf 5 Users id role_id online 2 4 2010-10-08 4 2 2010-10-08 5 4 2010-10-08 Rubrics id title 1 Stee...

Cakephp "joins" destroys conditions?

A bug has a state and hasandbelongs to many usergroups among other things. Now, when listing the bugs I used the pagination helper and also give the user the ability to filter it by various setting. Works great so far. You can filter it by project, you can filter it by state (via the state_id property of the bug) and by several other it...

How to select from two tables in MySQL even if not all rows in one table have corespondents in the other?

I have two tables for an online shop: one for categories: id, title one for products: id, ownerid, title, price (ownerid is the id of the parent category) I want to select all the categories and also select the minimum and maximum price in each, hence the following query: SELECT sc.*, MIN(s.price) AS minp, MAX(s.price) AS maxp FROM ...

Combine many joins into one join?

There are two tables - incoming tours(id,name) and incoming_tours_cities(id_parrent, id_city) where id_parrent is id from first table. Here is the query i wrote SELECT t.cities FROM `incoming_tours` t JOIN `incoming_tours_cities` tc0 ON tc0.id_parrent = t.id AND tc0.id_city = '1' JOIN `incoming_tours_cities` tc1 ON tc1.id_parrent = t.i...

LINQ double left join

I've got a students class with a name, a blogEntries class with a student name and entry title, and a blogAssignments class with an assignment name. I'd like to show ALL students and alongside them the blogEntries they created whose title matches the blogAssignments.assignmentName. I've got the following LINQ query but I can't figure o...

Order of tables in join query

I found this paragraph in Oracle documentation if you want to select the name of each department along with the name of its manager, you can write the query in one of two ways. In the first example which follows, the hint /++ordered++/ says to do the join in the order the tables appear in the FROM clause with attempting...

Need help in optimising query.

I have two tables - incoming tours(id,name) and incoming_tours_cities(id_parrent, id_city) id in first table is unique, and for each unique row from first table there is the list of id_city - s in second table(i.e. id_parrent in second table is equal to id from first table) For example incoming_tours |--id--|------name-----| |---1--|...

mysql join question, joining newest record in a one to many relationship.

Hi all, I have a simple (hopefully) SQL question for you, any help would be much, much appreciated :). I have two tables which I would like to join. One Is Users, lets say it's called users One is a kind of history of that user, lets say its called users_history. The relationship of these two is a one users to many users_history rel...

MySQL join, empty rows in junction table

I have three tables I'd like to join in a way that produces all records from one table and any matching records or NULL from another table. It is imperative that all records from the first table be returned. I thought I had done this before but I can't remember when or where and MySQL just isn't playing ball. SELECT VERSION(); 5.0.51a-3...

How to access joined table attributes in Rails3 ?

I am having trouble accessing the attributes of a join in Rails3. There is two models/tables : Place and Address. One place can have many addresses (i.e. a specific street address, a "corner of" address, etc.). For historical reasons, the tables do not follow standard Rails conventions: class Place < ActiveRecord::Base set_table_name ...

Doctrine: Complex JOIN problem

I have many-2-many relation between Property and Feature. I have to extract results from Property table, but only if both or more Features are related. Ie. Property #1 is related to 'Swimming Pool' and 'Garden'. Property #2 is related only to 'Swimming Pool'. So when I ask for properties that has both 'Swimming Pool' and 'Garden', it sh...

How can I speed up MySQL query with multiple joins

Hello all, Here is my issue, I am selecting and doing multiple joins to get the correct items...it pulls in a fair amount of rows, above 100,000. This query takes more than 5mins when the date range is set to 1 year. I don't know if it's possible but I am afraid that the user might extend the date range to like ten years and crash it....

Optimizing join query to fetch data from A with condition on B sorted by B

I have an item-to-item similarity matrix set up with these tables: items (id, ...) (Primary key `id`) similarities (item1_id, item2_id, similarity) (Index on `item1_id` and `item2_id`) The similarities tables contains pairs of ids with a similarity index, i.e: item1_id item2_id similarity 1 2 0.3143 2 3 ...

Unix sort is not consistent!

Hi i'm running the commands zcat [File] | sed "1d" | sort -t $'\xE7' -k [field to be sorted] > [file].sorted When i run this on File A sorting on field 1 i get 11622400 , abe, def 11622401 , abe, def 11622402 , bbabe, def 11622403 , ddabe, def 11622404 , acdc, dere 11622405 , ddabe, bere 11622406 , abe, fgh 11622407 , adbed, ddee 11...

mysql optimisation

Hi guys, I have a photo gallery with 1.5 million photos so we are talking a big database. We want to make it so that people once they have uploaded a photo can upload into several different areas of the site. Photos also have attributes and tags and we have a tables for that as well. here is the layout PHOTO TABLE photo_id image status...

SQL JOIN problem in MS Access 2007

SELECT DISTINCT bw.Bor_name FROM Borrower AS bw, Loan AS l JOIN Book_Copy AS bc ON l.Bc_id = bc.Bc_id WHERE bw.Bor_id = l.Bor_id GROUP BY l.Bor_id, bc.Bt_id HAVING COUNT( bc.Bt_id ) > 1 AND COUNT( l.Bor_id ) > 1; This works perfectly in a MySQL testing environment but won't work in MS Access 2007 where I actually need it run. I have a ...

Create join table with no primary key

I have two tables with a many to many relationship that I am using has_and_belongs_to_many to define the association. class Foo < ActiveRecord::Base ... has_and_belongs_to_many :bar ... end class Bar < ActiveRecord::Base ... has_and_belongs_to_many :foo ... end I also have the class defined to represent the join table cl...

howto get php PDO QUERY resultset in single array instead off nested in one

I needed to change a query into a JOIN of two tables. $q = "SELECT * FROM table1 AS a JOIN table2 AS b USING(id) WHERE a.id= $id"; $stmt = db::getInstance()->prepare($sql); return $stmt->fetchAll(PDO::FETCH_ASSOC); All off the sudden I cannot refer to each row value with $rows['value'] ,BUT I need to use $rows[0]['value']. How can I ...