join

Sql query with joins between four tables with millions of rows

We have a transact sql statement that queries 4 tables with millions of rows in each. It takes several minutes, even though it has been optimized with indexes and statistics according to TuningAdvisor. The structure of the query is like: SELECT E.EmployeeName , SUM(M.Amount) AS TotalAmount , SUM(B.Amount) AS BudgetAmount ...

SQL update with a join?

I have two tables. One is simple string/ID look up: StrTable: str_key String 0 'a' 1 'b' where the strings are unique. The other is more complex, and includes the shared string_id ValTable: str_key other_key val 0 0 1.234 0 1 1.567 1 0 1.890 Now, I want to do an update on Va...

SQL: 3 self-joins and then join them together

I have 2 tables to join in a specific way. I think my query is right, but not sure. select t1.userID, t3.Answer Unit, t5.Answer Demo FROM table1 t1 inner join (select * from table2) t3 ON t1.userID = t3.userID inner join (select * from table2) t5 ON t1.userID = t5.userID where NOT EXISTS (SELECT * FROM ...

Is it possible to convert this query to use a join instead of a subquery?

SELECT number, count(id) FROM tracking WHERE id IN (SELECT max(id) FROM tracking WHERE splitnr = 'a11' AND number >0 AND timestamp >= '2009-04-08 00:00:00' AND timestamp <= '2009-04-08 12:55:57' GROUP BY ident) GROUP BY number ...

SQl Delete from more than one table

Say, I have two tables Courses and faculty_courses - each has a primary key course_ID that is varchar(50) not null. I am trying to delete a row from the Courses table - so assume have to delete that row from both tables since they are linked by a relationship. I wrote this - doesn't work - says Incorrect syntax near the keyword 'JOIN'...

convert sql to linq help please

select m.messageID,m.[addeddate],m.message,count(*) as Comments from intranet.dbo.Blog_Messages AS m inner join intranet.dbo.Blog_Comments AS c on c.messageid = m.messageid group by m.messageID,m.[addeddate],m.message need help converting this to linq to sql ...

MySQL Join Where Not Exists

I have a MySQL query that joins two tables Voters Households they join on voters.household_id and household.id Now what i need to do is to modify it where the voter table is joined to a third table called elimination, along voter.id and elimination.voter_id, how ever the catch is that i want to exclude any records in the voter table ...

Linq2sql Find Top Subscribers Question

Hello all, It has been a while since I have wrote any code due to military obligations (Afghanistan, Reserves) and I have a question with regard to linq 2 sql (hell, I would do this as a stored proc at this point...I really am rusty). I have a table of feed names, and those feeds have subscribers in another table (foreign key associatio...

Mixing implicit and explicit JOINs

I am having a problem with Hibernate generating invalid SQL. Specifically, mixing and matching implicit and explicit joins. This seems to be an open bug. However, I'm not sure why this is invalid SQL. I have come up with a small toy example that generates the same syntax exception. Schema CREATE TABLE Employee ( employeeID INT, name...

Ruby on Rails: How to join two tables

I have an index page that I want to show all the users' profile and their associated photos. I'm using the plugin Paperclip for the photos. In the Profiles controller, I have the instance variable @profile but it shows me the table in the profiles table only and not the photos table. @profile = Profile.find(:all, :include => :photos, ...

SQL to join one table to another table multiple times? (Mapping products to categories)

Let's say I have a Product, Category, and Product_To_Category table. A Product can be in multiple categories. Product Category Product_to_category ID | NAME ID | Name Prod_id | Cat_id ===================== ============ =================== 1| Rose ...

SQL: finding double entries without losing the ID

I have 1 table "Products" that looks like this: ID Product Shop Color 01 Car A Black 02 Car B Black 03 Bike C Red 04 Plane A Silver 05 Car C Black 06 Bike A Red In this example, a Product always has the same color, independent ...

How do I make an UPDATE while joining tables on SQLite ?

I tried : UPDATE closure JOIN item ON ( item_id = id ) SET checked = 0 WHERE ancestor_id = 1 Then : UPDATE closure, item SET checked = 0 WHERE ancestor_id = 1 AND item_id = id Both works with MySql but gives me a syntax error in SQLite. How can I make this UPDATE / JOIN works with SQLite version 3.5.9 ? ...

What joins does SQLite support?

According to the join-op syntax, SQLite has 13 distinct join statements: , JOIN LEFT JOIN OUTER JOIN LEFT OUTER JOIN INNER JOIN CROSS JOIN NATURAL JOIN NATURAL LEFT JOIN NATURAL OUTER JOIN NATURAL LEFT OUTER JOIN NATURAL INNER JOIN NATURAL CROSS JOIN Are they all unique? Which are equivalent? ...

SSIS mismatched Join

I have two sets of data which I need to join, but there is an added problem because the data quality is not great. The two data sets are Calls (phone calls) and Communications (records created about phone calls). They have ID's called call_id and comm_id respectively. The communication records also have call_ids to perform the join. The...

MSSQL JOIN ON GROUP BY is too slow

I have the following query in MSSQL SELECT TOP 50 CustomerID FROM Ratings WHERE CustomerID != 915 AND MovieID IN (SELECT DISTINCT MovieID FROM Ratings WHERE CustomerID = 915) GROUP BY CustomerID ORDER BY count(*) DESC It is super fast. When I try to use it in a subquery like this. SELECT * FROM Ratings WHERE MovieID = 1 AND CustomerI...

How to iterate over joined data in two or more nested loops?

Some time ago I asked a question about nested loops on SO and as it was, there were queries inside the loops of my example and I got a clear answer: NEVER EVER NEVER put an SQL query inside a loop I've tried ever since and mostly it works. Just need to make an effort and write a query that retrieves all you need at once. BUT what ...

Compare 2 values of different types inside of subquery

I am using a MS SQL db and I have 3 tables: 'base_info', 'messages', 'config' bases: ID Name NameNum ==================================== 1 Home 101 2 Castle 102 3 Car 103 messages: ID Signal RecBy HQ ============================ 111 120 Home 1 111 110 Castle 1 111 125 Car...

Join 2 lists by order instead of condition in LINQ

How can I join 2 lists of equal lengths (to produce a 3rd list of equal length) where I do not want to specify a condition but simply rely on the order of items in the 2 lists. Eg how can I join: {1,2,3,4} with {5,6,7,8} to produce: {{1,5}, {2,6}, {3,7}, {4,8}} I have tried the following: from i in new []{1,2,3,4} from j in new [...

Rails has_and_belongs_to_many join across 3 tables

I have a three models: listing, category, and site. There is a many to many relationship between listing and site and there is a many to many relationship between listing and category. A listing thus belongs to one or more sites and one or more categories (a listing can appear on multiple sites and multiple categories). Given a site i...