join

Oracle joins ( left outer, right, etc. :S )

I knew stackoverflow would help me for other than know what is the "favorite programming cartoon" :P This was the accepted answer by: Bill Karwin Thanks to all for the help ( I would like to double vote you all ) My query ended up like this ( this is the real one ) SELECT accepted.folio, COALESCE( inprog.activityin, accep...

Left Join outperforming Inner Join?

I've been profiling some queries in an application I'm working on, and I came across a query that was retrieving more rows than necessary, the result set being trimmed down in the application code. Changing a LEFT JOIN to an INNER JOIN trimmed the result set to just what was needed, and presumably would also be more performant (since le...

MySQL LEFT JOIN SELECT not selecting all the left side records?

I'm getting odd results from a MySQL SELECT query involving a LEFT JOIN, and I can't understand whether my understanding of LEFT JOIN is wrong or whether I'm seeing a genuinely odd behaviour. I have a two tables with a many-to-one relationship: For every record in table 1 there are 0 or more records in table 2. I want to select all the ...

SQL Builder for PHP, with JOIN support?

Hi, Are any of you aware of a library that helps you build/manipulate SQL queries, that supports JOIN's? It would give a lot of flexibility i'd think if you have something where you could return an object, that has some query set, and still be able to apply JOIN's to it, subqueries and such. I've search around, and have only found SQL...

Help with a WHERE on a LEFT JOIN SQL Query

I'm trying to construct a query that will include a column indicating whether or not a user has downloaded a document. I have a table called HasDownloaded with the following columns: id, documentID, memberID. Finding out whether a user has downloaded a specific document is easy; but I need to generate a query where the results will look ...

What's the C# method/syntax for converting an array to a simple string?

What I'm looking for is a basic equivalent of JavaScript's Array::join() whereby you pass in a separator character and uses that in its return string of all the subscripts. I could certainly write my own function using a StringBuilder or whatnot, but there must be something built into the .NET BCL. EDIT: Array of anything, not necessar...

What are the uses for Cross Join?

A cross join performs a cartesian product on the tuples of the two sets. SELECT * FROM Table1 CROSS JOIN Table2 Which circumstances render such an SQL operation particularly useful? ...

SQL update from one Table to another based on a ID match

Hi All I realy hope someone can help me with this. I have a databse with Account Numbers and card Numbers that I match to a file to update any card numbers to account number so I only work with account numbers. I created a view linking the Table to the Account/Card db to return the Table ID and the related Account number. Now I need to ...

In What Order are MySQL JOINs Evaluated

I have the following query: SELECT c.* FROM companies AS c JOIN users AS u USING(companyid) JOIN jobs AS j USING(userid) JOIN useraccounts AS us USING(userid) WHERE j.jobid = 123; I have the following questions: Is the USING syntax synonymous with ON syntax? Are these joins evaluated left to right? In other words, does this query sa...

MySQL Joins

My table structure looks like this: tbl.users tbl.issues +--------+-----------+ +---------+------------+-----------+ | userid | real_name | | issueid | assignedid | creatorid | +--------+-----------+ +---------+------------+-----------+ | 1 | test_1 | | 1 | 1 | 1 | | 2...

JPA / Hibernate Select Column Subset on Join

In SQL it is easy to do a join and only return the columns you want from the joined table. What is the best way to map this in JPA / Hibernate? For example, there is a Folder entity mapped to the EMAIL_FOLDER and an Email entity mapped to the EMAIL table. There is a one-to-many relationship from Folder to Email. The Email entity is rath...

Speed up sql JOIN

First of all, some background. We have an order processing system, where staff enter billing data about orders in an app that stores it in a sql server 2000 database. This database isn't the real billing system: it's just a holding location so that the records can be run into a mainframe system via a nightly batch process. This batch...

Getting distinct records from a mysql query

In my application, there are publishers and categories. One publisher can belong to several categories. When I make my mysql transaction, it will return the same publisher record for each category it belongs to. Here's the query: SELECT grdirect_publisher.name, grdirect_publisher.short_description, grdirect_publisher.thum...

MVC LINQ to SQL Table Join Record Display

Hey all, Im having problems displaying records to my view when passing viewdata to a user control. This is only apparent for linq to sql objects where I am using table joins. The exception I receive is "Unable to cast object of type '<>f__AnonymousType410[System.String,System.Int32,System.Nullable1[System.DateTime],System.String,Syste...

query join question

I have a right outer join, that almost does what I want... SELECT users_usr.firstname_usr, users_usr.lastname_usr, credit_acc.given_credit_acc, users_usr.created_usr, users_usr.sitenum_usr, users_usr.original_aff_usr, users_usr.id_usr FROM credit_acc right Outer Join users_usr ON credit_acc.uid_usr = users_usr.id_usr The problem is, I...

A strange SQL join

Simplified for example, I have two tables, groups and items. items ( id, groupId, title ) groups ( id, groupTitle, externalURL ) The regular query I'm goes something like this: SELECT i.`id`, i.`title`, g.`id` as 'groupId', g.`groupTitle`, g.`externalURL` FROM items i INNER JOIN groups...

Cross table join using MYSQL rather than MSSQL

Is it possible to do a cross table join in mysql spaning different tables? in different databases. This seem to be easily possible in MSSQL, and greatly speeds up data transfer? How about mysql, do you need to use a powerful IDE to achieve this? or do you have to write a program to do something like this? UPDATE tblUser SET tblUser.R...

Bug in SQLite self-join on id?

I'm trying to generate pairwise combinations of rows on based on their ids. SQLite version is 3.5.9. The table contents are the following: id|name|val 1|A|20 2|B|21 3|C|22 with table schema being: CREATE TABLE mytable ( id INTEGER NOT NULL, name VARCHAR, val INTEGER, PRIMARY KEY (id) ); Then there's the self-jo...

MySQL Joining Problems

The following query returns strange results for me: SELECT `Statistics`.`StatisticID`, COUNT(`Votes`.`StatisticID`) AS `Score`, COUNT(`Views`.`StatisticID`) AS `Views`, COUNT(`Comments`.`StatisticID`) AS `Comments` FROM `Statistics` LEFT JOIN `Votes` ON `Votes`.`StatisticID` = `Statistics`.`StatisticID` LEFT JOIN `Views`...

Is there a disadvantage to using "USING" instead of "ON" in MySQL?

Two snippets of MySQL: SELECT * FROM annoyingly_long_left_hand_table LEFT JOIN annoyingly_long_right_hand_table ON annoyingly_long_left_hand_table.id = annoyingly_long_right_hand_table.id; vs SELECT * FROM annoyingly_long_left_hand_table LEFT JOIN annoyingly_long_right_hand_table USING (id); Given that both...