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

Top 5 Contributors of the Month
david stephan
Gaurav Pal
Post New Web Links

SQL query question

Posted By:      Posted Date: September 09, 2010    Points: 0   Category :ASP.Net
I am struggling with a query from a table that looks like this:ID | Rep     | Account---------------------------1  | Bob     | Safeway2  | Bob     | Krogers3  | Chris   | Krogers4  | Maria  | Safeway5  | Sam    | AlbertsonsI am trying to query all of a rep's teammates across all accounts. For example, Bob works on both Safeway and Krogers accounts. I want a query that outputs all reps who work on those accounts. If Bob is the input, Bob, Chris, and Maria would be the output.My stab is:SELECT DISTINCT tblUserRelationships.RepFROM tblUserRelationships INNER JOIN vwRelatedAccounts ON tblUserRelationships.Account = vwRelatedAccounts.Accountwith vwRelatedAccounts looking like:SELECT        AccountFROM            dbo.tblUserRelationshipsWHERE        (Rep = N'bob')The problem is I can't paramatize vwRelatedAccounts and I can't figure out how to nest second statement into the first statement.Any suggestions?Thanks!Bradley

View Complete Post

More Related Resource Links

MDX query using linked server - performance question

Hi, I'm having a strange performance behavior. When I’m running a mdx query on management studio, it runs for 10 minutes (its mainly calc members on a large cube) When I run the same query via linked server and openquery, it runs for 20 minutes. Is the SQL from some reason does a parsing or running the query twice? The server is sql2008 UP1 on 64bit. and the "allow in process" is checked. its the default in sql2008 Thanks in advance, Yoav  

Query Designer question

I am not sure where to post this question, so I guess it is here: I have a VERY large query that joins quite a few tables. I am using the graphical designer to get the tables all linked together. However the graphical designer seems to place the order of the tables in a random order resulting in a hodge podge of left and right joins.   In the graphical designer, is there a way to order the from clause?

Question about SQL query

Hello everyone, I am having a difficulty making a query. This is the situation: I have two tables: Persons and Work Persons: ID, LastName, FirstName Work: ID, PresonID, WorkType, Start, End WorkType in Work table is nvarchar type and Start and End is datetime type. There is 14 of possible combinations in WorkType I need to make a query to get all the persons and the summed number of minutes (DATEDIFF(minute,Start, End)) by every WorkType. Thanks in advance

Troubleshooting query question?

Hi All, We have below query which is working perfectly fine:-  ;WITH CTEA AS ( SELECT ....... FROM TABLEA LEFT OUTER JOIN TABLEB ON TABLEA.COLUMN1 = TABLEB.COLUMN2 ), CTEB AS ( SELECT ........ FROM CTEA -------------- record count of CTEA is 2 million records EXCEPT SELECT ... FROM TABLEC ), CTEC AS ( SELECT ..... FROM CTEB GROUP BY ........ ) SELECT * ----------------------- records count of CTEC is 30-40K records, execution time 2 mins FROM CTEC  But we have to do below query instead of "SELECT * FROM CTEC":- SELECT ......... FROM CTEA INNER JOIN CTEC ON CTEA.COLUMN0 = CTEC.COLUMN0 AND CTEA.COLUMN1 = CTEC.COLUMN1 AND CTEA.COLUMN2 = CTEC.COLUMN2 AND CTEA.COLUMN3 = CTEC.COLUMN3 AND CTEA.COLUMN4 = CTEC.COLUMN4 But above is not giving us any result for more than 30 mins, finally we have to kill the query, we tried it is not doing any cartesian product, so can anybody help us to troubleshoot where I'm doing mistake and what is wrong? Please feel free more questions for doubts, Also my 5 columns where I'm doing join operation are VARCHAR columns. Any Help ? Also let me know if their any other way to write above query instead of JOIN, main purpose is to reduce the execution time. Thanks Regards, Kumar

Mdx query question.



In the next query, I expected the same value for TotalSubCategory and Total1SubCategory calculated measures.

