SQL Server :: Subquery / Write Query?

Mar 24, 2011

SQL query:

I have a hierarchy structure like:

B reports to A
C reports to A
D reports to A
E reports to B
F reports to B

and so on......

So, when A logins he should be able to see B,C,D(who are reporting to A) and also E,F(who are actually reporting to B and not directly to A)Please help to write such sql query.

View 5 Replies


Similar Messages:

SQL Server :: SQL Query - Select Subquery?

Jan 24, 2011

I have been utilising a SELECT query such as the following:

SELECT SRFILE.SR_GROUP, SRFILE.SROWNERSHIP, SRFILE.SRCONMAE, BSFLBWF_1.Incidentx,

BSFLBWF_1.Severityx, BSFLBWF_1.Openx, BSFLBWF_1.Closex, BSFLBWF_1.Closurex, BSFLBWF_1.Customerx, BSFLBWF_1.Availabilityx, BSFLBWF_1.KPIx,

CONVERT(Decimal, BSFLBWF_1.Minsx) AS Expr1, BSFLBWF_1.Unusedx, BSFLBWF_1.FirstIntx, BSFLBWF_1.RepeatIntx, BSFLBWF_1.Corex,

BSFLBWF_1.OffPeakx, BSFLBWF_1.NonCorex, BSFLBWF_1.DedTypex, CONVERT(money, BSFLBWF_1.SchoolChargex) AS Expr2, CONVERT(money,

BSFLBWF_1.LANMLEChargex) AS Expr3, BSFLBWF_1.KPIFailx, BSFLBWF_1.Overridex, BSFLBWF_1.Reasonx, BSFLBWF_1.Contactx,
SRFILE.SR_CLOCK, SRFILE.SR_BACK_REASON, CONVERT(int, BSFLBWF_1.Minsx) / CONVERT(decimal, BSFLBWF_1.FirstIntx)

* 100 AS PERCENTAGE
FROM support.SRFILE INNER JOIN
BSFLBWF AS BSFLBWF_1 ON BSFLBWF_1.Incidentx = SRFILE.SRONUMBER
WHERE (BSFLBWF_1.Closex IS NULL) AND (BSFLBWF_1.DedTypex = 'School' OR
BSFLBWF_1.DedTypex = 'LAN-MLE')
UNION ALL

The rest of the query continues as the above albeit selecting the same data from different tables and works fine.


The issue I have is that I want to Select some more data (another SELECT query) but with a different Where clause e.g. WHERE (BSFLBWF_1.KPIx NOT LIKE 'KPI%' OR
BSFLBWF_1.KPIx NOT LIKE 'Proactive%') AND (BSFLBWF_1.Availabilityx LIKE '')

The problem is that this WHERE clause returns data which the above in bold has null values for (and thus doesnt work). Im still selecting the same data its just the WHERE Clause has changed. Is it possible to run such a query? If so, can it be done via a sub query?

View 10 Replies

Sql Server Query/subquery Question?

May 20, 2010

I have a tables with data like this

Id BookId TagId
34 113421 9
35 113421 10
36 113421 11
37 113421 1
38 113422 9
39 113422 1
40 113422 12

I need to write a query (SQL Server) which gives me data according to the tags say if I want bookIds where
tagid =9 it should return bookid 113421 and 113422 as it exists in both the books, but If I ask data for tags 9 and 10 it should return only book 113421 as that is the only book where both the tags are present.

View 3 Replies

SQL Server :: ERROR: Subquery Returned More Than 1 Value. This Is Not Permitted When The Subquery Follows =, !=?

Feb 16, 2011

im doing an update in web page by pressing a button. At the start some of the update was from a trigger, but i decided to put the hole update statement in the web page as an updatecommand in VB. this is the error
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.The statement has been terminated.and this is the code.

[Code]....

the error is in the updatecommand. And i dont know what is it.

View 1 Replies

SQL Server :: How To Write A Query To Fetch Records

Mar 27, 2011

I have two tables and following are the structure of both the tables

Tbale 1
Field1 Field2 Field3
d1 01 26/03/2011
d2 02 26/03/2011
d3 03 27/03/2011

Table2
Field1 Field2 Field3
01 6.15 14.25
02 7.30 16.25
03 6.00 18.25

My expected output when i search records by table1.field3 (say 26/03/2011)

Tbale 1
Table1.Field1 Table2.Field2 Table3.Field3 Table2.field2 Table2.Field3
d1 01 26/03/2011 6.15 14.25
d2 02 26/03/2011 7.30 16.25
d3 03 27/03/2011

Inorder to get the above result, what query should i write?

View 4 Replies

