I am using GridSQL where I get some performance problems whenever the SQL pattern INNER JOIN (SELECT arises. I am therefore considering rewriting all these queries into two queries, one creating a temporary table using the exact select statement and the other query joining with the temporary table, so the pattern would be INNER JOIN temp...
I have a resultset which has ProductURL stored : accessoires/abc/285/P187654 accessoires/abc/285/D18765432 accessoires/abc/285/A1876543 I need to get the final part of the URL i.e. anything that is after the final '/' that appears in the URL. I have a function which gives me the LASTINDEX : SELECT [LAST_INDEX] (ProductURL,'/') But...
Hi I have following db structure: File, User, FileRevision (has foreign key to File, and many-2-many connection through intermediate table to User). I want to fetch all FileRevision-s that: are newest/freshest in their corresponding File-s, have many-2-many link to User that performs search (permission checking). I found out that...
Hi all, I hava 2 objects associate by oneToMany relationship("One Model can have many events"). I'm trying to make a subquery in ejbql to find models for one event, like this: SELECT model FROM RegModelValue model WHERE :event IN (model.events) .... but toplink doent recognize model alias and tell me "Internal Exception: line 1:12...
I have a table which is kinda like an historic table... so I have data like this idA numberMov FinalDate 1 10 20090209 2 14 20090304 1 12 20090304 3 54 20080508 4 42 20090510 ... ... .... I need to retrieve the numberMov based on the newest finalDate from each ...
Hi All, I have a Patient information table with ~50 million records. I need to check some samples for each year which may be in any order. Here are the sample date available in database "20090722", "20080817", ... "19980301". Also i have a primary-key column called "PID". My requirement is to get 2 or 3 samples for each year with a...
Well for whatever reason MySQL does not support subqueries in a View. I'm stuck using MySQL for the forseeable future and I'm needing to write a View to aggregate data from an EAV-style table into a more readable format. The View itself is going to act as the Model for a Ruby on Rails-based application (it's read only so I'm safe using...
This should be a simple one I hope. I have an invoice and that invoice has a list of payments. Using the Criteria API I am trying to return a list of invoices and their payment total. So, in SQL I want something like this: SELECT i.*, (SELECT SUM(PMT_AMOUNT) FROM INVOICE_PAYMENTS p WHERE p.INVOICE = i.INVOICE) FROM INVOICES i I can'...
I'm working on a simple voting system, using php and sqlite. I prebuild a whole scenario and tested it in the sqlite commandline, where it worked nicely. But one query, which should return all possible answers of a poll and the users that voted for it, doesn't work anymore... Tables: CREATE TABLE polls ( question CHAR(200) ); CREATE ...
Hi All, I have this sql below that i use to compare values of z. I put z in a subquery and then compare it. My qn is in in my else statement below, i want to put in another formula to calculate something else of which the info is only available in another table called var1(for example, n). i would like to put in sum(n)/count(n) to en...
The following query hangs: (although subqueries perfomed separately are fine) I don't know how to make the explain table look ok. If someone tells me, I'll clean it up. select sum(grades.points)) as p, from assignments left join grades using (assignmentID) where gradeID IN (select grades.gradeID from assignments left join grade...
select colId, colTaskType, MaxID from tblTaskType join ( select tblCheckList.colTaskTypeID, max(colItemNumber) MaxID from tblCheckList group by colTaskTypeID ) x on coltaskTypeID = tblTaskType.colID ...
Hello, What I need to do is searching from companies and tags table and listing companies. My table structure is as follows; tags (tag_ID, tag) tag_relation (tag_ID, company_ID) companies (company_ID, company_name, company_description) The query should be able to search both company info (name, description) and tags. If tag searched,...
Hi there, I have a SQL like this: Select tbl.id, tbl.name From (select table1.id, table1.name from table1 inner join table2 on table1.id = table2.id order by table2.priority ) tbl group by table1.id order by table1.name What I'm tring to archieve is to first sort (order by table2.priority), and then get the record with table1.i...
I always though join gets the results of something and then joins only using those results. SELECT * FROM tbl AS t1 JOIN tbl2 AS t2 ON t1.id = t2.foreignId JOIN tbl3 AS t3 ON t2.id = t3.foreignId WHERE t1.date > SOMEDATE From my understanding it will run the where statement and get only the results that fall within the date range. Th...
What is the difference between an inner join and a subquery? and when should i use a subquery? or an inner join? I was in a situation recently where an inner join was too slow and subquery was the solution. From my limited understanding i thought the two worked exactly the same. ...
I'm trying to SELECT two values in one statement instead of two statements. The first statement counts how many entries won for a specific contest that has an id of 18. The second statement counts the total quantity of prizes for that contest. Query 1: SELECT COUNT(*) FROM entries WHERE entries.contest=18 AND entries.won=1 Query...
I am trying to update all records in a column so that they start with 'CD' e.g. DCE206 would become CDE206. UPDATE table SET column = REPLACE(column1, 'DC', 'CD') WHERE column1 LIKE 'DC%' I am using the above update statement however the following error appears 'Subquery returned more than 1 value. This is not permit...
I'd like to use MySQL in this form: SELECT 1 AS one, one*2 AS two because it's shorter and sweeter than SELECT one*2 AS two FROM ( SELECT 1 AS one ) AS sub1 but the former doesn't seem to work because it expects one to be a column. Is there any easier way to accomplish this effect without subqueries? And no, SELECT 2 AS two is no...
Hi SO, I am currently working on a particularly complex use-case. Simplifying below :) First, a client record has a many-to-one relationship with a collection of services, that is, a single client may have multiple services associated with it. Within my trigger, I am writing a Query that returns a client's id based on certain criteria....