I have three tabels A,B,C having relation of primery & forign key, and i want to fetch data from all three.
In case if only A have the values and B & C dont have values, then also i want values.
But my simple sql statement dose not show any data, if B & C dont have values.
Any one tell me that how to fetch data from this type of Situation???????
How do I determine which method I should use ifI want to optimize the performance of a database.I took Northwind's database to run my example.My query is I want to retrieve the Employees' Firstand Last Names that sold between $100,000 and$200,000.First let me create a function that takes the EmployeeIDas the input parameter and returns the Employee'sFirst and Last name:CREATE FUNCTION dbo.GetEmployeeName(@EmployeeID INT)RETURNS VARCHAR(100)ASBEGINDECLARE @NAME VARCHAR(100)SELECT @NAME = FirstName + ' ' + LastNameFROM EmployeesWHERE EmployeeID = @EmployeeIDRETURN ISNULL(@NAME, '')ENDMy first method to run this:SELECT EmployeeID, dbo.GetEmployeeName(EmployeeID) ASEmployee, SUM(UnitPrice * Quantity) AS AmountFROM OrdersJOIN [Order Details] ON Orders.OrderID =[Order Details].OrderIDGROUP BY EmployeeID,dbo.GetEmployeeName(EmployeeID)HAVING SUM(UnitPrice * Quantity) BETWEEN100000 AND 200000It's running in 4 seconds time. And here are theStatistics IO and Time results:SQL Server Execution Times:CPU time = 0 ms, elapsed time = 0 ms.SQL Server Execution Times:CPU time = 0 ms, elapsed time = 0 ms.SQL Server Execution Times:CPU time = 0 ms, elapsed time = 0 ms.SQL Server parse and compile time:CPU time = 17 ms, elapsed time = 17 ms.(3 row(s) affected)Table 'Order Details'. Scan count 1, logical reads 10,physical reads 0, read-ahead reads 0.Table 'Orders'. Scan count 1, logical reads 21,physical reads 0, read-ahead reads 0.SQL Server Execution Times:CPU time = 3844 ms, elapsed time = 3934 ms.SQL Server Execution Times:CPU time = 3844 ms, elapsed time = 3935 ms.SQL Server Execution Times:CPU time = 3844 ms, elapsed time = 3935 ms.SQL Server parse and compile time:CPU time = 0 ms, elapsed time = 0 ms.Now my 2nd method:IF (SELECT OBJECT_ID('tempdb..#temp_Orders')) IS NOT NULLDROP TABLE #temp_OrdersGOSELECT EmployeeID, SUM(UnitPrice * Quantity) AS AmountINTO #temp_OrdersFROM OrdersJOIN [Order Details] ON Orders.OrderID =[Order Details].OrderIDGROUP BY EmployeeIDHAVING SUM(UnitPrice * Quantity) BETWEEN100000 AND 200000GOSELECT EmployeeID, dbo.GetEmployeeName(EmployeeID),AmountFROM #temp_OrdersGOIt's running in 0 seconds time. And here are the Statistics IOand Time results:SQL Server Execution Times:CPU time = 0 ms, elapsed time = 0 ms.SQL Server Execution Times:CPU time = 0 ms, elapsed time = 0 ms.SQL Server Execution Times:CPU time = 0 ms, elapsed time = 0 ms.SQL Server parse and compile time:CPU time = 0 ms, elapsed time = 0 ms.SQL Server Execution Times:CPU time = 0 ms, elapsed time = 0 ms.SQL Server Execution Times:CPU time = 0 ms, elapsed time = 0 ms.SQL Server Execution Times:CPU time = 0 ms, elapsed time = 0 ms.SQL Server parse and compile time:CPU time = 0 ms, elapsed time = 0 ms.Table '#temp_Orders0000000000F1'. Scan count 0, logicalreads 1, physical reads 0, read-ahead reads 0.Table 'Order Details'. Scan count 830, logical reads 1672,physical reads 0, read-ahead reads 0.Table 'Orders'. Scan count 1, logical reads 3, physical reads 0,read-ahead reads 0.QL Server Execution Times:CPU time = 15 ms, elapsed time = 19 ms.(3 row(s) affected)SQL Server Execution Times:CPU time = 15 ms, elapsed time = 19 ms.SQL Server Execution Times:CPU time = 15 ms, elapsed time = 20 ms.SQL Server parse and compile time:CPU time = 0 ms, elapsed time = 1 ms.(3 row(s) affected)Table '#temp_Orders0000000000F1'. Scan count 1,logical reads 2, physical reads 0, read-ahead reads 0.SQL Server Execution Times:CPU time = 0 ms, elapsed time = 3 ms.SQL Server Execution Times:CPU time = 0 ms, elapsed time = 3 ms.SQL Server Execution Times:CPU time = 0 ms, elapsed time = 3 ms.SQL Server parse and compile time:CPU time = 0 ms, elapsed time = 0 ms.By the way why "SQL Server Execution Times"exists 3 times and not just one time?Summary:The first code is clean, 1 single SELECT statement buttakes 4 long seconds to execute. The logical reads arevery few compared to the second method.The second code is less clean and uses a temp table buttakes 0 second to execute. The logical reads are waytoo high compared to the first method.What am I supposed to conclude in this example?Which method should I use over the other and why?Are both methods good depending on which I prefer?If I can wait four seconds, it's better to reduce the logicalreads in order to provide less Blocking on the live tablesin a heavily accessed database?Which method should I choose on my own database?Calling a function like dbo.GetEmployeeName getsprocessed per each returned row, correct? That meansIf i had a scenario where 1000 records were to be returnedwould it be better to dump 1000 records to a temp tablevariable and then call a function to process each recordone at a time?Or would the direct approach without usinga temp table cause slower processing and moreblocking/deadlocks because I am calling the functionper each row as I am accessing directly from the tables?Thank you
we had some slow down complaints lately and this query seems to be the culprit almost every single time. The estimated execution plan is a clustered index seek as there is a clustered index on the uidcustomerid column. setting profile statistics on shows that every time it executes it does an index seek.
profiler session showed a huge number of reads for these queries depending on the value being looked up. 1500 through 50000. i set up profile io on and the culprit is lob logical reads. everything else is 0 or very low. in this case lob logical reads is over 1700.
3 of the columns in the select statement are text columns. when i take them out of the query the lob logical reads drops to 0 and goes up incrementally as i add each column back in.
is there anyway to improve the performance without changing data types to varchar(max)?
select SID,Last_name,Name_2,First_name,Middle_initial,Descriptives,Telephone_number,mainline,Residence,ADL, DID_number,Svce_street,Svce_town,Svce_state,Svce_appt,Mailing_street,Mailing_town,Mailing_state,Mailing_appt, Mailing_zip,Listing,Addl_listing,Published,Listed,Gold_number,PIN,status,SSnumber,tax_jurisdiction, Bill_date,Past_balance,Service_start_date,Service_end_date,LOA,FCC_type,Line_type,I_W,Jacks,Voice_messaging, vms_ring_cycles,CCS,phonesmarts,ringmate,voice_dialing,Bill_detail,Contact_Number,Contact_extension, Best_Time,suspend,suspend_start,suspend_end,credits_allowed,credits_granted,home_region,Calling_Plan,Local_Plan, Local_Plan_Rate,Flat_Rate,Sales_agent,Community,Building_Mgmt,How_Heard,Incentive_1,Incentive_1a,Incentive_1b, Incentive_1c,Incentive_2,Incentive_2a,Incentive_2b,Incentive_2c,Incentive_3,Incentive_3a,Incentive_3b, Incentive_3c,block_operator,block_collect,block_group,block_adult,block_call_return,block_repeat_dialing, block_call_trace,block_caller_id,block_anonymous,block_all_high_toll,block_regional_and_ld,block_DA_Call_Completion, block_DA,block_3rd_party,bank,prepayment,dial_around_number,custid,waive_interest,Financial_Treatment, Other_Feature_1_code,Other_Feature_1_rate,Other_Feature_2_code,Other_Feature_2_rate,Other_Feature_3_code, Other_Feature_3_rate,Other_Feature_4_code,Other_Feature_4_rate,Partial_Account,mail_date,snp_1_date,snp_2_date, terminate_date,snp1notified,snp1peak,snp1offpeak,snp2notified,snp2peak,snp2offpeak,avg_days_paid,Pulled_Ld,SNP1, SNP2,Treatment,Collections,Installment,Nynex_BTN,LD_rate,local_discount,to_month,rounds_up,full_package_made, local_made,PIC,LPIC,tax_exempt_local,tax_exempt_federal,CommissionedAgent,LDRateID,UidCustomerId, accVchLineClassUSOC,block_Inter_Reg_LD,block_international,block_DA_3rd_Collect,block_DH2,block_ISP_2,block_ISP_3, block_ISP4_3_GBAS,block_ISP3_3_GBAS,block_collect_only,block_LD_Reg_DA,block_usage_based,block_ISP5_3_GBAS, block_ISP5_2_GBAS,block_group_adult,csr_PIC,csr_LPIC,csr_SA,csr_exception,cutover_status,cutover_datetime, OutsideAgent,prfVchAttributes,uidResellerID,Category,uidDealID from profiles where UidCustomerID in (352199267)
ReportModel is created ,there is only a named query in DSV ,it has a few tables in it(The relationship are inner joins and outer joins).
The question is how could I create a unique logical primary key to identify each unique row in the named query dataset, and also you cannt generate a model unless the named query has a logical primary key . how can I solve this problem,any help?
Hi, I have a table Projects. This table has ProjectID and Version as PK. The Version starts at 1 and everytime a project is changed, I save the project with the same ProjectID and increase the Version by 1.How can I create a query that get all Projects with the latest Version? Thx
I have a Properties table like thisPropertyID PropertyValue 1 Address 2 City 3 Stateetc.and a UserProfile table like thisUserID PropertyID PropertyValue1 1 123 Main Street1 2 Denveretc.How do I write a query that can populate a registration page with Address,City, State as labels and 123 Main Street, Denver, as TextBox text?
Hi,I have included here my webform here.i need some assistance here with code.my webform contains two parts.the 1st part is office info and the 2nd part is client info.i also have two table named office_info and client_info.1st part is populated from the table office_info as soon as the office name is chosen from the dropdownlist.in my scenario,when user selects officename from dropdownlist,then textboxes correspondingto address and email gets populated by the related data from table office_info.2nd part is client info.here there are 3 textboxes(for name,age,address) to collect the data from the client using the form.these data gets posted to new row in table client_info as soon as user clicks on the save button.Now my actual question starts here.when user selects the option from the dropdonwlist the office info displays,now when he fills the client info part and clicks the save button,i want all the data to go to the table client_info in such a way that all the data fromthe client info part plus the id of the office also go along with it.eg: when user clicks the save button.i want data to get submitted in table client_info in this way.(id,name,age,address,off_row_id) (1,jack,25,US,1) here off_row_id is the id from the below table.my table office_info is like this (id,off_name,address,email) eg(1,xyz,ny,xyz@xyz.com) well can anyone tell me how to write query to do insert,edit,update,delete query in this case using c# and sql?here is the scenario <%@ Page Language="C#" %><!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"><script runat="server"></script><html xmlns="http://www.w3.org/1999/xhtml" ><head runat="server"> <title>Untitled Page</title></head><body> <form id="form1" runat="server"> <div> Office Info:<br /> <hr /> <br /> Office name: <asp:DropDownList ID="DropDownList1" runat="server" Width="63px"> <asp:ListItem>ABC</asp:ListItem> <asp:ListItem>XYZ</asp:ListItem> </asp:DropDownList><br /> <br /> Address: <asp:TextBox ID="TextBox1" runat="server"></asp:TextBox><br /> <br /> email: <asp:TextBox ID="TextBox2" runat="server"></asp:TextBox><br /> <br /> <hr /> </div> Client info:<br /> <br /> name: <asp:TextBox ID="TextBox3" runat="server"></asp:TextBox><br /> <br /> age: <asp:TextBox ID="TextBox4" runat="server"></asp:TextBox><br /> <br /> address:<asp:TextBox ID="TextBox5" runat="server"></asp:TextBox><br /> <br /> <br /> <hr /> <asp:Button ID="Button1" runat="server" Text="save" /> <asp:Button ID="Button2" runat="server" Text="cancel" /> </form></body></html> thanks.jack.
hello everyone. i want to know how asp.net works with sql database. can i have a link to the article where i can perform from basic to advance sql query using asp.net(C#)? (in context of vwd 2005 and sql express ) thanks. jack.
I have two table named tbl_Scale and tbl_NGTrDAMaster tbl_Scale(ScaleID,ScaleName,ScaleLB,ScaleUB,ScaleSI1,ScaleSI2,ScaleSI3) here scale id is prim key tbl_NGTrDAMaster(TrDaId,ScaleID,CityTypeID,DAAmount) no prim key and we get CityTypeID from xml databinder....... In my form thr is two drop down list one for scale name and another for city type id this is the data form tbl_NGTrDAMaster 17 1 1 555 18 3 1 777 19 3 1 999 8 1 1 777 5 5 1 34634 20 1 1 52352 27 1 1 6666 23 5 1 12412 12 2 1 235235 13 3 1 456456 14 5 1 1000000 15 4 1 60000 16 5 1 90 24 5 1 25123 25 5 1 13124 26 5 1 12412 but i am expecting only one combination of set..... like 1-1,1-2,1-3,1-4.......but if reenter 1-1 thn we have to restrict that.... please help me.... i am in big trouble......Thanx in advance If my qes is not clear for everyone... plz tell me.... i try my lebel best for understand my prob to u.....
Hello, I have a table with fields; T1: Dept, Name, Desc, ModificationDate How can I group by T1.Name, T1.Desc and bring T1.Dept which has the latest T1.ModificationDate Can anyone write me this query?
I have got the three tables above. Would you help me to write a SQL query to show the names and PercentOfQuota of sales people who have an order with all cuatomers. Thank you very much!
I have got the three tables above. Would you help me to write a SQL query to show the names and PercentOfQuota of sales people who have an order with all cuatomers. Thank you very much!
i am assuming there is a better way to write this query (since im not too proficient in SQL)
sql Code:
Original - sql Code
select client_id from clients where client_id not in (select schedule_det.client_id from schedule_det, schedule_mstr where schedule_det.schedule_id=schedule_mstr.schedule_id and schedule_mstr.status_code!='COMPLETE')
SELECT client_id FROM clients WHERE client_id NOT IN (SELECT schedule_det.client_id FROM schedule_det, schedule_mstr WHERE schedule_det.schedule_id=schedule_mstr.schedule_id AND schedule_mstr.status_code!='COMPLETE')
these table have some common feild names. table1,2 and 3 all have a,b,c and d as field names. each table has other field names too but the ones they all have in common are a,b,c and d.
so i would like to write a query that returns all rows from all 3 tables where column d is greater than 5 and less than 10.
so basically i want it to treat the records from all 3 tables ad one big dataset.
how would i write a query to do this.
i know i could say: SELECT a,b,c,d FROM table1,table2,table3
but what gets me is the WHERE clause
do i have to say WHERE table1.d >5 AND table1.d <10 OR table2.d>5 AND table2.d <10 OR table3.d>5 AND table3.d <10
Insert into Ename Select 'a' Union all Select 'a' Union all Select 'a' Union all Select 'b' Union all Select 'c' Union all Select 'b' Union all Select 'd' Union all Select 'g' Union all Select 'g'.
Hi i want to audit a tables For That i created audit tables In that table i want to store data as all field related to old data table and from which system user had changed the data
For this system id i used host_id() but the iam not getting the id
I have a table with many records in it. There is one field called "Nature". How would I select the value that appears the most often in the "Nature" field? The nature field contains text.
For example, this code selects those with more than 10 records...
I just want the top record.
SELECT count(*) FROM WEBASGN_FULL GROUP BY NATURE HAVING count(*) > 10
Basically select nature from webasgn_full that occurs the most often in the table....
I need help on writing a query which will return my results as I need them.
I have three tbls.
TimeType, TimePlan and TimeSpent
TimeType simply lists the Individual Jobs (types) a user can be doing.
The TimePlan and Time Spent each list individual records with start and end dates and what TimeType was being performed. Each TimeType can have multiple starting and ending dates for the TimePlan and Timespent.
I need my results to show individual timetypes, with the starting and end dates for the Timeplans and also the TimeSpent
So there will be multiple Types, and for each one I need to show the start and end dates.
I would normally write this as three seperate queries.
The first would get the TimeTypes, The second the TimePlan starting and ending dates for that type, The third , the Spent starting and ending dates for that type
However that doesn't come across as very efficient so I wonder if there was any other alternative
Bellow is my requirement I have employee table which stored employee information And I have reference table which holds information for that employee reference
I need to count from the reference table like how many reference made for male Employee and female depending on their age range
I have a selection list: Gender: Male Female All
When user select : Male from the list
Male Female Age range: 20-29 Age range: 30-45 120 0 100 20
When user select : Female from the list
Male Female Age range: 20-29 Age range: 30-45 0 25 15 10
When user select : All from the list
Male Female Age range: 20-29 Age range: 30-45 120 25 115 30
Please help me to sort out this problem regards Sujithcf
I have a Table "T1" with col "id","name". Table "T2" with "TID","ID" etc..d [Here, id of T1 is mapped to id of T2]
Now, I wanted to write a query such that the query should output T1.id,T1.name and a third column which will show me the count of records in T2 corresponding to an ID in T1.
I run into this question again. Here is it. I have two tables like this: StoreVisit(StoreVisitID(PK), UserID, StoreID, LoginID, LogoutID) ProductOrders(ProductID, UserID, StoreID, OrderQuantity, StoreVisitID) What I want to do is, I need to find out the lastest store visit record and put the StoreVisitID into ProductOrders table for the same userID, StoreID. So what I tried is:
update ProductOrders set StoreVisitID=sv.StoreVisitID from ProductOrders po inner join (select top 1 * from StoreVisit where UserID=po.UserID and StoreID=po.StoreID order by StoreVisitID desc) sv on po.StoreID=sv.StoreID and po.UserID=sv.UserID Where po.UserID=@UID and po.StoreVisitID is null
When I run it, it shows the error: The column prefix po, does not match with a table name or alias name used in the query. Looks like it does not recognize the po in select top 1... .
I hope you guys already understand what I am trying to do. I am wondering what is the best way to do this? Thanks.
I have query that return statuses and their totals, the data looks like: StatusCode Total---------- ---------A 100PS 50SI1 9SI2 8etc... The query returns over 30 totals, but I only want the top 9 totals plus others (the rest of the totals combined) . How do I go about this query? StatusCode Total---------- ---------A 100PS 50SI1 19SI2 18SI3 9SI4 8PS1 6PS2 6PS3 6Others 99 Also, have it work for queries that have 9 or less totals?
i am working on asp.net i am passing two values through querystring to the next page, that values are comming to next page but i want to display results on these two crieria hats. i have written following query which working fine in sql server 2000 SQL 2000==> select caterername, ccity from caterer where ccity='matunga' and cateringfor like '%event%' but when i am trying to do this in asp.net its not working. following code accepting only one value . i want to compair two value. Please guide me how to write above query in asp.net using 'and' and 'like' operatorasp.net ==>select * from addahall where HCity='" + city + "' ==> city is variable please let me know as early as possible i have very less time.
I'll start by giving the basic idea of the data structure I'm looking at in the database (pardon my MS Paint skills!):
And this all starts with a root plan. So (taking projects out of the picture for the minute), it could be a structure going any number of levels down like so: plan -> goal -> strategy -> plan -> goal -> strategy -> plan -> goal -> strategy, etc.And not forgetting, that a strategy can also have any number of projects, and all these relationships are one to many, so it ends up being like a large tree structure. What I am trying to accomplish is user security related, in that I need to enforce rules set to say whether a single user is allowed to view a project. You can define the rules at any level in the structure above. So if for instance, a user is given a rule that they can access anything from a goal, they can see any projects that exist in any strategies underneath that goal in the tree structure. I hope this is making sense so far... The rules can also just be defined at a specific project level. These rules (at the moment, I'm more than happy to change if this is a silly way of doing it) are set in a table that has the columns:user_id intplan_id intgoal_id intstrategy_id intproject_id intallow bitEvery row will have a user_id value, and one of either a plan_id, goal_id, strategy_id or project id (which I thought would be a simple way to pull out what kind of rule if needed), and the allow column just defines if it's an allow or deny rule (a specific deny rule on any object lower in the tree structure will override any cascaded allow rule). The way my application needs to grab this information, is to simply have some way of returning a list project_id values for which the current user is allowed to access. I'm basically stumped. The closest I have gotten (which still didn't quite work) was going to end up using quite a large number of cursors... Which is bad. It can be inefficient to an extent, as once these values are gathered once, I can store them in a Session variable, so it's not grabbing them every time the user tries to load a project. Thanks for any help!
In one SQL server table table1 it contains following data ID Date CA01-001 01/01/2000 CA01-002 02/01/2000 CA01-003 01/15/2000 CA01-004 02/11/2000 CB02-001 01/01/2001 CB02-002 02/01/2001
ID is grouped by first four characters CA01 or CB02, then each record increased by 1 to be 001, 002 .. untill 999 in sequence
How do I write SQL query just to get each group largerest record such as for our case: CA01-004 02/11/2000 CB02-002 02/01/2001
I can't figure out how to replace a single digit entry in my database with a new digit. i want to replace in the alarms.severity all values of 4 with a new value of 2. For some reason the script I wirte won't get past the digit 4 in the statement. Anyone have a script they can let me try?
I am wondering if there is some type of recursive query to return the values I want from the following database.
Here is the setup:
The client builds reptile cages.
Each cage consists of aluminum framing, connectors to connect the aluminum frame, and panels to enclose the cages. In the example below, we are not leaving panels out to simplify things. We are also not concerned with the dimensions of the cage.
The PRODUCT table contains all parts in inventory. A finished cage is also considered a PRODUCT. The PRODUCT table is recursively joined to itself through the ASSEMBLY table.
PRODUCTS that consist of a number of PRODUCTS are called an ASSEMBLY. The ASSEMBLY table tracks what PRODUCTS are required for the ASSEMBLY.
Sample database can be downloaded from http://www.handlerassociates.com/cage_configurator.mdb