subquery

MySQL: Selecting the rows having min value of a computed column

The naive way of doing this that comes to mind would be: SELECT name, lev FROM (SELECT name, levenshtein(name, *parameter*) as lev FROM my_table) WHERE lev = (SELECT MIN(lev) FROM (SELECT name, levenshtein(name, *parameter*) as lev FROM my_table )); However the "(SELECT name, levenshtein(name, parameter) as lev FROM my_table)" subqu...

How to reference a map name for a subquery in an HQL order by clause

I've got an HQL statement like this: select new map (f1 as field1, (select ...) as field2) from ... where ... order by field2; It fails saying "Unknown column 'field2'". I experienced this in general that when using the "new map" statement, I can't reference the map names in the order by field. As HQL subqueries are only allowed in t...

PostgreSQL: Why can't subqueries as expressions return more than one row, but functions can?

If I try to create a column whose value is a select returning more than one row, I get an error. => select (select 1 union select 2); ERROR: more than one row returned by a subquery used as an expression But if I create a function that does the same thing, I get the behavior I want. => create or replace function onetwo() returns set...

Hibernate Criteria Subquery

Hi, I need to do this SQL query with detachedCriteria: SELECT g.id FROM games g WHERE NOT EXISTS ( SELECT 1 FROM users_games ug WHERE ug.user_id = 1 AND g.id = ug.game_id) The idea is to get the ids from the games that aren't owned by the user. I tried like 10 different approaches with detachedCriteria but I get the "Unknown entit...

Complicated Sub-query - is this possible?

I've got 2 tables: one stores tags, the other stores articles. There's a mode "Get articles by tag", which basically takes all articles, tagged "x". In my articles table I use a filed, called Tags, that stores data in such pattern 'tag1, tag2, tag3, ...'. So I want to get everything work by just a single query like that: SELECT *, ...

How to use subqueries in SQLAlchemy to produce a moving average?

My problem is that I want to retrieve both a list of measurements along with a moving average of those measurements. I can do that with this SQL statement (postgresql interval syntax): SELECT time, value, ( SELECT AVG(t2.value) FROM measurements t2 WHERE t2.time BETWEEN t1.time - interval '5 days...

How do I return true or false if multiple columns equal a certain value in MySQL query?

I have a single DB table (MySQL) which I am running a simple SELECT on. In this table I have 3 fields which contain 3 possible values each. In each case the values are identical ('none','online','physical'). I want to return in my result a true or false value for an alias if any one of these fields are not set to 'none'. I can easily ev...

how to implement a sorting subquery in the 'from' section?

I need to have a subquery order a table before joining so that when I group the table, the proper collapsed data is shown. For the example query, I want the latest start time before a given datetime (2006-08-26 00:00:00) select * from parts p left join (select * from transactions t left join transactiondetails td on(td.transaction_...

Access 2K SQL OUTER JOIN using Derived Tables (i.e. subselect/subquery)

First, I am a noob, just trying to learn some Access/VBA/SQL, but I have been stumped by this issue. Shown below are the (9) tbl JOIN relations. This lists X records for each game_ID (X = number of players in game). I only want one record per game_ID where [game_players.player_ID] is a selected/special player (say, HERO ) [game_playe...

MySql Not accessing parent in subquery?

I get the error Unknown column 'm.id' in 'on clause' all my tables have a field called id. It appears to occur in my subquery. I use m.id in my join. So what the heck is going on? shouldnt i be able to access that var? how do i access media.id from my subquery? Also is the and not (select 1 part right? i am trying to exclude that tag....

How do I return multiple results in a SQL subquery?

I have a query below and would like to know if it is possible to get more than 1 result. I would like to get the 4 most recent entries. Thanks! select c.email,c.text,m.alertDataID from client_users as c, monitor_alerts as a, monitor_alerts_data as m where c.id=a.userID and a.alertID=m.alertID and m.alertDataID = (SELECT alertDataID ...

Mysql subquery, Update embedded into select?

Hi, I am doing a mysql injection on a site (for educational purpose i promise hehe), now, It uses mysql as its database, I cannot do: "; UPDATE..." so my question is, if i do: "OR id=(update...)".. as a subquery, that of course doesn't make any sense yet will it execute the update on the table i choose? ...

SQL Joins Vs SQL Subqueries (Performance)?

Hi all, I wish to know if I have a join query something like this - Select E.Id,E.Name from Employee E join Dept D on E.DeptId=D.Id and a subquery something like this - Select E.Id,E.Name from Employee Where DeptId in (Select Id from Dept) When I consider performance which of the two queries would be faster and why ? Also is the...

mysql query to three tables, want to use JOIN instead subquery

I want to use join instead subquery to find the trade id not exist on trade_log filtered by ip and current date for mysql syntax below. SELECT plug.id as a, plug.url as b, trade.id as c FROM plug, trade WHERE trade.id = plug.trade_id AND trade.id NOT IN (SELECT trade_log.trade_id ...

How to rewrite a subquery to use joins when you used DISTINCT in the subquery?

I have a query where I use a sub query, and I would like to rewrite into a join, to have better performance. The subquery uses DISTINCT as there are many records, and the problem I have is that they end up multiple times in the join when I use the join. So how do I rewrite a query like this to use join: SELECT * FROM table1 a ...

Optimize a simple JOIN or IN query

I have this query: SELECT DISTINCT id, label FROM bbproduct_cust WHERE CUSTNO IN (SELECT CUSTNO FROM customer WHERE SLSRPTGRP = '1996') AND DEPTNO = '0' ORDER BY label ASC EXPLAIN shows id select_type table type possible_keys key ...

Need help for SQL select query for this two table

Table Capture image : http://img844.imageshack.us/img844/6213/99730337.jpg ------------ PositionTable------------ ID ContentFK Position 11 100 1 12 101 1 13 104 2 14 102 2 15 103 2 16 105 3 17 106 3 18 1...

MySQL, Using a Subquery to get Rank of Score

I am trying to find out where this particular player ranks among shooting guards in the NBA. I am using this post on stackoverflow for a guide. I get the error "Invalid use of group function". SELECT first, last, team, pos, SUM(points) AS scoresum, ROUND(AVG(points), 2) AS avgpoints, (SELECT COUNT(*) FROM nba...

Mysql/php - Query Question

I have been trying to figure out a different way to complete this task on another question on this website, but think maybe I am making it too difficult. Here is what I have: Table with, and ImageID, ImageName, GalleryID Another Table with Comments, Author, Date, ImageID What I want to do is do a query where I find all of the Images th...

SELECT with multiple subqueries to same table

I'm using the same SQL pattern over and over, and I know there has to be a better way, but I'm having trouble piecing it together. Here's a simple version of the pattern, where I'm pulling back the student's information and the last book they checked out, if one exists: SELECT TStudents.*, BookName = (SELECT TOP 1 BookName ...