Hi, I'm currently trying to write a query that will return all users that have not logged time for a given date in our timesheet system. We currently have 2 tables, timesheets and users. I am trying to make a query that will return a list of users that do not have an entry in the timesheets table for a date range. There is only one reco...
Suppose I have a table of customers and a table of purchases. Each purchase belongs to one customer. I want to get a list of all customers along with their last purchase in one SELECT statement. What is the best practice? Any advice on building indexes? Please use these table/column names in your answer: customer: id, name purchase: i...
(This is the django version of the thread at http://stackoverflow.com/questions/2111384/) Suppose I have a table of customers and a table of purchases. Each purchase belongs to one customer. I want to get a list of all customers along with their last purchase. Can it be done without raw SQL and without multiple database queries? ...
Hi All, I have two existing tables, with different fields, except for Primary ID (a varchar, not an int). I want to create a third table which is essentially a merge of these two, such that for a given Primary Key I have all fields in one table. What's the bext way of doing this? Many thanks ...
I'm trying to do a join on two tables based on multiple conditions, the problem is I'm not able to compare the date fields. The date is stored in datetime format in DB and I want all the records on a particular date, when I do this in as shown in the code below, I get this exception .. Method 'System.String ToShortDateString()' has no s...
we have 3 tables: grandfathers, fathers, sons. grandfathers have many fathers, and fathers have many sons, through the foreing keys grandfather_id and father_id. Each record has only one property, the name of the person. What I want is to have a list of all grandfathers, each with a field called grandsons_names which is the csv of all ...
I use Linq To Sql Join Query in DAL layer, How can I define a return List<T> automatically for the LinqToSql Join method? Nowadays, I have to manually define a List<CustomViewType> for each LinqToSql Join method's return value. Is that possible to define a List<T> like the following code : public static List<T> GetJoinList() { L...
I have 4 different queries and each of them return individual unique set of Results. I need to combine the Query Results with using a single query. my sample queries are: 1. select cls.* from (calls as cls inner join calls_users as clsusr on cls.id=clsusr.call_id) inner join users as usr on usr.id=cls.assigned_user_id where cls.assigne...
Hi, I hope someone can help me. I need to solve a problem with a view that get me the correct result based in the following scenario. I have two tables: Language and ReportDescription Language Id Tag Description ---- ---- ---------------- 1 en English 2 de German 3 fr French 4 it Italian ReportDe...
I am working on developing a skeleton of what to eventually become an administration dashboard. One of the things the dashboard will eventually have to do is allow users to search for users of a game and view information about them. The information about them in question is in different table so naturally I have had to employ associati...
Hi everyone While I've been writing php for a long time, it was always a skill I learnt myself and I've just hit a minor crisis of confidence over table joins! Assuming a MySQL db auth containing MyISAM tables users and permissions: users - id (auto increment) - email permissions - id (auto increment) - name To join these tables in...
I have a GIANT MYSQL database that contains 100,000,000 records. Simplified, three of my columns are ID, date and time, i have several indexes over id and date ( ID , DATE , ID&DATE ) so no performance problem on join select id, max(date) as last_record from mytable where date < "2010-01-02" mytable /-----------------...
Although I've figured out several queries that almost do this, I can't quite get it perfectly and I'm getting frustrated. Here is the setup: Table: Issue | id | name | value | +-------------------+ | 1 | a | 10 | | 2 | b | 3 | | 3 | c | 4 | | 4 | d | 9 | Table: Link | source | dest | +---------------+ | ...
I have a program in access that is using some linked ODBC tables. I had originally had a query that contained the following INNER JOIN: FROM Neptune_FN_Analysis INNER JOIN Neptune_prem ON Neptune_FN_Analysis.PremID = Neptune_prem.premice_id This worked just fine until the column Neptune_prem.premice_id got changed from a number to a...
This is a follow-up question to my previous one. Situation: Table 1: +--------------------+--------------------+ | v_id | v_name | +--------------------+--------------------+ | 1 | v_name1 | +--------------------+--------------------+ | etc... Table 2: +--------------------+--------...
Hi, I have this webapplication tool which queries over data and shows it in a grid. Now a lot of people use it so it has to be quite performant. The thing is, I needed to add a couple of extra fields through joins and now it takes forever for the query to run. If I in sql server run the following query: select top 100 * from bam_Pr...
Hi, Im trying to write an NHibernate criteria that effectively joins and restricts at the same time. My DB looks like this... Cases ---> CustomerProducts <--- Customers Cases ---> CaseStatuses Each case is associated with a customer product (Many cases to one product). Each customer has a number of customer products (One customer has...
We have a simple query that looks like: SELECT a,b,c,d FROM table WHERE a=1 and b IN ('aaa', 'bbb', 'ccc', ...) No joins at all, 5000 contsant values in the IN clause. Now, this query takes 1-20 seconds to run on a very strong (16 core) server. The table has an index on (a,b), and we also tried reversing the index to (b,a). The serve...
Hey all, I wanted to know what is better performance-wise: to put the conditions in the JOIN? (x JOIN y on x.hi = y.hi AND ....) or maybe to put it under the WHERE, and leave the JOIN only with the crossing condition. thanks folks ...
I have two tables User id name Result id user_id score created_at Every hour a cron job runs and puts data in the result table for each user - assigning them a score based on some external variable parameters. So the Result table contains many records for each user. I want to retreive a 'top ten' users at any given poin...