subquery

Linq guru - filtering related entities...

My table structure is as follows: Person 1-M PesonAddress Person 1-M PesonPhone Person 1-M PesonEmail Person 1-M Contract Contract M-M Program Contract M-1 Organization At the end of this query I need a populated object graph where each person has their: PesonAddress's PesonPhone's PesonEmail's PesonPhone's Contract's - and this h...

In SQL or MySQL, can we join a table and a subquery result?

Can we join a table with the result of a subquery, such as: select name from gifts LEFT OUTER JOIN (select giftID from gifts) ... If not, can it be done by some methods, such as creating a temporary table? P.S. Can a subquery only appear using IN or NOT IN, or EXISTS or NOT EXISTS? ...

Selecting random top 3 listings per shop for a range of active advertising shops

I’m trying to display a list of shops each with 3 random items from their shop, if they have 3 or more listings, that are actively advertising. I have 3 tables: one for the shops – “Shops”, one for the listings – “Listings” and one that tracks active advertisers – “AdShops”. Using the below statement, the listings returned are random ho...

LINQ query with specified number of rows in a subquery problem

I'm trying to write the following query in LINQ, but can't seem to get it correct. select p.* from Person p inner join PersoniPhones i ON p.PersonID = i.PersonID where p.PersonID in ( SELECT PersonID FROM ( SELECT Top 10 PersonID, iPhoneID FROM iPhone ORDER BY LastPlayedDate DESC ) as t ) ...

How to use where condition for the for a selected column using subquery?

I have two columns as company and product. I use the following query to get the products matching particular string... select id,(select name from company where product.cid=company.id) as company,name,selling_price,mrp from product where name like '$qry_string%' But when i need to list products of specific company how can i do? i t...

SQLite subquery syntax/error/difference from MySQL

I was under the impression this is valid SQLite syntax: SELECT *, (SELECT amount AS target FROM target_money WHERE start_year <= p.bill_year AND start_month <= p.bill_month ORDER BY start_year ASC, start_month ASC LIMIT 1) AS target FROM payments AS p; But I guess it's not, because SQLite returns this erro...

Write subquery in Criteria of nHibernate.

I read about subquery in Criteria, but I am still unable to grasp it properly. So, here I am taking one example and if somebody can help me writing that using subquery it will be great. Lets say we have table Employee{EmployeeId.(int),Name(string),Post(string),No_Of_years_working(int)} Now I want all the employees who are Managers a...

Alternative to sql NOT IN?

Hi, I am trying to make a materialized view in Oracle (I am a newbie, btw). For some reason, it doesn't like the presence of sub-query in it. I've been trying to use LEFT OUTER JOIN instead, but it's returning different data set now. Put simply, here's the code I'm trying to modify: SELECT * FROM table1 ros, table2 bal, table3 flx ...

Hibernate Subquery and DetachedCriteria

I have created a DetachedCriteria that is retrieving estates that have the isApproved and isPublished set to true. It is defined in this way: DetachedCriteria activePublishedCriteria = DetachedCriteria.forClass(Estate.class) .add(Restrictions.eq("isApproved", true)) .add(Restrictions.eq("isPublished", true)) .setResultTransf...

MySQL subqueries

Can we do this query without subqueries? SELECT login, post_n, (SELECT SUM(vote) FROM votes WHERE votes.post_n=posts.post_n)AS votes, (SELECT COUNT(comments.post_n) FROM comments WHERE comments.post_n=posts.post_n)AS comments_count FROM users, posts WHERE posts.id=users.id AND (visibility=2 OR visibility=3) ORDER BY date DESC LIMI...

Optimize slow ranking query

I need to optimize a query for a ranking that is taking forever (the query itself works, but I know it's awful and I've just tried it with a good number of records and it gives a timeout). I'll briefly explain the model. I have 3 tables: player, team and player_team. I have players, that can belong to a team. Obvious as it sounds, playe...

TSQL - TOP X in FROM Subquery?

Can someone please enlighten me to a way to filter a subquery that is located in a FROM clause? I would like it to look something like this: SELECT * FROM TABLE_A LEFT JOIN (TOP 8 TABLE_B) ON TABLE_B.id = TABLE_A.id ...

MySQL sub query

UPDATE members SET money=money+100 WHERE username IN ( SELECT username FROM forum ); Lets say I wanted to give each of my members 100 money for each post in my forum. This query works but if one member has posted more than once, they only get 100. Could someone correc...

How do I use a MySQL subquery to count the number of rows in a foreign table?

I have two tables, users and reports. Each user has no, one, or multiple reports associated with it, and the reports table has a user_id field. I have the following query, and I need to add to each row a count of how many reports the user has: SELECT * FROM users LIMIT 1, 10 Do I need to use a subquery, and if so, how can I use it ef...

MySQL multiple dependent subqueries, painfully slow

I have a working query that retrieves the data that I need, but unfortunately it is painfully slow (runs over 3 minutes). I have indexes in place, but I think the problem is the multiple dependent subqueries. I've been trying to rewrite the query using joins but I can't seem to get it to work. Any help would be greatly appreciated. The ...

having condition on result of a subquery in mysql

hi i am trying to run a query like this SELECT a, b , c, (SELECT INNULL(x,y)) as mycol WHERE mycol < 400 ; BUt it gives the error #1054 - Unknown column 'mycol' in 'where clause' What would be the right way to do this? Thanks. ...

What's the most efficient query?

I have a table named Projects that has the following relationships: has many Contributions has many Payments In my result set, I need the following aggregate values: Number of unique contributors (DonorID on the Contribution table) Total contributed (SUM of Amount on Contribution table) Total paid (SUM of PaymentAmount on Payment tab...

Aggregate Functions on subsets of data based on current row values with SQL

Hopefully that title makes sense... Let's say I have an employee table: ID | Name | Title | Salary ---------------------------- 1 | Bob | Manager | 15285 2 | Joe | Worker | 10250 3 | Al | Worker | 11050 4 | Paul | Manager | 16025 5 | John | Worker | 10450 What I'd like to do is write a query that will give me the above ...

How to Compute for ABCIndicator in T-SQL

I have a table of sales data for example: SELECT ItemCode, ItemDesc, TotalYearlySales, ShareOfBusiness, ABCIndicator FROM Sales WHERE Yir = Year(getdate()) AND Manth = Month(getdate()) ORDER BY TotalYearlySales DESC The ShareOfBusiness is computed as the Item's (TotalYearlySales/SUM(TotalYearlySales))*100 The ABCIndicator is A for th...

How do I phrase this query in NHibernate with Lambda Extensions?

I'm using NHibernate with Lambda Extensions and I can't figure out how to phrase a specific kind of query. My application lets users tag other users in pictures. So there are Picture objects, and each Picture has one or more Tag objects, and each Tag object has one User object. I'm implementing a search feature. Given a search string, ...