subquery

Error in nested subquerys

I'm very confused by the subquery function in MySQL. For my example I use 3 tables: adr contains addresses adr_acc contains groups or users that have access to the addresses usr_grp contains the useres belonging to a group select * from adr where adr.adr_id in (select ac1.adr_id from adr_acc as ac1 where ( (ac1.acc_type =...

Subqueries with Doctrine_RawSql

Is it possible to have subqueries in the select field of Doctrine_RawSql? $q->select('{t.*}, {i.*}, {e.*}, {f.*}, {f2.*}'); $q->addSelect("(SELECT f.id FROM Following f WHERE f.follower_id = ? AND f.following_id = t.owner_id) AS following"); $q->addSelect("(SELECT COUNT(c.id) FROM PostComments c WHERE c.post_id = t.id) AS num_comments")...

MYSQL - Selecting existing users who have not made an entry in the last 3 months, but have made an entry within the last 6 months.

After several hours of searching I'm finally giving in and asking for help. I've found something that gets me close, but doesn't seem to be working properly. Basically looking for users who uses us between 3-6 months ago, but haven't in the last 3 months. For the sake of the example lets say I have two tables named event(id, clientFK, ...

How do I concatenate strings from a subquery into a single row in mysql?

I have three tables: table "package" ----------------------------------------------------- package_id int(10) primary key, auto-increment package_name varchar(255) price decimal(10,2) table "zones" ------------------------------------------------------ zone_id varchar(32) primary key (ex of data: A1,...

sqlite2: Joining max values per column from another table (subquery reference)?

Hi all, I'm using the following database: CREATE TABLE datas (d_id INTEGER PRIMARY KEY, name_id numeric, countdata numeric); INSERT INTO datas VALUES(1,1,20); //(NULL,1,20); INSERT INTO datas VALUES(2,1,47); //(NULL,1,47); INSERT INTO datas VALUES(3,2,36); //(NULL,2,36); INSERT INTO datas VALUES(4,2,58); //(NULL,2,58); INSERT INTO...

SQL Server: Boolean expressions with subquery in WHERE-clause

Hi! I have a subquery within a WHERE-clause within a subquery. Notwithstanding the design issues of the database (not my job), I am getting some strange errors when trying to extend the top level WHERE-clause in this expression. The example below WORKS. LEFT OUTER JOIN CargoVoyageLocation on CargoVoyageLocation.VoyageLocationI...

Where Is the Syntax Error in this SQL?

I've got a query that is pretty much the same as many others which are used in the same library... but I did a lot of copy&paste on the SQL to add features to each one which are all similar but slightly different. Just below is the section which gives me the SQL Parser error. It fires at the Set rs = line. dim sql, rs sql = "DECLARE @st...

SQL Get specific columns from one table and all rows from a joined table in one query

This may have been asked before and I just can't find it. I have a one to many relationship in the database on a few tables. table1 table2 table3 table2 - table3 is the 1-many relationship here's a mock of what I have: select table1.id table1.Column table2.Column2 -- I want all entries here from table 3 here as well From table1 t...

SQL subquery matches hard-coded IN criteria, but not subquery

I have a group of people who have taken a test. I can select their IDs with this query: SELECT person_id FROM tests WHERE test_code = 1234 I'd like to pull these individuals' records from a demographics table, so I tried this subquery to do so: SELECT * FROM demographics d WHERE d.person_id IN ( SELECT t.person_id FROM tests...

mysql multiple-subquery group_concat query

I'm trying to show the boroughs and postcodes a particular town in is. My database is fairly well structured, with a table such as town, postcode and borough. There are also tables for each of the relationships town_postcode & town_borough. Ideally I want the data returned as: "Abbey Wood", "SE2", "Bexley, Greenwich" "Barbican", "EC1,...

SQL Query Help: Returning distinct values from Count subquery

I've been stuck for quite a while now trying to get this query to work. Here's the setup: I have a [Notes] table that contains a nonunique (Number) column and a nonunique (Result) column. I'm looking to create a SELECT statement that will display each distinct (Number) value where the count of the {(Number), (Result)} tuple where Result...

Rewriting MySQL query with subqueries to joins.

I have written a fairly complex SQL query to get some statistics about animals from an animal sampling database. This query includes a number of subqueries and I would now like to see if it is possible to rewrite this query in any way to use joins instead of subqueries. I have a dim idea that this might reduce query time. (it's now about...

Subquery not in performance question

I have this slow query select * from table1 where id NOT IN ( select id from table2 ) Would this be faster by doing something like (not sure if this is possible): select * from table1 where id not in ( select id from table2 where id = table1.id ) Or: select * from table1 where table1.id NOT EXIST( select id from table2 where table...

SubQueries - using only one Where statement.

I have the below query, everytime i change it i need to change the stockclientid. I would like it where i only have one where statement to apply to all the sub queries. Is there also a way to apply the same code to return multiple rows? I would like to have the results display for all stockclientIDs rather than just 1. Select (Select...

WHERE IN Question

I have a couple of Tables already supplied in the form MainRecord Record ID Details Textual Information 1 AAAAAAA ... some text referring to Oxford St Giles... 2 BBBBBBB ... some text referring Oxford.... 3 CCCCCCC ... some text referring to Oxford St Aldate... and supporting table Pl...

how to delete the records from a table after a limit.

I have a table like this: CREATE TABLE vhist ( id int(10) unsigned NOT NULL auto_increment, userId varchar(45) NOT NULL, mktCode int(10) unsigned NOT NULL, insertDate datetime NOT NULL, default NULL, PRIMARY KEY (id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; A user can have more than one record. I need an SQL statemen...