I have a table: id | score | date bob | 40 | 2010-1-1 bob | 70 | 2010-1-15 sue | 55 | 2010-1-1 sue | 80 | 2010-2-1 I want to query for either the score for a user on a specific date OR, if no score exists for that user on that date,return the score from the most recent date for that user. Is there a way to do this w...
Is there any better way to make this query work? I'm looking for a more efficient solution, if there is one available. SELECT `unitid`, `name` FROM apartmentunits WHERE aptid IN ( SELECT `aptid` FROM rentconditionsmap WHERE rentcondid = 4 AND condnum = 1 ) AND aptid IN ( SELECT `aptid` FROM rentconditionsmap WHERE rentcondid = 2...
Hi, I have a photos table where users can have multiple photos. I'm trying to create the following query: $q = Doctrine_Query::create() ->update('Photo p') ->set('p.photo_type', 1) ->where('p.user_id = ?', $id) ->andWhere('p.date_added = (SELECT MIN(p.date_added) FROM Photo p WHERE p.user_id = ?)', $id) The idea is to se...
I have two tables: product (idproduct, name, description, tax) product_storage (idstorage, idproduct, added, quantity, price) for each product it could be different price and oldest are "first-to-sell" for example in storage I have: 1, 1, 2010-01-01, 0, 10.0 2, 1, 2010-01-02, 0, 11.0 3, 1, 2010-01-03, 10, 12.0 4, 2, 2010-01-04, ...
Hi, LINQ gurus, I am looking for help to write a query... I have a table with Person records, and it has a nullable ParentID column, so it is kind of self-referencing, where each record might have a Parent. I am looking for unprocessed rows whose parent rows were processed. This SQL works fine: SELECT * FROM Person where IsProce...
A Project can have many Parts. A property on Part is Ipn, which is a string of digits. Project "A" has Parts "1", "2", "3" Project "B" has Parts "2", "3", "4" Project "C" has Parts "2" Project "D" has Parts "3" I want to find all Projects that have all of the specified parts associated with it. My current query is var ...
I have three tables specifying important columns below Users(Id, username) Groups(Id, groupname, creator) (creator is the creator of the group) Association(Id, UserId, GroupId) (entries in this table include which user is in which group) Association.UserID =Users.Id, Association.GroupId = Groups.id and also Groups.creator = Users.Id....
i want to know, how to write subquery in between SELECT and FROM as SELECT Col_Name,(Subquery) From Table_Name Where Some_condition ...
Hey: I am trying to write a query that will return all orders that only have a Subscription included. It is easy enough to write a query that includes all Orders with Subscriptions, another that includes all orders without a Subscription and then compare them with an unmatched query. But I don't want to have to store Queries in my Acce...
this is what i am doing update t1 set x=a,y=b where a and b are obtained from (select query here) i know the select query the select query returns multiple results which are the same when i use group by or distinct query execution slows down considerably a and b are forward references so mysql reports an error i want to set a equal t...
I'm developing an rss feed reader that uses a bayesian filter to filter out boring blog posts. The Stream table is meant to act as a FIFO buffer from which the webapp will consume 'entries'. I use it to store the temporary relationship between entries, users and bayesian filter classifications. After a user marks an entry as read, it w...
This one seems to be a simple problem, but I can't make it work in a single select or nested select. Retrieve the authors and (if any) advisers of a paper (article) into one row. I order to explain the problem, here are the two data tables (pseudo) papers (id, title, c_year) persons (id, firstname, lastname) plus a link table w/one e...
I am trying to do a join with a sub query and can't seem to get it. Here is what is looks like working in sql. How do I get to to work in linq? SELECT po.*, p.PermissionID FROM PermissibleObjects po INNER JOIN PermissibleObjects_Permissions po_p ON (po.PermissibleObjectID = po_p.PermissibleObjectID) INNER JOIN Permissions p ON (po_p.P...
MySQL v5.0.58. Tables, with foreign key constraints etc and other non-relevant details omitted for brevity: CREATE TABLE `fixture` ( `id` int(11) NOT NULL auto_increment, `competition_id` int(11) NOT NULL, `name` varchar(50) NOT NULL, `scheduled` datetime default NULL, `played` datetime default NULL, PRIMARY KEY (`id`) ); ...
Is is possible to accomplish the equivalent of a LEFT JOIN with subselect where multiple columns are required. Here's what I mean. SELECT m.*, (SELECT * FROM model WHERE id = m.id LIMIT 1) AS models FROM make m As it stands now doing this gives me a 'Operand should contain 1 column(s)' error. Yes I know this is possible with LEFT JOI...
I'm running into my own limits of MySQL query skills, so I hope some SQL guru can help out on this one. The situation is as follow: I have images that can be tagged. As you might expect this is stored in three tables: Image Tag Tag_map (maps images to tags) I have a SQL query that calculates the related tags based on a tag id. The q...
SELECT SUM( amount_disbursed ) disbusedamount, (select SUM( loaninstallmentpaid_amount ) FROM ourbank_loan_repayment) paidamount, SUM( amount_disbursed )- (select SUM( loaninstallmentpaid_amount ) FROM ourbank_loan_repayment) differenceamount FROM ourbank_loan_disbursement Any Help would be appreciated ...
Hi In SQL Server 2008: I have one table, and I want to do something along the following lines: SELECT T1.stuff, T2.morestuff from ( SELECT code, date1, date2 from Table ) as T1 INNER JOIN ( SELECT code, date1, date2 from Table ) as T2 ON T1.code = T2.code and T1.date1 = T2.date2 The two subqueries are exactly identical. Is there...
I have two tables, news and news_views. Every time an article is viewed, the news id, IP address and date is recorded in news_views. I'm using a query with a subquery to fetch the most viewed titles from news, by getting the total count of views in the last 24 hours for each one. It works fine except that it takes between 5-10 seconds ...
Hi everybody, I’m working on a small kind of log system to a webpage, and I’m having some difficulties with a query I want to do multiple things. I have tried to do some nested / subqueries but can’t seem to get it right. I’ve two tables: User = {userid: int, username} Registered = {userid: int, favoriteid: int} What I need is a q...