join

"ambiguous column name" in SQLite INNER JOIN

I have two tables in a SQLite DB, INVITEM and SHOPITEM. Their shared attribute is ItemId and I want to perform an INNER JOIN. Here's the query: SELECT INVITEM.CharId AS CharId, INVITEM.ItemId AS ItemId FROM (INVITEM as INVITEM INNER JOIN SHOPITEM AS SHOPITEM ON SHOPITEM.ItemId = INVITEM.ItemId) ...

Transact-SQL shorthand join syntax?

I have noticed a few times when working on legacy code, that you can do left and right outer joins in sql by using the =* as kind of shorthand for "right outer join" and *= as kind of shorthand for "left outer join" in statements like this: select table1.firstname, table2.lastname from table1, table2 where table1.id *= table2.id ...

SQL query performance question (multiple sub-queries)

Hi, I have this query: SELECT p.id, r.status, r.title FROM page AS p INNER JOIN page_revision as r ON r.pageId = p.id AND ( r.id = (SELECT MAX(r2.id) from page_revision as r2 WHERE r2.pageId = r.pageId AND r2.status = 'active') OR r.id = (SELECT MAX(r2.id) from page_revision as r2 WHERE r2.pageId = r.pageId) ) Which...

Aggregation with two Joins (MySQL)

Hi! I have one table called gallery. For each row in gallery there are several rows in the table picture. One picture belongs to one gallery. Then there is the table vote. There each row is an upvote or a downvote for a certain gallery. Here is the (simplified) structure: gallery ( gallery_id ) picture ( picture_id, picture_gallery_re...

Simple Mysql table join problem

I'm designing a website to teach myself how to use table joins in mysql and have got stuck pretty early on. (The numbers are Wii friend codes, if that helps you make sense of this!) I have 3 tables: users: id, firstname, surname games: id, title numbers: number, users_id, game_id The number is a unique code that belongs to the...

Join two sql queries

I have two SQL queries, where the first one is: select Activity, SUM(Amount) as "Total Amount 2009" from Activities, Incomes where Activities.UnitName = ? AND Incomes.ActivityId = Activities.ActivityID GROUP BY Activity ORDER BY Activity; and the second one is: select Activity, SUM(Amount) as "Total Amount 2008" from Activities...

Difference between JOIN and INNER JOIN

Both these joins will give me the same results: SELECT * FROM table JOIN otherTable ON table.ID = otherTable.FK vs SELECT * FROM table INNER JOIN otherTable ON table.ID = otherTable.FK Is there any difference between the statements in performance or otherwise ? Does it differ between different SQL implementations ? ...

Dynamic/Conditional SQL Join?

I have data in a MSSQL table (TableB) where [dbo].tableB.myColumn changes format after a certain date... I'm doing a simple Join to that table.. Select [dbo].tableB.theColumnINeed from [dbo].tableA left outer join [dbo].tableB on [dbo].tableA.myColumn = [dbo].tableB.myColumn However, I need to join, using different formatting, bas...

SQL problem, LEFT JOIN [..] IN()

This small SQL error is bugging me. It doesn't seem to be a problem with the query, just the scope(?), examples work best: SELECT ocp.*, oc.*, GROUP_CONCAT( u.username SEPARATOR ', ') AS `memjoined` FROM gangs_ocs_process ocp, gangs_ocs oc LEFT JOIN users u ON u.userid IN ( ocp.membersin ) WHERE ocp.ocid =1 AND ocp.gangid =1 AND oc.oc_n...

Are all these SQL joins logically equivalent?

Hi, I'm just wondering if all of the following joins are logically equivalent, and if not, why not? SELECT t1.x, t2.y from t1, t2 where t1.a=t2.a and t1.b=t2.b and t1.c = t2.c; SELECT t1.x, t2.y from t1 join t2 on t1.a=t2.a where t1.b=t2.b and t1.c = t2.c; SELECT t1.x, t2.y from t1 join t2 on t1.a=t2.a and t1.b=t2.b where t1.c = t2.c...

Complex join with nested group-by/having clause?

I ultimately need a list of "import" records that include "album" records which only have one "song" each. This is what I'm using now: select i.id, i.created_at from imports i where i.id in ( select a.import_id from albums a inner join songs s on a.id = s.album_id group by a.id having 1 = count(s.id) ); The nested sele...

Finding the T-SQL to return these values

I'm trying to write a stored procedure that will return two calculated values for each record according to the rules below, but I haven't figured out how to structure the SQL to make it happen. I'm using SQL Server 2008. First, the relevant tables, and the fields that matter to the problem. ProductionRuns RunID (key, and RunID is giv...

joins on ints vs joins on chars (or varchars)

Are there any performance drawbacks in SQL to joining a table on a char (or varchar) value, as opposed to say joining on an integer value? ...

Using sql JOIN...

I'm a sql noob trying to get this query to use 2 tables. tables & columns are: person: department_id, name, etc... department: department_id, dept_name, etc... I have a 'select' html form that the user will choose a dept_name from, and I need my php script to return every person with a matching department_id. ...

Can you update using a LIKE operator on JOIN w/ TSQL?

I'm doing a large import from a comma delimited file and want to lookup an employer id during the sproc that is executed at the end of the import process. The issue I'm having is that my LIKE doesn't seem to work ... so I wanted to see if the syntax is correct. Note - This will update all the records = the employer name but anything LI...

MySQL statement combining a join and a count?

I've got a table of 'folders'. I want to return all the records with the userId of 16. SELECT * FROM `folders` WHERE userId = 16; I've got a table of 'files'. For each 'folder' returned above, I want to return a count of 'files' within that 'folder'. SELECT COUNT(*) as "Files" FROM files WHERE Folder = n; How do I combine these? I'...

How do you perform an AND with a join?

I have the following data structure and data: CREATE TABLE `parent` ( `id` int(11) NOT NULL auto_increment, `name` varchar(10) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1; INSERT INTO `parent` VALUES(1, 'parent 1'); INSERT INTO `parent` VALUES(2, 'parent 2'); CREATE TABLE `other` ( `id` int(11) NOT NUL...

How to convert a SQL subquery to a join

I have two tables with a 1:n relationship: "content" and "versioned-content-data" (for example, an article entity and all the versions created of that article). I would like to create a view that displays the top version of each "content". Currently I use this query (with a simple subquery): SELECT t1.id, t1.title, t1.con...

How do I join tables on an update

I have the following query: select count(L.ID) from LA inner join L on (LA.leadid = L.ID) where L.status = 5 and L.city = "cityname" and Date(LA.Datetime) < Date_Sub(Now(), INTERVAL 6 MONTH); which looks for records with status 5 in a particular city that are older than 6 months (the date for which is...

SQL Query joins

Hi, I have a table called tblIssueTicket tblIssueTicket contains the fields: TicketID, TicketRequesterID, ApprovalManagerID, RequestDate, ApprovalDate, TicketStatus There is another table called tblEmployeeProfile. tblEmployeeProfile contains fields EmployeeID, EmployeeFirstName, EmployeeLastName I need to display the following records...