join

Symfony Jobeet: Need to refactor index page DB queries

I use Symfony 1.4 with Doctrine, and I noticed that my project has similar issue with Jobeet project at index page (http://www.symfony-project.org/jobeet/1_4/Doctrine/en/06). The problem is on index page the system shows to user jobs per category and we have 1+2*N database queries, where N - categories count. This isn't very good, if ...

Entity Framework - How do I join tables on non-primary key columns in secondary tables?

I want to join 2 tables using entity framework. I want the join to the second table to be on a non-primary key column. e.g. I have a table Foo with fields Foo.Id (PK) Foo.DbValue and table Bar Bar.Id (PK) Bar.DbValue Bar.Description And I want to join Foo to Bar in EF on the DbValue field. In hibernate/nhibernate one can do this...

How to join two collections by index in LINQ

What could be a LINQ equivalent to the following code? string[] values = { "1", "hello", "true" }; Type[] types = { typeof(int), typeof(string), typeof(bool) }; object[] objects = new object[values.Length]; for (int i = 0; i < values.Length; i++) { objects[i] = Convert.ChangeType(values[i], types[i]); } ...

sql query to join 2 tables and show all records from 1 column

i have two tables in sql server - one with 51 american states, and the other with name,state. the table with name state has different records namely - Seere -- AK Seere -- LA Seere -- CA John -- HI John -- MA I want a query that picks up one name say "Seere" and shows all the states from state table, and the name attached to those s...

php & mySQL: Selecting Multiple Books and showing their info in next screen

Hi all, Let me illustrate this with an example. Consider there are 10 books (there may be more, even 100+ on a single screen). A customer can order any number of books. In the fontend, each book is represented in a row and the book details are arranged column wise. Now the 10 books (along with their details) are shown in 10 rows. BOO...

join lists based on common head or tail

What is the fastest way to solve the following I will to join several lists based on common head or tail input = ([5,6,7], [1,2,3], [3,4,5], [8, 9]) output = [1, 2, 3, 4, 5, 6, 7] ...

How can I SELECT the names of all groups a person belongs to using a JOIN instead of IN?

Consider the following simplified example: CREATE TABLE groups ( gid INTEGER PRIMARY KEY, name VARCHAR(100) ); CREATE TABLE people ( pid INTEGER PRIMARY KEY ); CREATE TABLE people_groups ( gid INTEGER NOT NULL CONSTRAINT fk_people_groups_group REFERENCES groups(gid), pid INTEGER NOT NULL CONSTRAINT fk_p...

SQL join against date ranges?

Consider two tables: Transactions, with amounts in a foreign currency: Date Amount ========= ======= 1/2/2009 1500 2/4/2009 2300 3/15/2009 300 4/17/2009 2200 etc. ExchangeRates, with the value of the primary currency (let's say dollars) in the foreign currency: Date Rate ========= ======= 2/1/2009 ...

Shuold I use not exists or join statement to filter out NULLs?

SELECT * FROM employees e WHERE NOT EXISTS ( SELECT name FROM eotm_dyn d WHERE d.employeeID = e.id ) And SELECT * FROM employees a LEFT JOIN eotm_dyn b on (a.joinfield=b.joinfield) WHERE b.name IS NULL Which is more efficient,some analysis? ...

Using max and join together in linq using c#

I have a master-detail tables and I want to get list of master join with detial where detail is max in some filed. for example I have a table named Document and also a child table named Revision .I want to get list of document join Revision where Revision filed is max ? One solution is: using ( ProcurementDataContext dc = new Procurem...

How to use Join in Zend Framework.

Hello, i am using Join query in zend.. like $select = $table->select() ->from(array('e' => 'EducationHistory'), array('status_DataDictionary_id')) ->join(array('r' => 'ReportOrder'), 'e.id = r.EducationHistory_id', ...

SQL query to show difference from the same table

My application has a table that contains snapshot inventory data from each year. For example, there's a vehicle inventory table with the typical columns vehicle_id, vehicle_plate_num, vehicle_year, vehicle_make, etc, but also the year designating that the vehicle is owned. Querying the entire table might result in something like this: ...

rails HABTM joins and inconveniences

I have a working HABTM association between the models Posts and Users... the posts_users table is as advertised and both have the necessary has_and_belongs_to_many in their model.rb And if I use User.find(1).posts it will find all the posts with the valid username My question is how to deal with this situation. I want to use user.po...

T-SQL query to find missing IDs for a date range

Given these tables table Channel -------------- ChannelID int IDENTITY <other irrelevant stuff> table Program -------------- ProgramID int IDENTITY ChannelID int AiringDate datetime <other irrelevant stuff> and this query SELECT C.ChannelID, t.AiringDate FROM Channel C LEFT JOIN ( SELECT distinct ChannelID FROM Pr...

Using Include() with inherited entities problem

In EF eager loading related entities is easy. But I'm having difficulties including inherited entities when loading data using table-per-type model. This is my model: Entities: ArticleBase (base article entity) ArticleSpecial (inherited from ArticleBase) UserBase (base user entity) UserSpecial (inherited from UserBase) Image R...

Joining 2 SQL SELECT result sets into one

Hi. I've got 2 select statements, returning data like this: Select 1 col_a col_b Select 2 col_a col_c If I do union, I get something like col_a col_b And rows joined. What i need is getting it like this: col_a col_b col_c Joined on data in col_a. ...

Need help with Join Problem

Hi I have 2 tables Table 1 ID Status 1 D 2 F 3 D Table 2 SID ID Approve 1 1 N 2 1 Y 3 1 Y 4 2 Y 5 3 Y Result:- Should be Table 1 (ID, Status) 3, D 2,F Should not display 1 (As one of child rows have N in the Approve Column) I need a query to joins 2 tables on ID and finds records that don not hav...

Linq to SQL - return record details from two tables

Hey, I'm having some trouble with a linq query. It's a simple problem, but I'm not sure what the best way to approach it would be. I have two tables (I'm only showing relevant fields) called Artist and Song: ARTIST - int ArtistID (pk) varchar Name SONG - int SongID (pk) uniqueidentifier UserID (To lookup songs the use...

SQL: Having a table twice in the FROM clause

I am using MySQL. Here is my schema: Suppliers(sid: integer, sname: string, address string) Parts(pid: integer, pname: string, color: string) Catalog(sid: integer, pid: integer, cost: real) (primary keys are bolded) I am trying to write a query that selects pairs of sids that supply the same part: -- Find pairs of SIDs that both su...

Why join is faster than normal concatenation

Hi, I've seen several examples from different languages that unambiguously prove that joining elements of a list(array) is times faster that just concatenating string. Unfortunately I didn't find an explanation why? Can someone explain the inner algorithm that works under both operations and why is the one faster than another. Here is...