join

Why is my join on a JavaScript array failing?

I have a function that accepts multiple arguments (only one is defined in the function though) var getSearchFields = function(method) { console.log(arguments); // this prints them out nicely into the console var args = arguments; var argsString = args.join('/'); // I expect 'arg1/arg2/arg3', instead I get 'args.join is no...

mysql: How to INNER JOIN a table but limit join to 1 result with the highest vote or count?

I have 2 tables. One is items and another is votes for those items. Items table has: |item_id|name|post_date Votes table has: |votes_id|item_id|answer|total_yes|total_no What I want to do is show all items based on post_date and show the answer in the votes table with the HIGHEST total_yes. So I want to show only a SINGLE answer from ...

Alias table name to facilitate 3 column join table (MySQL or PostgreSQL)

This is a separate question, but it is related to an earlier question: Three Column Join in Rails with Active Scaffold. To sum up: Rails automatically looks for a two column join table, but it doesn't do the same for a three column join table. I've tried the suggestions in the previous question, but it comes down to this: If you have ...

SQL Select subquery

I'm a little new to SQL and have come across the following problem. I have a table with company details on it which is joined to a contact table by an enqID. Within the contact table, there are 4 different types of contacts which may or may not have an entry. These are differentiated by a ctcTypID (1 - 4) I would like to produce a qu...

Why does this SQL code give error 1066 (Not unique table/alias: 'user')?

This is my table structure: The error message is: #1066 - Not unique table/alias: 'user' The following is my code. SELECT article.* , section.title, category.title, user.name, user.name FROM article INNER JOIN section ON article.section_id = section.id INNER JOIN category ON article.category_id = category.id INNER JOIN user ON...

Linq to SQL: Multi-table join return type not generated by dbml

Hello, I'm trying to figure out the best way to handle a simple problem: I have a simple LINQ join to two tables. I know how to return the type for one table, since it is the same as the generated dbml class. However, what if I want to return data from both tables- isn't there a way to return both and use their relationships? Do I rea...

Can a single LINQ Query Expression be framed in this scenario?

I am facing a scenario where I have to filter a single object based on many objects. For sake of example, I have a Grocery object which comprises of both Fruit and Vegetable properties. Then I have the individual Fruit and Vegetable objects. My objective is this: var groceryList = from grocery in Grocery.ToList() fr...

SQL Join Problem... what is it called?

Hi, I have two tables that are joined. I've included the 'output' of the joined tables. I'm wondering what is this join problem called? There are duplicating rows. Table 1          Table 2 ID     Name     ID Name 1     Joey         4 Mary 2     Shawn      5 Xavier 3     Mark         6 Gary The join output is: ...

In which cases can INNER JOIN (SELECT ... not be rewritten using temp table

I am using GridSQL where I get some performance problems whenever the SQL pattern INNER JOIN (SELECT arises. I am therefore considering rewriting all these queries into two queries, one creating a temporary table using the exact select statement and the other query joining with the temporary table, so the pattern would be INNER JOIN temp...

Linq query to exclude from a List when a property value of List of different type are equal?

I have a List of type Fee from which I need to exclude the ones that have an ID that exists in another List of type int. List<int> ExcludedFeeIDs = new List<int>{1,2,3,4}; List<Fee> MyFees = (from c in ctx.Fees select c).ToList(); Example: List GoodFees = (from f in ctx.Fees where f.FeeID!=One of the IDs in Exclu...

Getting data from two different tables into one single table?

I have the following two tables: uid | ABC | ... (bunch of other stuff) ... 1 | val 1 | ... (bunch of other stuff) ... 2 | val 2 | ... (bunch of other stuff) ... 3 | val 3 | ... (bunch of other stuff) ... and... uid | DEF | ... (bunch of other stuff) ... 4 | val 4 | ... (bunch of other stuff) ... 5 | val 5...

Conditional Joins in LINQ

I have a parent child table relationship. In the example below Foo has a FooID and a nullable ParentFooID that points to a parent record. The Bar table is always linked to the parent record. This is the SQL I use to get the result. Select * from Foo f JOIN Bar b ON b.FooID = CASE WHEN f.ParentFooID is null TH...

Yet another Join Question

I want to pull back information about a loan. One piece of information is a certain fee amount. If I simplify my query down to the loan number and fee amount, I still can not figure it out. The first query returns what I expect, one loan number and a 0 for the fee amount (fee was not applied) while the second one I can not get to work...

Join with conditions and aggregate functions

Hi, I have a table in which there are records about works accesing entrance doors. DECLARE @doorStatistics TABLE ( id INT IDENTITY, [user] VARCHAR(250), accessDate DATETIME, accessType VARCHAR(5) ) Sample records: INSERT INTO @doorStatistics([user],accessDate,accessType) VALUES ('John Wayne','2009-09-01 07:02:43.000','IN') INSERT IN...

Zend_Db _Table_Abstract and Left Joins

Is there a way to do a left join within a Zend_Db_Table_Abstract based model. I would like to retrieve a record with a left join to another table so that I am able to query all the fields as if they were the same table. Any examples would be useful Thanks guys ...

C# Silverlight - Defining a join between database tables?!!?

Currently I have a c# silverlight business application. The application is hosted in Asp.net using the ADO.Net entity framework and a domain service class to read/write to/from my sql server database. The structure of the database is as follow. I have three tables. Job, Audit & Image. One Job can have many Audits, and One Image can have...

How to build HQL query, thats joins subtables marked LAZY, automatically?

I have some entity: public class Album extends GenericAuditedEntity { @OneToMany(fetch = FetchType.LAZY) private Set<Item> itemSet = new HashSet<Item>(); } And when i run HQL like this: em.createQuery("select a from Album a").getResults() it produses many SQL queries: One for select data from Album's table. Smth like thi...

ActiveRecord: Clever Join and Include

Hi all, I am trying to get ActiveRecord to perform the following query: SELECT A.*, B.*, C.* FROM A INNER JOIN B ON B.ID = B_ID INNER JOIN C ON C.ID = C_ID The dataset is rather large, and I need as the best performance, hence this specific query. I have my models and query as follows: class A < ActiveRecord::Base belongs_to :b ...

SQL to show all entries within a number of categories

Hi, I'm writing an sql query on three tables which finds and displays categories and all the entries within each category For example Category 1 post 1 post 2 post 3 post 4 Category 2 post 5 post 6 Category 3 post 7 post 8 etc I have the categories displaying but can only get one item from each. Can anyone suggest a better approac...

rails include with options

Is it possible to limit an AR :include to say only pull in one record... Item.find(:all, :include => [ :external_ratings, :photos => LIMIT 1 ]) I have a list of items and each item has between 5 and 15 photos. I want to load a photo id into memory, but i don't need all of them, I just want to preview the first one. Is there a ...