inner-join

SQL: inner join on alias column

Hi all, Previously I have asked to strip text from a field and convert it to an int, this works successfully. But now I would like to do an INNER JOIN on this new value. So I have this: SELECT CONVERT(int, SUBSTRING(accountingTab.id, PATINDEX('%[0-9]%', accountingTab.id), 999)) AS 'memId', userDetails.title, userDetails.lname FROM ...

Inner join across multiple access db's

I am re-designing an application for a ASP.NET CMS that I really don't like. I have made som improvements in performance only to discover that not only does this CMS use MS SQL but some users "simply" use MS Access database. The problem is that I have some tables which I inner join, that with the MS Access version are in two different f...

Using DISTINCT inner join in SQL

I have three tables, A, B, C, where A is many to one B, and B is many to one C. I'd like a list of all C's in A. My tables are something like this: A[id, valueA, lookupB], B[id, valueB, lookupC], C[id, valueC]. I've written a query with two nested SELECTs, but I'm wondering if it's possible to do INNER JOIN with DISTINCT somehow. SELE...

LINQ inner join betwenn Enumerable and DB Table

I'm trying to determine which records to delete from a database when a user submits a form. The page has two CheckBoxList one representing the records before modification and one after. I can easily get the selected values that need to be deleted like this... //get the items not selected that were selected before var oldSelectedItems =...

Interesting Many-many sql join.

I have three related tables "A(id, val)", "B(id, val)", and a link table with a value "AB(aid, bid, val)" I am querying against B to bring back A values, for example: SELECT A.* FROM A INNER JOIN AB ON A.id = AB.aid INNER JOIN B ON AB.bid = B.id WHERE B.val = 'foo'; Every A has many B's and every B has many A's. And the catch that ...

MySQL correlated subquery

Having difficulty articulating this correlated subquery. I have two tables fictitious tables, foo and bar. foo has two fields of foo_id and total_count. bar has two fields, seconds and id. I need to aggregate the seconds in bar for each individual id and update the total_count in foo. id is a foreign key in bar for foo_id. I've tried ...

Is seven inner joins in a query too much?

I have a query that has 7 inner joins (because a lot of the information is distributed in other tables), a few coworkers have been surprised. I was wondering if they should be surprised or is having 7 inner joins normal? ...

LINQ To SQL Weird Join Issue

I have a simple database with two tables. Users and Configurations. A user has a foreign key to link it to a particular configuration. I am having a strange problem where the following query always causes an inner join to the Configuration table regardless of the second parameter value. As far as I can tell, even though the "UserConfigu...

SQL Select statement with Inner Join help

Hi, Here is my SQL Statement which is not returning DISTINCT Thread Titles. SELECT DISTINCT TOP 5 tblThread.Title, tblPost.Date FROM tblPost INNER JOIN tblThread ON tblPost.ThreadID = tblThread.ThreadID ORDER BY tblPost.Date DESC The common field between tblThread and tblPost is ThreadID. What I want this to do is return The late...

What's wrong with my MySQL query?

I'm not getting any errors as such just a minor performance issue. EXPLAIN SELECT a.nid, a.title, a.uid, b.parent, b.weight, c.name, d.value FROM table1 AS a INNER JOIN table2 AS b ON a.vid = b.vid AND a.status = 1 INNER JOIN table3 AS c ON c.uid = a.uid INNER JOIN table4 AS d ON d.content_id = a.nid AND d.value_type = 'percent' AND d.f...

How do you combine an inner and outer join in mysql

I can do it in sybase and I can do it in oracle, but I'm not seeing how to do it in mysql. I've got this: (please restrain yourself from re-formatting my sql, last time somebody did that they changed it so it wasn't the same and made the question meaningless) select table1.id from table1 inner join table2 on (table1.id = table2.i...

Why doesn't this SQL statement work?

SELECT * FROM table1, table2 WHERE table1.user_id = table2.id AND table1.content = news AND table1.content_id = 1 that wont work. cant u have two "AND" in a sql statement?? //Tomek ...

Inexplicable inner query

Hi, I have a nested SQL query that is exhibiting results which I can't understand. The query joins the PARTNER and USER tables via the PARTNER_USER table. A partner is basically a collection of users, and the objective of this query is to figure out when the 20th user registered with the partner that has ID 34: select p.partner_id id, ...

MySQL join query help

Basically I have a mysql query something like the following: mysql_query("SELECT n.title, v.value FROM posts n INNER JOIN votes v ON n.id = v.id"); And what I need to do is to grab the title from the posts table and the current vote value from the votes table. At the moment, votes aren't stored into the votes table unless a vote happ...

SQL products/productsales

A common (i assume?) type of query: I have two tables ('products', 'productsales'). Each record in 'productsales' represents a sale (so it has 'date', 'quantity', 'product_id'). How do i efficiently make a query like the following: Retrieve the product names of all products that were sold more than X times between date Y and date Z. (...

SQL query : inner joins optimization between big tables

Hello, I have the 3 following tables in a MySQL 4.x DB : hosts: (300.000 records) id (UNSIGNED INT) PRIMARY KEY name (VARCHAR 100) paths: (6.000.000 records) id (UNSIGNED INT) PRIMARY KEY name (VARCHAR 100) urls: (7.000.000 records) host (UNSIGNED INT) PRIMARY KEY <--- links to hosts.id path (UNSIGNED INT) PRIMARY KEY <--- links ...

Can anyone show me why my SQL query isn't working (see details)?

I used the following query to find duplicates: SELECT userID, COUNT(userID) AS NumOccurrences FROM userDepartments GROUP BY userID HAVING ( COUNT(userID) > 1 ) I then tried adding an inner join so I could see the user names that match, which are stored in a different table. SELECT userDepartments.userID, users.firstname, users.lastna...

optimize SQL query

What more can I do to optimize this query? SELECT * FROM (SELECT `item`.itemID, COUNT(`votes`.itemID) AS `votes`, `item`.title, `item`.itemTypeID, `item`. submitDate, `item`.deleted, `item`.ItemCat, `item`.counter, `item`.userID, `users`.name, TIMESTAMPDIFF(minute,`submitDate`,NOW()) AS 'timeMin' , ...

How to use INNER JOIN in the scenario?

I have 2 tables: 'Users' Table id username ---- -------- 0001 user1 0002 user2 0003 user3 0004 user4 'Friends' Table user_id friend_id friend ------- --------- ------ 0001 0004 1 0002 0004 1 0005 0004 0 How do I display all user4 friends'...

Mysql order by rating - but capture all

Hi, I have this PHP/MYSQL code which returns records from a table ordered by their ratings, from highest rated to lowest rated: <table width="95%"> <tr> <?php if (isset($_GET['p'])) { $current_page = $_GET['p']; } else { $current_page = 1; } $cur_category = $_GET['category']; $jokes_per_page = 40; $offset = ($current...