Why are these values distincts?





MEMBER [measures].[TotalSubCategory] AS ([Product].[subCategory].[All],[Measures].[Sales Amount])
MEMBER [measures].[Total1SubCategory] AS ([Product].[subCategory].[All],[Product].[Category].currentmember,[Measures].[Sales Amount])



   [Measures].[Sales Amount]

 } ON 0





   ) ON 1

FROM [Adventure Works];

Query Performance Question



I have a view , it gets the data from almost 20 joins , couple case statements and some subqueries and it is not materialized.

I am loading this view's data into a table so it takes 80 minutes to load with select into clause.

there are almost 100 milyon rows in that view.

here is the view , it will be great if I can take 80 min down to 20-30 min.

 , l =
  case when cd.a is not null then (select name from bbb where dd = caz.dd)
    when cd.a is not null then (select name from cn where d_id = caud.d_id)
    when cd.a is not null then (select name from cn where di_id = cap.d_id)
    else null
 ,m= case when cd.a is not null then (select name from bbb where dd = caz.dd)
    when cd.a is not null then (select name from cn where d_id = caud.d_id)
    when cd.a is not null then (select name from cn where di_id = cap.d_id)
    else null
  cd inner join  cpcs on
  cd.c = cpcs.c
 left outer ........
 left outer ........
 and cdid = (select id from c where class = 'bbbbe' and

SSIS - Passing a date parameter to a SQL Query question again....


I need to pass a date string to my sqlcommand inside of my "datareader source".  I read some of the questions about setting expression variables but I can't seem to figure out how to do it.  I pasted the query below.  The table name contains a date in it so I need to pass that date as a string into my sqlcommand at runtime.  Do I create a variable with the entire SQL command in it or can I just create a single variable that holds the date and just reference that in my datasource reader sqlcommand property?  Can someone assist me with the syntax for creating the expression?

     pages_20101006.`date` AS full_date,
     pages_20101006.`cat` AS category_no,

Thanks very much,



data shaping question - can this much conditional processing can be done within the linq query?



I have a scenario where I need to map the value of a field to a different value... easier explained with simple example:

when actual field data is "RED" I need to return "some other data"
when actual field data is "BLUE" I need to return "something else" 

so I've written linq to sql statements that project and shape, but I've never had to include any 'conditional' processing, can you?

Query with a question Mark reference in .NET


Ok... I know this is likely easy and it should be.  Foot in mouth 

I have a query in Microsoft Access 2003 or 07'.  Like the following:  Let's assume this query is named 'GetDudesStuff'

Here is the Raw SQL of the Query in Access: SELECT avail.MLS_CODE, avail.AV_DESCAD, CLASS.CL_DESC, CLASS.CL_DESCSUB, customer.CP_NAME, customer.CP_EMAIL, MFG.MF_NAME, avail.AV_DESCLN, avail.AV_UED, avail.AV_MACLASS, avail.AV_MATYPE
WHERE (((avail.MLS_CODE)=[?]))

In .NET how do I call this Query and add in the unnamed query?

I tried the AccessDataSource class, or just the OleDBDataSource/OleDBCommand with adding a parameter.  But it no worky...
(VB or C# me no care I can translate)

Question about a custom InfoPath 2003 Query


I have searched the web and youtube but cannot find anything. I have published a InfoPath form to a SharePoint list, and created a "Receive" connection. I need help creating the search to pull data using 2 dropdown boxes and a Query button. Here are the steps. (this is NOT a web form, just a standard form)

1. The user will choose Q1, Q2, Q3 or Q4 from the 1st dropdown box.

2. Then choose Submitted, Review, or Stage3 from the 2nd dropdown box. 

3. Then click the Query button. 

4. Then I want the results to be displayed in a repeating table with controls. (which I already have setup)

Any help to build the query logic would be greatly appreciated! 

Thank you for your time!''


A question about an SQL query


I would like to get the number of messages for each post on my forum, with this syntax I get them but I cannot get the ones with "0" messages.

SELECT        dbo.Posts.PostID, dbo.Posts.ForumID, dbo.Posts.Title, dbo.Posts.Details, dbo.Posts.VisitorID, COUNT(dbo.Messages.MessageID) AS CountMessages,
                         dbo.Posts.CreateDate, dbo.Posts.UpdateDate, dbo.Posts.OnStatus, dbo.Posts.IPaddress, dbo.Posts.TopicCreateName, dbo.Posts.PostViews
FROM            dbo.Posts INNER JOIN
                         dbo.Messages ON dbo.Posts.PostID = dbo.Messages.PostID
GROUP BY dbo.Posts.PostID, dbo.Posts.ForumID, dbo.Posts.Title, dbo.Posts.Details, dbo.Posts.VisitorID, dbo.Posts.CreateDate, dbo.Posts.UpdateDate, dbo.Posts.IPaddress,
                         dbo.Posts.TopicCreateName, dbo.Posts.PostViews, dbo.Posts.OnStatus


SQL Query Question


When i run a SQL query on a single table and here is the data (this is just a sample, errors might be more than 10)

time    total    Error
00:16    6    10000(E)
00:20    4    10000(E)
00:46    2    10000(E)
01:01    2    10000(E)
01:40    2    10000(E)
02:07    2    10000(E)
02:52    1    10000(E)
04:27    2    10000(E)
04:29    6    10000(E)
04:32    4    10000(E)
04:49    2    10000(E)
04:50    2    10000(E)
06:18    2    10000(E)
09:04    1    10000(E)
10:57    4    10000(E)
10:58    4    10000(E)
00:36    1    9401(E)
00:37    1    9401(E)
00:57    1    9401(E)
00:58    1    9401(E)
01:32    1    9401(E)

Query Question

insert into CompletedTable (Year, Qtr, Resource_ID ,Resource_Name,Project_Name,Cost_Actual, OT, OB, OQ, DRE, PIR, OverAll_Actual_OT, OverAll_Actual_OB, OverAll_Actual_OQ, OverAll_Actual_DRE, OverAll_Actual_PIR, OT_Ratings, OB_Ratings, OQ_Ratings, DRE_Ratings, PIR_Ratings, Overall_Rating)
select '2002', '1', c.Resource_ID, b.Resource, a.Project, Cost_Actual, F.OT , G.OB, OQ_Rating,DRE_Rating, PIR_Rating,
SUM(Case When F.OT >= 0 then Cost_Actual end) over (partition by Resource), 
SUM(Case When G.OB >= 0 then Cost_Actual end) over (partition by Resource),
SUM(Case When OQ_Rati

MS SQL Server: Search All Tables, Columns & Rows For Data or Keyword Query

If you need to search your entire database for specific data, this query will come in handy.

So when a client needs a custom report or some sort of custom development using Great Plains, most of the time I will have to track down the data in the system by running this query and find the table(s) it is in.

How to Encrypt Query String Parameters in ASP.NET

Encrypt Query String Parameters in ASP.NET.u can send secure data one page another page u can also use query string to encrypt

Content Query Web Part missing in SharePoint 2010

If you don't see content query web part listed in the web parts list, this is because you have not enabled "Search Server Web Parts" feature in site collection features. Enable this feature and content query web part will show in the list of web parts.

Some basic MVC question

  1. In regular asp net I can run (debug) the application but I can either "view in browser" that gives me the option to view the application and write code on same time.

In MVC I can't find this option.

  1. What is the basic DATA MODEL for working with SQL DB.

I mean with no any framework (entity framework, Sub sonic, Link to SQL etc')

I look for basic application that works with data but with no fw.

  1. What is the popular DB framework that working with data.
  2.  I am looking on  mvc series


C# Soup To Nuts  (the best series ever)


I watch some of the MVC video, seems that the music store is good but I have to wait to the other part.

Does any one know and recommend   a Microsoft MVC tutorial?





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