SQL Server :: How To Write A SQL Query To Present Values Horizontally 2005

Mar 2, 2011

Suppose I have one table called [Code]....:

[Code]....

I want to see the specialist name and his jobs IDs horizontally.

[Code]....

A specialist may have

[Code]....

jobs. Suppose specialist

[Code]....

has 10 jobs where BEN has 5 jobs.

In this way I want to show specialist his jobs horizontally where the number of jobs may vary per specialist.

How can I do this in SQL?

View 13 Replies

DataSource Controls :: How To Write Query Using Pivot Logic In Sql Server 2005

Jan 7, 2010

i have a table for customer forecast which contains customer, week number and quantit as columns. now i need to write query and list the week as column for each customer and each cell should have a quantity. how to write query for this using pivot logic. i dont want to use cursor.

View 3 Replies

Verbatim - Why Write @ Before Sql Queries - Select Data From Database Then Write Query?

Oct 1, 2010

Suppose we want to select the data from the database then we write the query for that. Example:

SqlConnection con=new SqlConnection(Connetion name) string selectPkId = @"SELECT PK_ID FROM TABLE" SqlCommand cmd=new SqlCommand(selectPkId ,con);

So,my question is that why we basically use @ before the sql query.If I don't use @ before that then it again work fine (does not give any error), then what is need of using "@"?

View 1 Replies

SQL Server :: Subquery Returning More Than 1 Value?

Sep 13, 2010

I have a problem regarding this code:

select NotificationID, (select u.UserName from aspnet_Users u, notifications n where u.UserId = n.FromID) as 'UserName', [Message], DateCreated, TypeID from Notifications n, aspnet_Users u

View 2 Replies

SQL Server :: Subquery Returned More Than 1 Value?

Aug 29, 2010

[Code]....

I always received an error of: Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

View 1 Replies

SQL Server Subquery In SQL Compact Give Error 4.0?

Feb 15, 2011

query in SQL Server Express, which worked fine, now I am trying to do it in WebMatrix using a Compact 4.0 db but it gives me an error.

