subquery

Subqueries in linq

Hello, I've been trying for a couple of days to write to "translate" this query in LINQ with no success so far. Could you guys please help me? I would also appreciate some explanation to learn actually something out of it. Here is the T-SQL query: SELECT R.ResourceID, R.DefaultValue FROM Resources as R JOIN (SELECT [t0].[NameResou...

How to avoid repeating a lengthy CORRELATED subquery?

Here is the code to help you understand my question: create table con ( content_id number); create table mat ( material_id number, content_id number, resolution number, file_location varchar2(50), file_size number); create table con_groups (content_group_id number, content_id number); insert into con values (99); insert into mat values...

SQL: "NOT IN" alternative for selecting rows based on values of *different* rows?

Hello, everyone: How do you make an SQL statement that returns results modified by a subquery, or a join - or something else, that deals with information you're trying to return? For example: CREATE TABLE bowlers ( bowling_id int4 not null primary key auto_increment, name text, team text ); Someone might incorrectly be on more tha...

Sub queries and sorting? (ORDER BY)

So my syntax is apparently correct in all three cases (PostgreSQL isn't grousing about anything) but the results come back in the same order with all three of these queries. Even stranger when I add/remove DESC from any of the following it has no impact either. Is it possible to sort results based on elements of a sub query or not? So...

dependent subquery to self join

How do I change the following dependent subquery to self join? SELECT d.name, d.created, (SELECT SUM( q1.payout ) FROM client AS q1 WHERE q1.uid = d.uid) AS payout, (SELECT COUNT( q2.uid ) FROM client AS q2 WHERE q2.uid = d.uid AND q2.winning =1) AS cnt FROM client AS d group by d.name, d.created ORDER BY cnt DESC LIMIT 0 , ...

Subquery returns more than one row SQL

Hi there, I have executed a code SELECT CASE b.ON_LOAN when 'Y' then 'In Lib' when 'N' then (SELECT c.duedate from book_copy a, book b, loan c where b.isbn = 123456 and a.isbn = b.isbn and a.book_no = c.book_no) END AS Availability, a.isbn, a.class_number FROM book_copy...

Nhibernate DetachedCriteria Left Outer Join on subquery

Hi! I'll only try to present the main part of the problem, because the whole situation is much more complicated - I am unable to achieve the following with DetachedCriteria SELECT * FROM User LEFT OUTER JOIN GroupItem ON User.ID = GroupItem.UserID AND _groupItemRestrictions_ There can be multiple GroupDefinitions, User can belong to ...

How can I join these two queries?

Here's what I want to accomplish: Select all rows from table "posts" while also fetching linked entities, such as the author (from table "users") and the names of the categories the post belongs to (from table "categories"). These are my two queries so far: This one fetches the posts: SELECT posts.* , users.authorName...

Subquery returning multiple columns

Hi, I am writing a select subquery that returns a COUNT. However, I need to use a HAVING clause in the subquery and as a result need to specify that column in my subquery SELECT. So the result is that I have 2 columns: COUNT and column_for_having_clause. Is there any way that i can tell SQL to take just the first column. I get an ...

How to use results of one sql query within a sub-query

Hey Guys I have to answer the following question "For each year in the database, list the year and the total number of movies that were released in that year, showing these totals in decreasing order. That is, the year(s) with the largest number of movies appear first. If some years have the same number of movies, show these in increasi...

NHibernate 2.1: LEFT JOIN on SubQuery with Alias (ICriteria)

Hello, I am basically trying to create this query with NHibernate ICriteria interface: SomeTable 1:n AnotherTable SomeTable has columns: PrimaryKey, NonAggregateColumn AnotherTable has columns: PrimaryKey, ForeignKey, AnotherNonAggregate, YetAnotherNonAggregate SELECT table1.NonAggregateColumn, subquery.SubQueryAggregate...

How do I perform update query with subquery in Access?

I want to port this SQL query, which works just fine on SQL Server, MySQL, and Oracle, to an Access database. How do I do that? Right now it prompts me for a Company_ID for some reason. Edit: I was getting the prompt because I forgot to first create the Company_ID column in VendorRegKeys. Now I am getting error "Operation must use an...

MySql View - Value from one column where some other column is max

I have Table1 and Table2 related on Table1.ID. There can be zero or more Table2 records for a given Table1.ID. I have a view where I want to get Table2.Value where Table2.ID is max for a given Table1.ID. A friend suggested a derived table, but that requires a subquery in the from clause, and MySQL doesn't like that. Are there any oth...

Can layered subqueries spanning multiple tables reference a parent query?

I have three tables setup: POSTS, DISCUSSIONS, and COMMENTS. The layout is as follows: POSTS id | user_id | title | body | created DISCUSSIONS id | post_id | user_id | title | body | created COMMENTS id | post_id | discussion_id | user_id | title | body | created A post hasMany DISCUSSIONS, which then hasMany COMMENTS. What I need ...

Joining to a limited subquery?

I have this releases table in a SQLite3 database, listing each released version of an application: |release_id|release_date|app_id| |==========|============|======| | 1001| 2009-01-01 | 1| | 1003| 2009-01-01 | 1| | 1004| 2009-02-02 | 2| | 1005| 2009-01-15 | 1| So for each app_id, there will be multi...

SQL Subselect in an Update

Oh great Stackoverflow, I beseech thee... I need to do the following: CREATE UNIQUE INDEX UserName ON Emp(UserName) -- Sheesh! UPDATE Emp SET UserName = Left(FirstName,1)+LastName WHERE NOT exists an employee with that UserName already. ...

How can I use a variable from a subquery?

I have this query: (SELECT e.IdEvent,e.EventName,e.EventSubtitle,e.EventDescription,l.LocationName,e.EventVenue,EventStartDate,e.EventEndDate,e.EventHost,c.CategoryName,l.LocationCity,l.LocationState,e.isTBA, (SELECT s.status FROM jos_rsevents_subscriptions s WHERE s.IdUser = 72 AND s.IdEvent = e.IdEvent LIMIT 1) as status FROM jos_rse...

Selecting only authors who have articles?

I've got two SQL Server tables authors, and articles where authors primary key (AuthorID) is a foreign key in the articles table to represent a simple one-to-many relationship between authors and articles table. Now here's the problem, I need to issue a full text search on the authors table based on the first name, last name, and biograp...

SQL statement to include rows that have no matches in a correlated subquery

I have the correlated subquery below. Each id (147,148,149) in table1 has many records in table2. The id 148 however has no records that match the time condition in the statement. Therefore it is not included in the output. I want it to be included with a 0 for the count column. What needs to be changed? SELECT b.fkid, COUNT(DISTIN...

SQL Select subquery

I'm a little new to SQL and have come across the following problem. I have a table with company details on it which is joined to a contact table by an enqID. Within the contact table, there are 4 different types of contacts which may or may not have an entry. These are differentiated by a ctcTypID (1 - 4) I would like to produce a qu...