subquery

Using Subquery results as a query variable

I'm trying to make the result of one of my subqueries be used in another subquery but it doesn't work. This is the query: SELECT t.TenantName, (SELECT SUM(Amount) FROM tblTransaction WHERE Amount > 0 AND TransactionDate >= '12/01/09' AND TransactionDate <= '12/31/09' AND TenantID = t.ID ...

MySQL Multiple Join/Subquery Questions

Hi All, For about a year now, we’ve been allowing our users to login with usernames and/or email addresses that are not unique (though each user does have a unique id). Although the system handles duplicate usernames/emails elegantly, we’ve decided to finally enforce unique usernames and email addresses. I’ve been tasked with generati...

MYSQL Query TAKING user's participation place with quantity of all other participants

Hi, I am making a website where users add the place where they have visited. There is 4 main tables users (user_id,name ) places (place_id,type_id,place_name, city_id) user_place (user_id,place_id, date ) city(city_id,city_name) Now I need to take with one query all the places which are type of 2 (where type=2) where the given user...

How to specify the parent query field from within a subquery in mySQL?

Is there a way to specify the parent query field from within a subquery in mySQL? For Example: I have written a basic Bulletin Board type program in PHP. In the database each post contains: id(PK) and parent_id(the id of the parent post). If the post is itself a parent, then its parent_id is set to 0. I am trying to write a mySQL que...

What's wrong with this MySQL query? SELECT * AS `x`, how to use x again later?

The following MySQL query: select `userID` as uID, (select `siteID` from `users` where `userID` = uID) as `sID`, from `actions` where `sID` in (select `siteID` from `sites` where `foo` = "bar") order by `timestamp` desc limit 100 …returns an error: Unknown column 'sID' in 'IN/ALL/ANY subquery' I don't understand what I'm doing wron...

Entity Framework sub query

How to write sub queries like these in EF? select * from table1 where col1 in (select col1 from table2 where col2 = 'xyz') or select * from table1 where col1 not in (select col1 from table2 where col2 = 'xyz') I tried something like these from t1 in table1 where (from t2 in table2 where col2 = 'xyz' select t2.col1).Contains(t1.col...

insert query with a subquery

hi i tried to use the following query to insert data in a table nd got error insert into filmo_person_song (person_id, song_id, role_id) select person_id from filmo_person_song fps, filmo_song fs, filmo_role fr where fps.song_id = fs.song_id and fps.role_id = fr.role_id and fps.person_id = 43629; ERROR 1136 (21S01): Column count d...

Postgresql - Using subqueries with alter sequence expressions

My question is kind of simple. Is it possible to use subqueries within alter expressions in PostgreSQL? I want to alter a sequence value based on a primary key column value. I tried using the following expression, but it wouldn't execute. alter sequence public.sequenceX restart with (select max(table_id)+1 from table) Thanks in ...

How to get a distinct result, but return another column in the select?

I want to get a distinct result based on a property, but return the id in the select because I will be using it in a subquery. e.g. (List<Article>) session.createQuery("from Article a where a.id in (select distinct a2.title from article a2 where a2.body = :body"); setString("body", "") .list(); The key section is the subquery, I want...

SQL subqueries to try to get maximum difference of the same column in two tables

Hi all, I’m looking to get the max discrepancy between two tables per day, per id. I have the following data in a mysql database insert into test.foo values ('2010-01-10', 1, 10); insert into test.foo values ('2010-01-10', 1, 5); insert into test.foo values ('2010-01-10', 2, 10); insert into test.foo values ('2010-01-10', 2, 10); inse...

SQL Server 2005 select for XML path with union in sub-selection problem

Hi There, I'm rather experienced with SQL server "select for XML path" queries but now i run into a strange problem. The following query works fine: select ( select 'Keyfield1' as "@Name", t1.Keyfield1 as "Value" from MyTable t1 where t1.KeyField1= t2.KeyField1 and t1.KeyField2= t2.KeyField2 for xml...

Subquery question in MS Access

I'm not quite sure why the following query won't run in Access. It asks me to give the value of s1.sku SELECT s1.SkuApexId, s1.sku, s1.apex, s1.btw, s1.gebruikernr, q2.* FROM tblSkuApex AS s1, (SELECT MAX(s2.begindatum) FROM tblskuapex s2 WHERE s1.sku = s2.sku) q2 ...

MS-Access Get price of product at certain order date

I have a table filled with purchase prices, like this: sku price btw startdate PCR-CA5425023181515 21,17 € 1 01/01/2009 PCR-CA5425023181515 999,00 € 1 06/06/2009 PCR-CA5425023181515 444,00 € 4 09/07/2009 PCR-CA5425023181515 100,00 € 4 10/08/2009 I have another table filled with orders, ...

Why can't I apply a criteria on a sub-query?

What I'm trying to do is this: select Store.Id, (select COUNT(*) from StoreProduct where StoreProduct.Store_id = Store.Id) as _count from Store where _count > 3 SQL Server says it's invalid because the column name '_count' is invalid. Why it's invalid if I'm declaring it? ...

how would you write this sql to delete if either of two fields are in a set of values

Background: i have 2 tables: Components: (id, name) Dependencies: (id, hasaComponentId, isaComponentId) in this case the hasaComponentId and isaComponentId are both foreign keys into the components table joined by components.id Question i have a set of Ids that the user selects. I want a sql query that will delete records from...

Mysql Sub Select Query Optimization

I'm running a query daily to compile stats - but it seems really inefficient. This is the Query: SELECT a.id, tstamp, label_id, (SELECT author_id FROM b WHERE b.tid = a.id ORDER BY b.tstamp DESC LIMIT 1) AS author_id FROM a, b WHERE (status = '2' OR status = '3') AND category != 6 AND a.id = b.tid AND (b.type = 'C' OR b.type = 'R') AN...

Mysql subquery : Trying to get the last order from a customer

Sorry , maybe it's trivial but I can't find a way to do this : table customers customer_id customer_email customer_newsletter (1=yes/0=no) table orders order_id customer_id order_status (1=ok/0=no_ok) Each customer can have zero, one or many orders. I'm trying to make a query to get all the emails of the persons who are : ...

Subselect a Summed col in Oracle

this is an attempted fix to a crystal reports use of 2 sub reports! I have a query that joins 3 tables, and I wanted to use a pair of sub selects that bring in the same new table. Here is the first of the two columns in script: SELECT ea."LOC_ID", lo."DESCR", ea."PEGSTRIP", ea."ENTITY_OWNER" , ea."PCT_OWNERSHIP", ea."BEG_BAL", ea."ADD...

Subquery in LINQ that's in the select statement, not the where clause

I need to do something like the following SELECT p.name, (SELECT COUNT(p.id) FROM products WHERE products.parent_id = p.id) AS sub_products FROM products AS p I see lots of LINQ examples of subqueries in the where clause, but nothing like this where it's in the select statement. ...

Mysql subquery fails, need help :)

I have a table PICTURES: username varchar(50) picture_id varchar(50) datetime ...and I have a table FRIENDS: user_1 varchar(50) user_2 varchar(50) datetime When you have friends on the website your username goes in user_1, and your friend username's go in user_2. For each new friend a new row... I want to show the 5 last ...