SELECT *, (SELECT
count(1)
FROM Posts
WHERE CategoryI
[code]...

View 7 Replies

SQL Server :: Select SubQuery / Bind Two Values Into One?

Feb 25, 2011

I have a SELECT query which grabs data from two different tables however when I run the query the CurrentCallOwner often pulls up the same name twice albeit with a different SLAFailed value. What I am trying to do is bind those two values into one. I thought that running a subquery and using a count would solve this however I all receive are a bunch ones. Do you know how I could bind those values into a single value (i.e. add them up?)

SELECT CurrentCallOwnerx, SLAFailed, COUNT(CurrentCallOwnerx) AS Expr1
FROM (SELECT BSFLBWF_1.CurrentCallOwnerx, COUNT(BSFLBWF_1.Incidentx) AS SLAFailed
FROM .SRFILE INNER JOIN
BSFLBWF AS BSFLBWF_1 ON BSFLBWF_1.Incidentx = SRFILE.SRONUMBER
WHERE (SRFILE.SR_GROUP = '38') AND (BSFLBWF_1.KPIFailx = '1') OR
(BSFLBWF_1.SchoolChargex > '0.00') OR
(BSFLBWF_1.LANMLEChargex > '0.00')
GROUP BY BSFLBWF_1.CurrentCallOwnerx
UNION
SELECT BSFWES_1.CurrentCallOwnerx, COUNT(BSFWES_1.Incidentx) AS SLAFailed
FROM SRFILE AS SRFILE_1 INNER JOIN
BSFWES AS BSFWES_1 ON BSFWES_1.Incidentx = SRFILE_1.SRONUMBER
WHERE (SRFILE_1.SR_GROUP = '38') AND (BSFWES_1.KPIFailx = '1') OR
(BSFWES_1.SchoolChargex > '0.00') OR
(BSFWES_1.LANMLEChargex > '0.00')
GROUP BY BSFWES_1.CurrentCallOwnerx) AS derivedtbl_1
GROUP BY CurrentCallOwnerx, SLAFailed

View 12 Replies

SQL Server :: Update Multiple Records - Subquery Returns More Than 1 Row

Jan 5, 2011

I have a table line (ID, LineNo, LineName) having values as :

1, 1, <blank>
2, 2, <blank>
3, 3, <blank>

ID is PK with Auto-Increment and LinNo(I am manually incrementing through ASP.Net Page while record insertion) Now i have to write a query such that it updates LineName column from line table which is blank(as shown above) with values from table linet(linenamet) having values as :

a
b
c

My trial query is :
update line set LineName = (select linenamet from linet);

It gives ERROR as : Subquery returns more than 1 row
Expected Result : After Updation it should show line table as :
1, 1, a
2, 2, b
3, 3, c

View 8 Replies

SQL Server :: Difficult Insert With Subquery / Assign All Products Without Pictures Into A Category Located On A Mapping Table?

Nov 3, 2010

I'm working on an ecommerce project that is missing 5,000 product pictures.

I am trying to assign all products without pictures into a category located on a mapping table.

I know an insert can be accomplished from one table (or subquery) to another but I only need to find the product IDs and insert them with default values into the category mapping table that has all non-null columns.

The following code is the farthest I can get without help (I'm not strong in SQL), it won't work because my subquery returns many rows while the SQL statement is designed to insert one - I don't know how to handle this...

[Code]....

Any help would be appreciated from those more experienced than I. BTW... there are no known search results for this type of solution - if there is even a solution.

View 2 Replies

ADO.NET :: How To Write SQL Insert Into Query Using LINQ

Oct 13, 2010

How I can write following SQL query using LINQ. There is no default constraint define for the ID column so we have explicitly pass the NEWID().

I want to replicate the products of a company to another company.

Insert Into Products Select NEWID() AS ProductID, ProductName, CompanyID = @DestinationID, ProductCategoryID From Products Where CompanyID = @SourceID

Also once I get these result how can I use InsertAllOnSubmit to insert all the records at once without looping it.

View 2 Replies

How To Write Sql Query To Retrive Data

Aug 16, 2010

how to write sql query to retrive data as follows....
've a table with two columns..

brno brname
100 hyd,hyderabad
102 cni,chennai
03 mm,mumbai
104 kalk,kolkata

View 4 Replies

DataSource Controls :: How To Write The Sql Query Or Have To Do Programmatically

Mar 3, 2010

I have a table with columns A and B, values as follows..

A B
-- --
1 11
1 12
1 13
2 21
2 22
2 23

View 7 Replies

Write A Sql Query To Display Top 25 Records With A Catch?

Oct 11, 2010

I need to write a query as follows which will be bound to a grid

select top 25 * from ErrTable Order by DateErrorad Desc

However, I need to write this query to return only 25 records at a time, but when a user clicks next it will display the next 25 most recent records from the db.

View 8 Replies

Web Forms :: Url Write Breaks Query String On Postback?

Nov 18, 2010

i am having a problem on my aspx page, what i did was i wrote a simple url re-wrte rule in my aspx page, after i write my url re-write rule my query string looks like this [URL] idea been to writing a rule to accept that above query for user friend seo url string, but when i am in that page i cant click any of my datalist page indexes ...it will redirect to [URL] i dont know how to fix this proble, i am trying s hard ,but i cant make it work, i think page post back changing my url re-write link.

View 2 Replies

ADO.NET :: How To Write A Query That Pulls Records From A Master Table

Mar 18, 2011

Being new to Linq and Entity Framework, I find myself struggling to write queries that are relatively simple in SQL. My latest challenge is how to write a query that pulls records from a master table and includes a column for aggregating data from related detail records. Let's start with this example:

List<Customer> list = (from x in Customer select x.Customer_ID, x.CustomerName).ToList();

Now I'd like to modify this so that I include a column indicating how many orders each customer has, where there is a one-to-many relationship between the Customer and Order entities. I suspect the solution might result in a List of an anonymous type rather than the List of Customer like my example, but that would be

View 1 Replies

DataSource Controls :: Write SQL Query Result To Label

Mar 22, 2010

I have the following code which writes to a Gridview:

[Code]....

Using the same methods of connecting to the database, how can I amend this code to write the result of a second query (which only returns a number) to a label?

View 11 Replies

DataSource Controls :: SQL Query Re-Write In Linq To Entity?

Jan 26, 2010

Need to figure out the correct way to do a simple SUM function in Linq, but also manipulate one of the feilds coming back by doing a Substring on it, here is the original SQL query:

[Code]....

View 1 Replies

SQL Reporting :: Write Query In Run Time In SSRS 2005

Jan 9, 2011

i want to write query in run time in SSRS.is it possible??...

View 1 Replies

DataSource Controls :: To Write The Query - Retrieve The Records?

Jan 16, 2010

i have a table for customer forecast with following fields.. customer, year, week, quantity. now i need to write the SP to retrive the records based on the from week,year and to week,year... like this... from 45th week of 2009 to 25th week of 2010. how can write the query...

View 4 Replies

DataSource Controls :: Write Alter Schema Query?

Jun 18, 2010

how do i write my alter schema query in Asp.net

using the sqlclient object..

View 3 Replies







Copyrights 2005-15 www.BigResource.com, All rights reserved