join

mysql newbie - joining

I have a table of movies and a table of votes. Users vote for movies they like. I need to display the list of movies descending on the total votes for the movie. What I have now sort of works. The only problem is it doesn't show movies with 0 votes. SELECT m.name, m.imdb_url, m.comment, COUNT(v.movie_id) AS votes FROM movies m, votes v ...

Left Joins that link to multiple rows only returning one

I'm trying to join two table (call them table1 and table2) but only return 1 entry for each match. In table2, there is a column called 'current' that is either 'y', 'n', or 'null'. I have left joined the two tables and put a where clause to get me the 'y' and 'null' instances, those are easy. I need help to get the rows that join to r...

Java: How to use Thread.join

I'm new to threads. How can I get t.join to work, whereby the thread calling it waits until t is done executing? This code would just freeze the program, because the thread is waiting for itself to die, right? public static void main(String[] args) throws InterruptedException { Thread t0 = new Thready(); t0.start(); } @Override pu...

LINQ to Entities Group By Driving Me Crazy

I have a LINQ query written in VB.Net that uses Joins to return records from a relational database. I am trying to add a GROUP BY statement, but whichever way I cut it, I either get an error or Visual Studio Intellisense does not allow my format. This is what my query looks like without the GroupBy: Public Function GetBadges(ByVal ser...

Avoiding union by join?

Hi, My problem is on Oracle, but is probably database independent (?). I have the following tables: aa vid cb --- -- 1 10 2 15 bb vid cb --- -- 3 25 4 24 *rep repid vid p ----- --- -- 99 1 aa 99 2 aa 99 3 bb 99 4 bb The column p indicates in which table to get the row....

What is wrong with this JOIN query?

I have a list of tenant reports, each link pointing to a report ID. I need this query to select the report information, and some of the information related to the tenant who submit it. This query gets the report information correctly, but returns copies of it with every tenant attached to it: SELECT reports.person_reporting, reports....

How to write this challenging SQL (MySQL) command?

This is the scenario: I am developing a website which is similar to stackoverflow.com. After an asker/seeker posts his question, other users can post their answers to the question. A user can post more than one answer to the same question, but usually only the latest answer will be displayed. User can give comments on an answer, if comme...

Oracle: Check if rows exist in other table

I've got a query joining several tables and returning quite a few columns. An indexed column of another table references the PK of one of these joined tables. Now I would like to add another column to the query that states if at least one row with that ID exists in the new table. So if I have one of the old tables ID 1 2 3 and th...

table joins in Access 2003 on date-time field

I need to join my data table to a server calendar file (has set fiscal dates, periods, etc.). I don't get any results and believe it is because my table has date/time like "11/23/2009 11:30:34AM" and the calendar file only has date/time like "11/23/2009", so the fields are not identical. current SQL reads like: From tbl_data Inner Join...

Select newest entry from a joined MySQL table

I have stock quantity information in my database. 1 table, "stock", holds the productid (sku) along with the quantity and the filename from where it came. The other table, "stockfile", contains all the processed filenames along with dates. Now I need to get all the products with their latest stock quantity values. This gives me ALL th...

MySQL query to search for people with specific skills

Hi, I'm inexperienced with SQL... i'm trying to figure out how to search my database for personnel with specific skills. I want to generate a table which contains two columns, Name and Skill(s) The initial search is currently performing this query: SELECT * FROM User_Skills LEFT JOIN skills ON User_Skills.Skill_id = Skills...

MySQL - JOIN 2 tables with 2 ID's in common

Hi, I have 2 tables that I need to get information from, and would like to get the information in one single query. The situation is this : table "matches" : id team_A_id team_B_id table "teams" : id name The objective is to retrieve information from table "matches" (football matches) and join the information with the table "team...

Improving performance on a view with a LOT of joins...

I have a view that uses 11 outer joins and two inner joins to create the data. This results in over 8 million rows. When I do a count (*) on the table it takes about 5 minutes to run. I'm at a loss as to how to improve the performance of this table. Does anyone have any suggestions on where to begin? There appear to be indexes on al...

Speed up MySQL query containing 300k+ records

I need to lookup all my products (sku's) their latest stock quantity. I have one table (called "stock") with 315k+ records containing this information (a new batch of data is added every day, for most sku's). The reference data is in another table (called "stockfile"). This is the query to do it: SELECT s1 . * , f1 . * FROM stock s1 JO...

mysql join query using like ?

Hey I have been trying to get this working for quite a while now but it just doesn't seem to work, maybe it is is not even possible, what i am wanting to do is to perform a mysql join query using like, such as this example i found... SELECT * FROM Table1 INNER JOIN Table2 ON Table1.col LIKE '%' + Table2.col + '%' but it just doesn't ...

sqlite join query

hii i am new to iPhone, I am using sqlite3 as my database. There are two tables in database as FriendInfo and ItemInfo. FriendInfo contain FriendId and some other field relative to friend info which are quine in this table and ItemInfo contain the ItemId as primary key and friend Id as Foreign Key, i want to execute the join query which...

Linq 'equals' keyword Revisited - Does it compare values and references to objects?

This question is a follow on to link text hence the revisited in the title. I raise this as a new question as the accepted answer and comment made under the original question both suggest that the equals keyword used in the join query refers to only value types within the comparison. I believe this is misleading as follows. Behind the s...

What class to return from LINQ to SQL classes when using JOIN

I am returning List from my BusinessLogic methods where the Type is one of the classes generated by Linq2Sql classes. Now if I use a join statement and get a result containing properties(columns) from different tables, then my return object should be a List<> of what? I am using asp.net ajax components from Telerik and set the datasour...

LINQ to SQL: Data from two unrelated tables, sorting on date

I have two tables tblActionLog and tblLoginLog. This is tblActionLog: tblActionLog ------------ ID (int) Happened (DateTime) ...etc. This is tblLoginLog: tblLoginLog ----------- ID (int) LoginDate (DateTime) ...etc. Now I want to have a GridView with information from both of these tables interleaved in eachother so that their sepa...

MYSQL selecting users with specific skills

Hi, I'm muddling my way through by far the most complex SQL query i've ever done, which is probably extremely simple for most of you (: I have three tables, User, Skills and User_Skills. The fields of which should be fairly self explanatory. I want to choose people who have one or more skills that meet my criteria. I can select a use...