.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Register
 
Win Surprise Gifts!!!
Congratulations!!!


Top 5 Contributors of the Month
MarieAdela
Imran Ghani
Post New Web Links

Subqueries and Unions

Posted By:      Posted Date: September 10, 2010    Points: 0   Category :Sql Server
 
I am attempting to create a SELECT statement that queries the UNION of two SELECT statements. Here is my UNION statement, which works fine (and all the fields in both tables are the same): SELECT * From table1 UNION SELECT * From table2 I then want to create a SELECT statement that queries the result set from this, which I would think would look something like this: SELECT MyField FROM (SELECT * From table1 UNION SELECT * From table2) WHERE Id=123 However, this keeps giving me the error message: Incorrect syntax near the keyword 'WHERE'. What am I doing wrong, and what should I be doing? Any help would be appreciated. Thanks. Nathan Sokalski njsokalski@hotmail.com http://www.nathansokalski.com/ Nathan Sokalski njsokalski@hotmail.com http://www.nathansokalski.com/


View Complete Post


More Related Resource Links

Subqueries and Unions

  
I am attempting to create a SELECT statement that queries the UNION of two SELECT statements. Here is my UNION statement, which works fine (and all the fields in both tables are the same): SELECT * From table1 UNION SELECT * From table2 I then want to create a SELECT statement that queries the result set from this, which I would think would look something like this: SELECT MyField FROM (SELECT * From table1 UNION SELECT * From table2) WHERE Id=123 However, this keeps giving me the error message: Incorrect syntax near the keyword 'WHERE'. What am I doing wrong, and what should I be doing? Any help would be appreciated. Thanks. Nathan Sokalski njsokalski@hotmail.com http://www.nathansokalski.com/ Nathan Sokalski njsokalski@hotmail.com http://www.nathansokalski.com/

Having subqueries within select statements

  
I want to know whether there are any consequences of having sub queries within a select statement so for example writing queries using this style.     SELECT NAME ,PROFILE CONVERT(VARCHAR(10), (SELECT PROFILEDATE FROM tblTable WHERE ProfileID= tst.[ID] AND [ProfileDate]=(SELECT MAX([ProfileDate]) FROM [EtpForecastDividendsStaging] WHERE [ProfileID]=qry.[ProfileID] AND [ProfileDate]<qry.[ProfileDate])), 120) , CONVERT(VARCHAR(10), (SELECT MAX([ProfileDate]) FROM tbltable WHERE [ProfileID]=qry.[ProfileID] AND [ProfileDate]<qry.[ProfileDate]), 120) FROM tbltable join tblothertable The above example is just a sample and not a query i had expect to compile, i am just using it as an illustration. I believe its bad programming, but i may be wrong. I tend to like normal traditional joins where you select the column and join based on the corresponding keys, but I am trying to grasp some justification for my approach.   Any ideas    

Datetime issues in Entity Framework in subqueries

  

I'm bonking my head against the wall trying to resolve this.

I've got three tables - Widgets, Users, and Subscriptions. I have a one-to-many reference set up between Users and Subscriptions since users can have multiple subscription, and a many-to-one between Widgets and Users.

What I'm trying to do is populate a gridview of widgets. Widgets have (only) one user, and those users may or may not have a subscription that's in the same category as that widget. So I've got

Widgets has a category_ID and user_ID (the owner)

Users have a user_ID and a name

Subscriptions have an expiration date, category_ID, and user_ID

Now before in LINQ to SQL I was doing something like

from c in dataContext.Widgets
let sub_days_left = (from d in dataContext.subscriptions
  where d.category == c.category
  && d.user_table.user_ID == c.user_table.user_ID
  select d.expiration_date).Single()
select new
{
  c.user_table.user_name,
  days_left = sub_days_left == null ? 0.0 : (sub_days_left - DateTime.Now).TotalDays
};

So I'm converting this all to Entity Framework and obviously TotalDays can't be used. So I'm trying to figure out how to accomplish this. I don't want to do an .AsEnumberable or .ToList() so that I can use Linq

Using subqueries in queries from several tables

  

I have 3 tables:

Customers that has fields (custid),

Orders that has fiels (orderid,custid,orderdate)

OrderDetails that has fields (orderid.qty)

I need using subquery write query that calculates a total quantity for each customer and month (monthly_qty) and running total for monthly_qty

The result should be like this:

custid      ordermonth                  monthly_qty      runqty
----------- --------------------------- ----------- -----------


Using subqueries

  

I have table Orders that contains fields (custid,orderid,orderdate) and OrderDetails that contains fields (orderid,qty). I need write a query that calculates a total quantity for each customer and month (monthly_qty) and running total for monthly_qty. Customers - it is custid, Month from order_date, total using qty.


Using subqueries

  

I have table

Orders:

-----------------------------

orderid custid orderdate

OrderDetails:

---------------------

orderid qty

-- Write a query that calculates a total quantity for each customer and month (monthly_qty) and running total for monthly_qty
-- Output:
custid      ordermonth                  monthly_qty      runqty
----------- --------------------------- ----------- -----------

I work with SQL Server 2008

 


Subqueries and derived queries

  

I work with SQL Server 2008

I have a question: Is it possibility of using "group by" or "order by " in subqueries? Why can I not do it? And what are variants for these situation?

I have a task. I have Orders table with fields below. I need return the three most recent orders for each customer.

custid      orderid     orderdate
----------- ----------- -----------------------

The result should be get with using derived query

custid      orderid     orderdate
----------- ----------- -----------------------


The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common ta

  

Any ideas how I can order inside the subquery? I get the following error. I have tried ORDER BY MAX([Count]) but it doesn't like this much either... Also, I don't want to use TOP in the subquery as I need to use all the records!

	SELECT
		 l.[Count]
		,m.LoweredEmail
	FROM 
		(SELECT 
			 COUNT(*) AS [Count]
			,System_User_ID
		FROM LOGSEARCH 
		GROUP BY System_User_ID
		ORDER BY [Count]) l
			
	INNER JOIN aspnet_Membership m
	ON CAST(m.UserId AS VARCHAR(36)) = l.System_User_ID
Error:
Msg 1033, Level 15, State 1, Line 11
The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP or FOR XML is also specified.


Categories: 
ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend