I want to join two tables of information together. I want to bring the order information together with the order charge. But since the OrderCharges table can have multiple values, I just want to retrieve the sum of those charges for a specific OrderID+ItemID.
My data looks something like this:
CREATE TABLE #OrderItems
(
OrderItemsID INT IDENTITY (1,1) PRIMARY KEY,
OrderID INT,
ItemID INT
)
SET IDENTITY_INSERT #OrderItems ON
[Code] ....
So I'm looking to see a result set that would like this this:
I wrote this SQL Query based in several tutorial on the net:
"SELECT c.CustomerName, sh.IncidentDesc, sh.IncidentTechInCharge, sih.IncidentChangeTo, sih.IncidentChangeDate FROM Customer c INNER JOIN SupportHistory sh ON c.CustomerCode = 'sh.IncidentCustomerCode' INNER JOIN SupportIncidentHistory sih ON sh.IncidentID = sih.IncidentID"
Element(Element_Id, Element_Name) and Parent_Child(Parent_Id, Child_Id). Parent_Id and Child Id are actually the element Ids only. An element can have more than 1 parent or more than one child.
Hi Group,My apologies for the lengthy post, but here goes...I have the following tables:TABLE Vehicles([ID] nvarchar(5),[Make] nvarchar(20),[Model] nvarchar(20),)TABLE [Vehicle Status]([ID] int, /* this is an auto-incrementing field*/[Vehicle ID] nvarchar(5), /* foriegn key, references Vehicles.[ID] */[Status] nvarchar(20),[Status Date] datetime)Here's my problem...I have the following data in my [Vehicles] and [Vehicle Status] tables:[ID] [Make] [Model]----------------------H80 Nissan SkylineH86 Toyota Aristo[ID] [Vehicle ID] [Status] [Status Date]----------------------------------------1 H80 OK 2006-10-012 H80 Damage 2006-10-053 H86 OK 2006-10-134 H86 Dent 2006-10-155 H86 Scratched 2006-10-16I need a query that will join the two tables so that the most recentstatus of each vehicle can be determined. I've gotten as far as:SELECT Vehicle.[ID], Make, Model, [Status], [Status Date] FROM[Vehicles] INNER JOIN [Vehicle Status] ON [Vehicles].[ID] = [VehicleStatus].[Vehicle ID]Of course this produces the following results:[ID] [Make] [Model] [Status] [Status Date]--------------------------------------------H80 Nissan Skyline OK 2006-10-01H80 Nissan Skyline Damage 2006-10-05H86 Toyota Aristo OK 2006-10-13H86 Toyota Aristo Dent 2006-10-15H86 Toyota Aristo Scratched 2006-10-16How do I filter these results so that I get only the MOST RECENT vehiclestatus?i.e:[ID] [Make] [Model] [Status] [Status Date]--------------------------------------------H80 Nissan Skyline Damage 2006-10-05H86 Toyota Aristo Scratched 2006-10-16Thanks in advance,Rommel the iCeMAn*** Sent via Developersdex http://www.developersdex.com ***
I have a sql query that I am using to populate a datagrid. The problem is one of the tables is a month table. and the other tables are full of data. So there is no common column name to match using a inner join "on".
In my query, I am showing Student Record and also want to retrieve the last class attended by the student using JOIN to the table (ClassHistory).
Master ID Student_Name 1 Arindam 2 Brenda 3 Callie
(ClassHistory) ID Class Year 1 4 2003 1 5 2004 1 6 2005 2 4 2003 2 5 2004 3 4 2003
DESIRED RESULTSET ID Student_Name Class Record 1 Arindam 6 2 Brenda 5 3 Callie 4
The result is like - Arindam last class was Class 6, while Brenda was in Class 5 and Callie was in class 4.
Please note that the query will b part of a larger query and the Class record to be retrieved from ClassHistory if possible need to be retrieved through JOIN.
Table 2 Email ------- EmailId, RxId(duplicates ok), Subject, Message, To, From, Type
I would like the result to be
Result Table ------------ Rx Id, RxDate, Name, Notes(or Subject), Type
Table 1 can be searched by date, or Name or DOB I want the result of this search to go through table 2 and return to me all emails that match thre previous results RxIds
If I do a search for Rxs that were prescribed from 1/1/2005 to 7/1/2005 I would like the resulting table to be RxId RxDate Name Note/Subject Type 99 1/1/2005 Person1 Note1 1 98 3/1/2005 Person1 Note2 1 98 3/1/2005 Person2 S2 2 98 3/1/2005 Person2 S5 2 98 3/1/2005 Person2 S8 2 95 6/1/2005 Person5 Note5 1 95 6/1/2005 Person2 S4 2 95 6/1/2005 Person2 S6 2
I have tried a combination of inner joins and Union with no luck any suggestions
I need to retrieve the available tables in a database in SQL Server 2000. I found this command and although it works I can't figure out how to filter the results.
exec sp_tables
I don't want to return the whole list, just the tables starting with "OV" and "SV". Does SQL Server 2000 support a wildcard? Like can I have it search for "OV*" or do I have to use LIKE. Did google search with no helpful results. I remember in mySQL this was simply "SHOW Tables" but this command isn't supported in SQL Server 2000.
(SQL 2005)I'm looking to create a stored procedure to first "select name fromsys.databases where name like '%site'" then pass each name to thefollowing using some kind of loop "USE @name select name fromsys.tables where type = 'U'"I tried a while statement, but the master sys.databases recordsetdoesn't change..It's the loop I can't get to work.Any help is greatly appreciated!
I'm having a problem retrieving information from two different tables. Everytime i run a query which i type a source id into a textbox my page keeps timing out. What could be the problem? The tables i'm pulling from is profiles and phone. Here is the code. If someone could tell me what's going on id apperciate it. Thanks!
Dim queryString As String = "SELECT [profiles].[date_added], [profiles].[source_id], [RP_profiles].[fnam"& _"e], [profiles].[lname], [profiles].[title], [phone].[number], [RP_phone"& _"].[source_id] FROM [profiles], [phone] WHERE ([profiles].[source_id] = "& _"@source_id)"
I want retrieve data from 3 table my tables structure is this
tblUsers
U_ID - Name
3 John
tblGroups
G_ID - Name
5 Admins 6 Moderators
Now I want join some of the users to different groups for example John maby is a member of two groups (Admins and Moderators)
in order to do this I created a new table names tblGroupsUsers
tblGroupsUsers
ID - User_ID - Group_ID
1 3 5 1 3 6
its ok, but Now I don't know how to retrive my users list from database I don't know how to write a wuery for this I have tried this :
strSQL = "SELECT tblUsers.name, tblUsers.U_ID, tblGroups.G_ID, tblGroupsUsers.Group_ID, tblGroupsUsers.User_ID FROM tblUsers INNER JOIN tblGroupsUsers ON tblGroupsUsers.User_ID = tblUsers.U_ID, tblGroups WHERE tblGroupsUsers.Group_ID = tblGroups.G_ID ORDER BY tblUsers.name ASC;"
Its working withut error but the problem is the results its like this
John
John
its will retrive the username twice , I think its reading based on tblGroupsUsers table because it has two rows , help please I need this how can I configure my query to get eache name once
We are accessing a database through Linked Servers. That database has a bunch of views.We are able to get a list of columns for our views by querying [syscolumns]. However, how do we find out which of those columns have primary keys?
Many HUGE Thanks to the person who knows how to do this.
Problem: My goal is to take a single data row (myDataRow) from some table (MyTable) and then retrieve every single associated record throughout the database that is related to that datarow. This requires me to first find out what tables are related to the starting table and then recursively pull all of the associated records until I get all of the records associated. Sounds simple right?
The following code only pulls back only those tables that have a child table with an associated foreign key (i.e. a 1 -> many):
Select [Name] from sysobjects where xtype='U' and Id in(
select fkeyid from sysforeignkeys where rkeyid=
(Select Id from sysobjects where Name='<TableName>'))
order by name
But this query does not work backwards where I start with a child table and want to know the parents (i.e. many->1 relationship)
Anybody have a clue how to retrieve these parent related tables when starting with a child table? I know it can be done because SQL Server Management lets you select a table (child OR parent) and retrieve the related table(s).
Hi, i m using vwd 2005 express and sql express. i have created following tables table_office,table_customer,table_order in my sql express. i also added one more table named table_final. In my webform i have 2 textbox and submit button plus(controls to take values for table_office,table_customer,table_order).so when user fills the form and clicks the button the data gets inserted into thier respected tables.similarly table_final gets populated with values in this way (id,name,address,table_office_id,table_customer_id,table_order_id) Now when displaying the output in the gridview. i combined values of all these tables together. its fine till here. Now what i want is i want to write a query to retrieve these values back into the webform . Here i want the values from all the tables back into the webform controls on a click of a button.so that i can modify it manually, make updation on it and finally show it into gridview. i hope i m able to make u understand. anyway can somebody help me with simple code(C#) and sql query to achieve this task.? thanks. jack.
I have two tables that have no relation. However, both have a column which has a field of nvarchar(50) that I want to retrieve together in one operation and bind to a DropDownList in a sorted fashion. So, what I'm trying to achieve is this: 1. SELECT name FROM table1 2. SELECT name FROM table2 3. Join the two results together and order them alphabetically 4. Return the result set I'm not sure how to do this or even if it's possible. Ideally I'm hoping it can be done in a stored proc.
I'm having trouble with a multi-table JOIN statement with more than one JOIN statement.
For each order, I need to return the following: CarsID, CarModelName, MakeID, OrderDate, ProductName, Total ordered the Car Category.
The carid (primary key) and carmodelname belong to the Cars table. The makeid and orderdate belong to the OrderDetails table. The productname and carcategory belong to the Product table.
The number of rows returned should be the same as the number of rows in OrderDetails.
A piece of software I wrote starting timing out on a query that left outer joins a table to a view. Both the table and view have approximately the same number of rows (about 170000).
The table has 2 very similar columns, one is a varchar(1) and another is varchar(100). Neither are included in any index and beyond the size difference, the columns have the same properties. One of the employees here uses the varchar(1) column (called miscsearch) to tag large sets of rows to perform some action on. In this case, he had set 9000 rows miscsearch value to "g". The query then should join the table and view for all rows where miscsearch is set to g in the table. This query takes at least 20 minutes to run (I stopped it at this point).
If I remove the "where" clause and join all rows in the two tables, the query completes in about 20 seconds. If set the varchar(100) column (called descrip) to "g" for the same rows set via miscsearch, the query completes in about 20 seconds.
If I force the join type to a hash join, the query completes using miscsearch in about 30 seconds.
So, this works:
SELECT di.File_No, prevPlacements, balance,'NOT PLACED' as status FROM Info di LEFT OUTER HASH JOIN View_PP pp ON di.ram_file_no = pp.file_no WHERE miscsearch = 'g' ORDER BY balance DESC
and this works:
SELECT di.File_No, prevPlacements, balance,'NOT PLACED' as status FROM Info di LEFT OUTER JOIN View_PP pp ON di.ram_file_no = pp.file_no WHERE descrip = 'g' ORDER BY balance DESC
But this does't:
SELECT di.File_No, prevPlacements, balance,'NOT PLACED' as status FROM Info di LEFT OUTER JOIN View_PP pp ON di.ram_file_no = pp.file_no WHERE miscsearch = 'g' ORDER BY balance DESC
What should I be looking for here to understand why this is happening?
Till recently we were using the following code to retreive schema information of columns of tables
Dim schemaTable = connection.GetOleDbSchemaTable( _ System.Data.OleDb.OleDbSchemaGuid.Columns, _ New Object() {Nothing, Nothing, tableName, Nothing})
Now instead of getting the name of table (which i was using as param for filtering) i'm going to receive a sql-query. Now my question is if I were to get a query like the following :
I would like to use a dynamic query to obtain a value...a recordcount specifically.
I have two options so far. The 2nd option works fine, but seems like it would be slower. The first option doesn't recognize that i have declared my variable (@int). Any ideas how i would use the first method...or if it's worth trying?
Method 1
--# produces error: Must declare the variable '@int'.
DECLARE @int as int EXEC ('SELECT @int = COUNT(ProductID) FROM Products') print @int
Method2
--# works fine...but seems less efficient. EXEC ('SELECT ProductID FROM Products') print @@rowcount
select PaySlipDate,EmpID, case ADCode when 'GPF' then ADAmount else 0 end GPF, case ADCode when 'GPF.ADV' then ADAmount else 0 end 'GPF.ADV' from EmpSalaryRecord
It is showing multiple records of each employee for each date. First a record with GPF and GPF.ADV both zero and then records with values. I want a single record for each date and employee.
I have 2 tables, I will add sample data to them to help me explain...Table1(Fields: A, B)=====1,One2,Two3,ThreeTable2(Fields: A,B)=====2,deux9,neufI want to create a query that will only return data so long as the key(Field A) is on both tables, if not, return nothing. How can I dothis? I am thnking about using a 'JOIN' but not sure how to implementit...i.e: 2 would return data- but 9 would not...any help would be appreciated.
