Hi, how can I search using more than one field? This is a hard question to ask, I will see if I can explain it.
On a web form I have 7 various textboxes and dropdownlists. It is for a search. The user does not have to fill in all of them (he or she may want to use just one search field).
If I have 6 of the 7 fields blank, how can i get the result of just the field being used?
I have this example:
sSQL.Append("and (table.fieldname= isnull(@parameter, table.fieldname) or table.fieldname IS null) ")
but it doesn't seem to be doing the job.
I have just taken over the job of sorting out a rather poorly designed database. It looks like it was 'upsized' from an access database to the SQL server. The SQL server is the 2000 version.
Now I am trying to generate a report of what the students in the database are owing by referencing the Receipt table and then all the available payment methods and allocations. I was wondering if there was anyway to work out data being displayed twice (Let me demonstrate)
Note1: All the tables are linked by a key of ReceiptNo. From what I can see there is a table for every payment type and allocation but no link between the two other then the receipt number.
Using the query: SELECT T_Receipt.ReceiptNo, T_cheque.Amount AS Chq_Amount, T_credit.Amount AS Cre_Amount, StandingOrder.Amount AS Stn_Amount, T_BankTransfer.amount AS Bnk_Amount, T_cash.TotalAmount AS Cas_Amount, T_RentPayment.AmountPayed AS Ren_Paid, T_AdminPayment.AmountPaid AS Adm_Paid, T_InternetBilling.Total AS Int_Paid, T_Utilities.AmountPaid AS Util_Amount, T_InvoicePayment.amountPaid AS Inv_Paid, T_OtherPayments.paymentAmount AS Oth_Paid, T_parkingBill.paymentAmount AS Prk_Paid, T_TelephoneBills.TelephoneCredit AS Tel_Paid, T_DepositPayment.[Deposit payment] AS Dep_Amount, T_Receipt.cancelled AS Canceled, T_Receipt.RemittanceReceiptNo AS Rec_Ref, T_Receipt.Student FROM T_Receipt INNER JOIN T_DepositPayment ON T_Receipt.ReceiptNo = T_DepositPayment.receiptNo LEFT OUTER JOIN T_RentPayment ON T_Receipt.ReceiptNo = T_RentPayment.RentPaymentNo LEFT OUTER JOIN StandingOrder ON T_Receipt.ReceiptNo = StandingOrder.ReceiptNo LEFT OUTER JOIN T_TelephoneBills ON T_Receipt.ReceiptNo = T_TelephoneBills.ReceiptNo LEFT OUTER JOIN T_parkingBill ON T_Receipt.ReceiptNo = T_parkingBill.ReceiptNo LEFT OUTER JOIN T_OtherPayments ON T_Receipt.ReceiptNo = T_OtherPayments.ReceiptNo LEFT OUTER JOIN T_InvoicePayment ON T_Receipt.ReceiptNo = T_InvoicePayment.receiptNo LEFT OUTER JOIN T_cash ON T_Receipt.ReceiptNo = T_cash.ReceiptNo LEFT OUTER JOIN T_AdminPayment ON T_Receipt.ReceiptNo = T_AdminPayment.ReceiptNo LEFT OUTER JOIN T_BankTransfer ON T_Receipt.ReceiptNo = T_BankTransfer.receiptNo LEFT OUTER JOIN T_Utilities ON T_Receipt.ReceiptNo = T_Utilities.receiptNo LEFT OUTER JOIN T_credit ON T_Receipt.ReceiptNo = T_credit.ReceiptNo LEFT OUTER JOIN T_cheque ON T_Receipt.ReceiptNo = T_cheque.ReceiptNo LEFT OUTER JOIN T_InternetBilling ON T_Receipt.ReceiptNo = T_InternetBilling.ReceiptNo GROUP BY T_Receipt.Student, T_Receipt.ReceiptNo, T_cheque.Amount, T_credit.Amount, StandingOrder.Amount, T_BankTransfer.amount, T_cash.TotalAmount, T_AdminPayment.AmountPaid, T_InternetBilling.Total, T_Utilities.AmountPaid, T_InvoicePayment.amountPaid, T_OtherPayments.paymentAmount, T_parkingBill.paymentAmount, T_TelephoneBills.TelephoneCredit, T_Receipt.cancelled, T_Receipt.RemittanceReceiptNo, T_DepositPayment.[Deposit payment], T_RentPayment.AmountPayed, T_Receipt.Student HAVING (T_Receipt.Student LIKE N'06%')
Which gives a result of:
RecNo. 30429 Cheque 250 Deposit 250
30429 679.98 250
This is fine but when I do analysis on this it appears as though the student has paid two deposit payments. I was wondering with out querying each table independently from an application if there was a criteria to specify that I only get one deposit result. So as such say, give me all the payments but I only want one result from the other tables. I though about discrete but that wouldn't work here.
Howdy folks. I have a SELECT question for you. Or maybe a WHERE question. That I am not sure is part of the problem. My application is ASP.NET 2.0, and I’d like to avoid getting into stored procedures right now, if I could.
I am trying to summarize the order status for each customer in the database (SQL Server 2005, by the way). I want to provide two counts: the number of open jobs per customer, and the number of rush jobs per customer. Something like this:
SELECT CustID, MAX(CustName) AS CustName, COUNT (*) AS JobOpen FROM Customers INNER JOIN Jobs ON JobCustID = CustID WHERE (JobDone = 0) GROUP BY CustID
As you can see, it finds from the Jobs table all jobs that are not done (JobDone is a T/F field), joins the Jobs and Customers tables, groups by CustID, and counts the totals in each CustID group. It works great in outputting the first three columns that I am looking for.
But I cannot come up with a simple way to add JobRush (also a T/F field), because it needs a different WHERE clause than the one JobOpen uses. It would need
WHERE (JobRush = 1)
So maybe my question should be: how do I use multiple WHERE clauses, each with its own COUNT?
I did mess around with Common Table Expressions, and managed to build two CTEs (one with JobOpen results and the other with JobRush results) that I joined together. It worked in Studio Manager, but my ASP.NET page didn’t like it. I guess that means I could learn stored procedures, but wow I’d love to just have a nice complete SELECT command for my page.
Thanks for reading all this. Any input is greatly appreciated. Matt
When viewing an estimated query plan for a stored procedure with multiple query statements, two things stand out to me and I wanted to get confirmation if I'm correct.
1. Under <ParameterList><ColumnReference... does the xml attribute "ParameterCompiledValue" represent the value used when the query plan was generated?
2. Does each query statement that makes up the execution plan for the stored procedure have it's own execution plan? And meaning the stored procedure is made up of multiple query plans that could have been generated at a different time to another part of that stored procedure?
I have two tables .. in one (containing user data, lets call it u).The important fields are:u.userName, u.userID (uniqueidentifier) and u.workgroupID (uniqueidentifier)The second table (w) has fieldsw.delegateID (uniqueidentifier), w.workgroupID (uniqueidentifier) The SP takes the delegateID and I want to gather all the people from table u where any of the workgroupID's for that delegate match in w. one delegateID may be tied to multiple workgroupID's. I know I can create a temporary table (@wgs) and do a: INSERT INTO @wgs SELECT workgroupID from w WHERE delegateID = @delegateIDthat creates a result set with all the workgroupID's .. this may be one, none or multipleI then want to get all u.userName, u.userID FROM u WHERE u.workgroupIDThis query works on an individual workgroupID (using another temp table, @users to aggregate the results was my thought, so that's included) INSERT INTO @users SELECT u.userName,u.userID FROM tableU u LEFT JOIN tableW w ON w.workgroupID = u.workgroupID WHERE u.workgroupID = @workGroupIDI'm trying to avoid looping or using a CURSOR for the performance hit (had to kick the development server after one of the cursor attempts yesterday)Essentially what I'm after is: SELECT u.userName,u.userID FROM tableU u LEFT JOIN tableW w ON w.workgroupID = u.workgroupID WHERE u.workgroupID = (SELECT workgroupID from w WHERE delegateID = @delegateID) ... but that syntax does not work and I haven't found another work around yet.TIA!
Hi, I remember seeing a fancy query that checked for multiple fields in a table (I think using a select statement in the where clause but not sure), but can't remember how to do it... here is what I want to do (and maybe there is a much easier way). Thanks!
Table1 id item color 1 shoe red 2 shoe blue 3 coat green 4 coat black
Table2 item color shoe red coat green
I want everything in Table1 where item and color are not a match.
So my results should be: 2 shoe blue 4 coat black
I'm sorry if this is a dumb question... it's been that kind of a day!
Hi,New to .Net and SQL. I have two tables that I have joined together. RentalControl_Main has the rental informationd and an Adjuster ID that links to the ADjuster table and the adjusters name. I am trying to create a report that gives the "Single" adjuster name and the totals for all of their contracts. I have a details report that gives each contract info. for each specific adjusters rentals. However, I want to just list the adjuster once and give all of their totals. In my SQL statement I have all of it written out and just need to knowwhat to do in place of 'Alex Early' that will give me all of the distinct adjusters.Do I need to code this on the page with a do while loop?Appreciate any help.SELECT SUM(dbo.RentalControl_Main.Rate) / COUNT(dbo.RentalControl_Main.Rate) AS AmtAvg, SUM(dbo.RentalControl_Main.DaysBilled) / COUNT(dbo.RentalControl_Main.DaysBilled) AS DayAvg, SUM(dbo.RentalControl_Main.Rate * dbo.RentalControl_Main.DaysBilled) / COUNT(dbo.RentalControl_Main.Rate) AS TotAvgFROM dbo.RentalControl_Main INNER JOIN dbo.RentalControl_Adjuster ON dbo.RentalControl_Main.AdjusterID = dbo.RentalControl_Adjuster.AdjusterIDWHERE (dbo.RentalControl_Adjuster.AdjusterName = 'Alex Early' AND (dbo.RentalControl_Main.DateClose IS NOT NULL) AND (dbo.RentalControl_Main.AgencyID = '2')
I want a query ro select the number of wins and the number of games played for a team. The pertinent columns are all in the same table and I was trying to get the information all in one query. Is something like this possible?SELECT COUNT(*) AS WinsFROM tbl_ScheduleWHERE Winner = 'IND'SELECT COUNT(*) AS GamesPlayedFROM tbl_ScheduleWHERE HomeID = 'IND' OR VisitorID = 'IND'How would I merge the two, or can I even do that?
Hi, I'm a bit stumped as to how to do this.I have a string[] with a list of users, and I want to query my database to select only the users in this array and bind the datasource to a GridView, but I don't know how to write an SQL query to search for multiple results from the same field. E.g. Say I have two results in my string[], fred and bob.How can I select data from the database for just those two users - "SELECT * FROM tblUsers WHERE UserName='bob' AND ??"; IF this is possible, I also need to bind it to a gridview. I tried the following, but it didn't work as I needed it to: for(int a = 0; a < userArray.Length; a++){ conn.Open(); SqlCommand command = new SqlCommand("SELECT * FROM tblUsers WHERE UserName='" + userArray[a] + "'", conn); SqlDataReader reader = command.ExecuteReader(); grid.DataSource = reader; grid.DataBind(); conn.Close()} That 'worked', but as I'm sure you can see, the data that was bound to the gridview was only the last result found, not the whole result set. Any help is greatly appreciated.
I have a table that is linked to other tables in one to many relationship.I have a query using LEFT OUTER JOINs to join the tables together.There are multiple counts in the query and count numbers are messed up.(if there are 4 records from one table and 3 from the other - it shows 12 as the count) Your help is appreciated.
Hi, I have a problem which I thought it has a simple solution but now I'm not even sure it is possible.
I have 3 tables Clients <-oo ClientContacts oo-> Contacts (the <-oo means one to may relation between the tables)
A Client may have related none, one or many Contact records. The table ClientContacts is the link that stores that information. The field ClientContacts.Category represents the type of the contact and it will be used in queries. It may be owner, accountant, employee, etc.
The result should return values for Contacts.FirstName, Contacts.LastName, Contacts.[E-mailAddress] if the Client has attached Contact records filtered by category, and '','','' or <NULL>,<NULL>,<NULL> if the Client does not have any Contact records.
I tryed an INNER JOIN but it will return juts the records having contact information.
I need to build a table to combine data into a single table but I need it to include a count on more than 1 column. For example, I have a table containing a store number, an isbn, an on hand quantity, and an on order quantity. It's probably easier to explain with an example. I have a source table containing this:
I am new to Analysis services. and I have figure out the following things:
I have a cube with multiple dimensions. I want to display all the options in cube along with the count of the rows for that combination. I am unable to understand how to count the dimensions and apply to column.
For example, I have say dim1, dim2, dim3 dim4, name as dimensions and cnt as count of the rows. Now I want to write an mdx expression that returns me dim1, dim2, dim3, dim4 with count(name fields) for above combination so the output will be as follows:
name field1 name field2 dim1 dim2 dim3 dim4 200 100
how do I do it?
Also how do we accept input parameters for mdx expression. for example in the above thing, if I have to accept an input for dim1 and display the output values for dim1 how do I do it?
Can anyone tell me if SQL Server 6.5 has anything similiar to linked tables in MS Access? I need to query two tables simultaneously that reside in separate SQL databases. Is this possible, or must the tables reside in the same database?
For example, I have two databases db1 and db2. Table1 is in db1 and Table2 is in db2. I want to query something like this:
"SELECT * FROM table1 WHERE fieldname1 in (SELECT fieldname2 FROM db2.Table2 WHERE...)
I've run into an interesting challenge, and haven't figured out how to solve it yet. I'm fairly novice at MS SQL, and I would assume that there is a relatively simple/elegant solution. Here's what's going on:
I'm tracking projects that have a one to many relationship with sub-project type. There can be many entries for any given project and sub-project type. Each entry has a status.
EX: "Datalist"
ID Proj Sub-Proj Status 1 Alpha Review 1 2 Alpha Test 3 3 Alpha Review 2 4 Alpha Review 2 5 Alpha Test 4
In addition to the Datalist above, I've got a table which contains all valid combinations of Projects and Sub-Projects.
EX: "ValidCombos"
ID Proj Sub-Proj 1 Alpha Review 2 Alpha Test 3 Beta Review 4 Beta Rewrite 5 Beta Test
I need to make a query/table which contains the total number of IDs that have a given Project, Sub-Project, and Status.
EX using data from the tables above: "DesiredTable"
My end game is to automate some of my monthly queries in a Job in SQL Server Agent. Right now I have two metric tables. One table is the name and comment with the PK. The secondary table is attributes/detail, such as reporting month, target and actuals.
I am currently running all different types of queries to get the aggregates. I'd like to get these into a job so it would run automatically and update the reporting table.
Would you recommend making one step or multiple steps for each query? I am trying to use an intelligent approach to begin to load the tables.
I am trying to write a query which includes the following two tables.
Incidents Column 1 = SourceContactNo
Contacts Column 1 = ContactNo Column 2 = Ref
The first columns in Incidents and Contacts both contain the same type of information (contact numbers, only the column names are different, some contact numbers appear in table 2 (contacts) which do not appear in incidents).What I really want to do is return the Contact Number & Ref from the table named contacts where the contact number is the same as SourceContactNo (in the first table incidents).Here is what I have so far:
SELECT Incidents.SourceContactNo, Contacts.ContactNo, Contacts.REF FROM Contacts, Incidents
Assume that I got 10 line of insert query to be carry out, how can I run all these 10 insert query at on time instead of using sqlcommand.executenonquery for 10 time. Thanks.
I've a table with a field named 'TypeOfProd' that has an ID for the various types of products: ex.: 1 - product A 2 - product B 3- product C 4 - product D .... 10 - product J and so on
I need to create a stored procedure that querys only the product types selected by the user.
The user can select 1, 3, 5, 10 or 1, 3 or 3 or 0 for all or some other combination.
For the first user selection a have something like this SELECT Prod FROM tableProd WHERE TypeOfProd = 1 OR TypeOfProd = 3 or TypeOfProd = 5 OR TypeOfProd = 10 For the second, SELECT Prod FROM tableProd WHERE TypeOfProd = 1 OR TypeOfProd = 3
Is it possible to have a stored procedures that runs a query with this random scenario?
FROM [MediaFileData] INNER JOIN [AudioPCData] ON MediaFileData.FileIdentifier = AudioPCData.FileIdentifier INNER JOIN [MetaData] ON MediaFileData.FileIdentifier = MetaData.FileIdentifier INNER JOIN [SSRData] ON MediaFileData.FileIdentifier = SSRData.FileIdentifier
WHERE ([MediaFileData].[SubmittingUser] = '{0}') AND ([AudioPCData].[RecorderID] = '{0}') AND ([MediaFileData].[MediaDescription] = '{0}') AND ([SSRData].[ModelUsed] = '{0}')
Each table has only got one 'FileIdentifier' apart from MetaData. This table has three columns 'FileIdentifer', 'FiledName' and 'FieldText'. One file can have more than one field and therefore It will have the same 'FileIdentifier' e.g.
FileIdentifier FieldName FieldText
1 Field 1 Hello
1 Field 2 Goodbye
The first problem is I only want to display the first and second field 'FiledText' in my results grid but still load all the other fields into my dataset.
The second problem is that it creates a new row for every field, whereas I want the fields with the same 'FileIdentifier' to be in the same row!
At the moment mt results gridlooks like this:
FileIdentifier RecorderID Channel SubmittingUser DateTimeAsString Field 1 Field 2
1 MyPC 1 Me 03/05/07 14:24 Hello Hello
1 MyPC 1 Me 03/05/07 14:24 GoodBye Goodbye
I need it to look like this:
FileIdentifier RecorderID Channel SubmittingUser DateTimeAsString Field 1 Field 2
Hello, I have a table called "ShoppingCart" which has fields for CartID, UniqueID and Quantity. At the moment I haven't specifed a primary key for this table becuase none of the fields are unique. I need to be able to link this ShoppingCart table to a table called "Size" using the UniqueID (both tables contain a field for UniqueID) in order to obtain the ProductID and CategoryID of the data item. I then need to link the ProductID and CategoryID I obtained from the first part of the SQL statement to a table called "plants" in order to get information from the following fields in the plants table: Common_Name, Latin_Name and Thumb_URL. So far I've been able to use an INNER JOIN with the Size table to obtain the ProductID and Category ID using.... SELECT * FROM ShoppingCart INNER JOIN Size ON ShoppingCart.UniqueID = Size.UniqueID WHERE CartID = @CartID ORDER BY ProductID I now need to be able to use the ProductID and Category ID I've obtained from this part of the query to reference the Common Name, Latin Name etc of the data item from the plants table. Is there any way that I could do this? I do sometimes wonder if I'm making this whole thing a lot more complicated than it needs to be. At the moment I'm storing details about the different types of plants the garden centre sells in the "plants" table. However, some plants are available in different sizes so I've got a table called "Size" which links to the plants table using the ProductID and CategoryID of data items. The Size table has a unique primary key field called "UniqueID" which is used to uniquely identify every plant that the garden centre sells (this cannot be done using Product/CategoryID becuase different sizes of the same plant have the same ProductID/CategoryID). I'm then storing just the CartID, UniqueID and Quantity in the "ShoppingCart" table. There must be an easier way of structuring the whole thing! I'd really appreciate any help you can offer me. Many thanks, Luke
Hi there, I tried to make it different as usual but i´m stacked into this problem:Supose TABLE DetailsID_DET ID_CAR DETAILS1 3 1,2,3,4,5,62 4 2,43 5 5,6,7,8andTABLE Details_ItemsID_DI DETAIL_NAME1 Stereo HiFi CD2 Alarm3 AirConditioning4 LeatherSeats5 Pro Wires6 Aluminium WheelsThe problem appears when i need to bring CAR DETAILS (NAME) from TABLE DETAIL_ITEMS.Mi guess i that I should make something like:SELECT * FROM DETAILS_NAME WHERE id_di = (( Array(i) FROM Details )) one by one...I really dont know how to face it.First I thought in bringing ALL details_Items (datafieldtext = id_di and datavaluetext=Details_names) into a dataview.And then "somohow?" filter this dataview according with the Array previuosly splited by me with a For each function.Then I thought "Perhaps" there is a simpliest way to do that using SQL views, o advanced SQL QUERYS.and Finally I thought that creating a VIEW in for both TABLES would be great.The point is that, neither 1,2,3 options, honestly , I dont know how to face them.Thanks in advance, apologise my "rude" English grammar.LUCAS ( From Argentina )
I have some data that I'd like to order by a certain attribute.. but if there is a tie, then it should order by a secondary attribute.. and if there's still a tie.. then a 3rd attribute.
Currently the query looks like this:
SELECT * FROM Players WHERE ORDER BY Points
But I want it to look something like this (I know this doesn't work.. but it's just to give an idea):
SELECT * FROM Players WHERE ORDER BY (Points desc AND Games asc AND Goals desc)
Does anyone know the proper syntax for a multiple ORDER BY query like I described above?
Hello Group I am having a little trouble with a search. I have 12 checkbox controls and 12 textbox controls. I want to be able to choose which textbox to query from. Most of the time the search may be from one or a couple of textboxes. My problem is I can get the SQL String right. Below is what I have for a string. I know I could write an SQL string for every possible combination and put in all 144 in a SELECT CASE, but I would think there is an easier way to do this. Is there anything that would let me build a SQL string depending what checkbox are checked true?
Dim SQL_SL As String = "SELECT * FROM BuildZone" & _ " WHERE Parcel = '" & strPercelS & "'" & _ " ORDER BY Parcel Asc" & _ " SQL_SL As String = SELECT * FROM BuildZone" & _ " WHERE fldNameLast = '" & strNameLastS & "'" & _ " UNION" & _ " SQL_SL As String = SELECT * FROM BuildZone" & _ " WHERE fldDateMonth = " & intDateYearS & "" & _ " AND fldDateYear = " & intDateYearS & "" & _ " UNION" & _ " SQL_SL As String = SELECT * FROM BuildZone" & _ " WHERE fldNameLast = '" & strNameLastS & "'" & _ " UNION" & _ " SQL_SL As String = SELECT * FROM BuildZone" & _ " WHERE fldPermitNum = " & intPermitS & "" & _ " UNION" & _ " SQL_SL As String = SELECT * FROM BuildZone" & _ " WHERE fldStructureType = " & intStructureTypeS & "" & _ " UNION" & _ " SQL_SL As String = SELECT * FROM BuildZone" & _ " WHERE fldConstructionCost >= " & intCCost1S & "" & _ " AND fldConstructionCost <= " & intCCost2S & "" & _ " UNION" & _ " SQL_SL As String = SELECT * FROM BuildZone" & _ " WHERE fldRange = " & intRangeS & "" & _ " AND fldTownship = " & intTownshipS & "" & _ " AND fldSection = " & intSectionS & "" & _ " UNION" & _ " SQL_SL As String = SELECT * FROM BuildZone" & _ " WHERE fldZoningDistricts = " & intZoneS & "" & _ " UNION" & _ " SQL_SL As String = SELECT * FROM BuildZone" & _ " WHERE fldTwsp = " & intTwspS & "" & _ " UNION" & _ " SQL_SL As String = SELECT * FROM BuildZone" & _ " WHERE fldRWD = " & intRWDS & "" & _ " UNION" & _ " SQL_SL As String = SELECT * FROM BuildZone" & _ " WHERE fldWW = " & intWWS & "" & _ " UNION" & _ " SQL_SL As String = SELECT * FROM BuildZone" & _ " WHERE fldAccessApplic = " & intAccessAppS & ""
