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).
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
I need help using the TSQL Command.... I want to display car reviews by authors.... a car may be reviewed by multiple authors.... but the latest entry should be displayed... the older enteries will not be displayed....the following is the query </P><FONT color=#0000ff size=2> <P><FONT color=#000000>SELECT CarReviews.Date AS Date, CarReviews.Company AS Company, CarReviews.Name AS Reviewer, CarMake.MakeName, CarModel.ModelName, CarModel.YearName, CarMake.KeyName + '/' + CarModel.Image AS Image, CarReviews.Id AS IdFROM CarReviews INNER JOIN CarMake ON CarReviews.MakeId = CarMake.Id INNER JOIN CarModel ON CarReviews.ModelId = CarModel.IdGROUP BY CarMake.MakeName, CarModel.ModelName, CarModel.YearName, CarMake.KeyName + '/' + CarModel.Image, CarReviews.Id, CarReviews.Name, CarReviews.Company, CarReviews.DateORDER BY CarReviews.Date DESC</FONT></FONT></P><FONT color=#0000ff size=2> <P></P></FONT> <P>Results of above statement.... Date Company Reviewer MakeName ModelName YearName Image Id ----------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------- ----------------------------------------------------------- ----------- 10/3/2005 5:12:25 PM LATimes.com Dan Neil MINI Cooper S 2005 RAJ-0122/RAJ-0122-82.jpg 8 10/3/2005 5:11:41 PM Star-Telegram.com G. Chambers Williams III MINI Cooper S 2005 RAJ-0122/RAJ-0122-82.jpg 7 10/3/2005 4:51:31 PM MotoFlare.com Joe Wiesenfelder MINI Cooper S 2005 RAJ-0122/RAJ-0122-82.jpg 6 10/3/2005 4:49:22 PM MotoFlare.com Jim Flammang MINI Cooper S 2005 RAJ-0122/RAJ-0122-82.jpg 5 10/1/2005 4:19:25 PM MotoFlare.com Erphan Rajput Audi A4 (2005.5) 2005 RAJ-0089/RAJ-0089-80.jpg 3 10/1/2005 4:19:25 PM MotoFlare.com Jim Flammang Audi A4 (2005.5) 2005 RAJ-0089/RAJ-0089-80.jpg 4 Problem is repeation... In the above statement .... the statement should return only 2 records... but it is displaying 6 records....record one should be of MINI Cooper S 2005 & second should be of Audi A4 (2005.5) 2005Urgent reply will be highy appreciated...Thanks,Erphan Rajput..
Hi guys, Working on a MS Access database, I have a table named "myTable" which contains several fields. I just want to retrieve all the fields (columns) in the myTable, without retrieving Col1 and Col2 What should my SQL string be?
Hi guys, When I thought everything is okay with this script, I got a new problem... I have a VBA's script from Excel 2003 that builds sql script and retrieves data from SQL SERVER 2000. in order to make the sql running, I need to use a multi - batch processing, to pass and execute every command line once a time.
Up to here, I am using a test case with Account number = '123456' and getting the desire results. The code below is running okay with the test case, but when changing the account number (mark as yellow in the code) to include all the accounts (or just one other account), I am getting the following ERROR: run - time error '-2147217871 (80040e31)' - [Microsoft] [ODBC SQL Server Driver] time out expired.
Now, if I take the same code, with the condition that generates the ERROR, and try it into SQL Server, I get the results without errors. Thanks in advance, Aldo.
Below the code:
Code Snippet Function QuerySalesAging() '-------------------------------------------------------------- 'MUST !!! References: Microsoft ActiveX Data Object 2.1 Library '--------------------------------------------------------------
Dim ConnString As New ADODB.Connection Dim RecordSet As New ADODB.RecordSet
'Report Criterias Criteria05 = " AND " & "Accounts.ACCOUNTKEY Between " & AccountKeyAsRange ' -- ==> With AccountKeyAsRange = '123456' AND '123456' it works okay. ' -- ==> With any other value, in example AccountKeyAsRange = '123456' AND '9999999999' it get's ERROR.
CmdLine01 = " USE " & CompanyName
' Check and drop temporary table TemporaryTableName = "CTE" ' The table is a regular one CmdLine02 = " if object_id('" & TemporaryTableName & "') is not null exec('DROP TABLE " & TemporaryTableName & "') "
CmdLine03 = " SELECT ..." CmdLine03 = CmdLine03 & " INTO " & TemporaryTableName CmdLine03 = CmdLine03 & " FROM ..." CmdLine03 = CmdLine03 & " WHERE " & "(" & Replace(Criteria05, "AND", "") & ")" CmdLine03 = CmdLine03 & " ORDER BY ..."
On the web form I have textboxes for the Purchase No., date and part description, and a drop down list for the machine name. How do I insert them into the different tables?
I've just start learning ASP.NET and I am using Web Matrix for this. The examples I've seen so far only shows how to insert into a single table.
hi this is probably a dumb question with an easy answer but does anyone know how to do this...
2 tables, one is a supplier list (id, supplier_name) and the other is an order list (id, order_name, supplier_id... etc)
basically i need to join the 2 tables together (id=supplier_id) but i need to display the results without the supplier_id and the supplier table id, only the supplier name?
do i have select only the specific fields I want? or can I exclude the supplier_id and the id from the supplier table?
Hi all. how could i join unrelated tables? for example: table1: employee dcno name address telno 1 sarah philippines 111 2 john india 222 3 joy usa 333
table2: hospital hospname location owner hosp1 loc1 billgates hosp2 loc2 ann hosp3 loc3 love hosp4 loc4 vic
I have 2 tables with this design: one has ArticleID as primary key and multiple other fields and one has GroupID as primary key and multiple other fields. Each article can belong in multiple groups so I created a new table called articleGroups with only 2 fields: ArticleID and GroupID to show the groups associated with each article. There is a relation between this table and each of the main 2 tables. My question is, in the articleGroup table, does it make any sense to create an Index on ArticleID, GroupID or both? Since the group is needed for each article the Groups will always be queried everytime the article is queried. So, I am not sure if an index is needed?
Please give your ideas about how to create a query from two tables, which have many-to-many relationship. For example, we have table Book and table Author and consider, that one author had wroten several books and one book had being wroten by several authors. So we have many-to-many relationship of these two tables. Usually this sutuation resolves by maens of creating the third table AuthorBook, which consist of key fields from the Author table and key fields from the Book table. So that the Author table is the parent table to AuthorBook, and the Book table is the parent table to AuthorBook. So we have two relations one-to-many. Now the question is how to get all related values from both tables, using JOIN operation and the conjunctive table?
I have a relationship database that contains two related tables. Table1 has the fields Customer_Name, Customer_ID, and products_Purchased. Table2 has the fields Customer_Name and Cutomer_ID. I would like to add a new row to Table1for a new purchase by a cutomer. When I try to add a new value in Table1 I get an error message saying I can't add a value to table1 because it requires a corresponding value in Table2. I am using the following code: '********************************************** ("INSERT INTO [Table1]([products_Purchased], [Customer_ID]) VALUES('" & textBox..Text & "'," & session("ID") & ")" ' ***************************************** How do I write an INSERT STATEMENT that adds vaules to both tables, so that the code works? Any HELP would be appreciated. charlie
I have 1 Excel spreadsheet. It has about 50 fields. I need to import this into my Database.The problem is; there are 2 tables that I need to import this into. These 2 tables are related with [ID].So, I need to split the data from 1 spreadsheet between 2 SQL server table. and keep the records related.
I am trying to delete the records in 2 related tables. The 'child' table has a field called [SETA],
I want to delete all the records in this table that contain the same info, as well all the fields in the parent table that is related to this table. They share the [ID] field as key. This is my code:
Code: DELETE FROM Student a full outer JOIN Qualification b on a.[ID] =b.[ID] WHERE b.[SETA] = @SETA
Hi, I have 3 tables: Employees with the fields:idEmployee and employeeName Roles with the fields:idRole and roleName. An employee can have one or many roles. I created the table EmployeeRoles with the fields: id,idEmployee,idRole. idEmployee and idRole are foreign keys. I want to insert a new employee into Employees table, but I have to insert idEmployee field into EmployeeRoles table.
Hi I have a question how to load data to tables linked by Foreign Keys in MSDE/SQL server. Example: If I have 2 tables linked (by Foreign Key): One table:
ITEM idITEM NAMEITEM CATEGORY (FK) 1cheese 2
And another:
Category IDCATEGORY NAME 1 household 2 food 3 general
How do I enter the load of data Do I have to enter it as 1cheese2 or is there some way of entering it as 1cheesefood
TDS wizard does not allow me to transfer to views/querries what I thought would be a normal way as I would enter data to view(relevant to Access's form) and it would update related tables . When I wrote sql to do it it said I can not update my view table as too many tables would be affected(I had lookup tables empty then though) I am doing it by number using TDS wizard to transfer it directly to the main table but there must be a better way
I have a problem there must be an answer to, but I cannot find it anywhere on Google.I have a SQL INSERT statement in an ASP.Net page that inserts into 3 tables in one transaction.The problem is that 2 of the 3 tables are children of the main table, and I need to get the Parent table's Primary Key [which of course has not been inserted yet] to insert into the Child tables.How do I do this?THanks.Doug
I have a situation where I want to load some entities from one table lets say the table is customers and i would like to load all the customers with first name = dummy, not only this i would like to load all the orders and order details for these specific customers (these are two different separate tables) . I want all this within one stored procedure that return me three results for three different tables. Please tell me whether it is possible and how.
Hey guys up until now i've only inserted data into a single table. Now I have a form that collects information over a span of three forms. Each form has a table related to it and these three tables are related to each other.
What I want to know is: 1)How do you go abouts inserting data into multiple related tables that have constraints on them?
2)Would you use a stored procedure in an instance like this?
3)At what stage would you execute the sql queries. I assume you do this once you have collected all the required information as opposed to: Enter info into form1, submit form1 data to database... enter info into form2, submit form2 data into database etc
Any help would be greatly appreciated!
Say for instance I have three related tables.
table1 ------ tbl1_id tbl1_data1 tbl1_data2
table2 ------ tbl2_id tbl2_data1 tbl2_data2
table3 ------ tbl3_id tbl3_data1 tbl3_data2
table1 has a one-to-many relationship with table2 table3 has a one-to-one relationship with table2
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 what should be simple question about related tables in a dataset and using those fields in a report. Here is my simple design.
Order Header -> Line Item -> Line Discount -> Discount Table
Now when designing this report I cannot seem to access the related discount records for the line item. (as more than one can be applied) Every time I try to drag an field from one of the tables in the dataset to my report it automatically adds "First()" onto the field value expression. This seems to work fine for the order header, but doen't even come close to getting the right discount on the line item? I basically need to pass through the Line Discount table to get to the discount name field to put on the same line as my line item data on the report.
I have looked and googled for for an example on this, but have come up short. Can anyone point me in the right direction?
Does anyone know how I can do this? I have 2 tables in an ODBC datasource (INV HEADER) and (INV DETAILS). The relationship on these 2 tables is (INVNUM). I want to import these tables into SQL 2005 on a nightly basis by date. The problem is the date field is on the (INV HEADER) table and not the (INV DETAILS) table. Basically I want to import all the (INV DETAILS) rows that have the same (INVNUM) as the (INVHEADER) but don't know how to do this. I could use a join on the source tables but how would I direct specific columns to 2 different destinations? Any help on this would be great and appreciated. Thanks
Hi everyone! I'm on my way to learn SSIS by myself and it's a little complicated! I'd like to ask you one thing:
I have two tables at my data source, one is "Clients" and the other one is ClientsAddress. That is, a client can have more than one address. Both tables are related by a one to many relationship and the tables description is:
CLIENTS CLIENTSADDRESS #PK_Client #PK_Client other fields.. #ID_address ... other fields
What i intend to do is to obtain one table with approximately 3 fields, each one for a possible client address; something like this: CLIENTS PK_Client ID_Address1 ID_Address2 ID_Address3
My question is what transformation can i use? an how ?
Thanks very much in advance!! Emilio Leyes Salta, Argentina
hi there, i am new to sql server database.i am doing small projects right now using asp.net and sql to create webpages (very basic webpages) My problem is:
Problem :
i have two tables .....table 1 and table 2.
Table 1 has following fields: studentid,student name,student address.
Table 2 has following fields:studentid and course . table1 student id is the primary key refrencing table 2 student id. Now i delete a record in table 1 which will in turn also get deleted in table 2 . so for eg if i have three records 1 ,2 and 3 ....then i delete 2 in table 1 ...i will have 1 and 3 in both table 1 and table 2....now i want 3 to become 2 in both table 1 and table 2...so that i dont have empty space between two student id's 1 and 3. so this is my problem....if any one can help me out with suggestions please do.
Terminology question:Is there a term for a set of records related directly or indirectly by keyvalue in several tables? For example, a single invoice record and its lineitem records -or- a single customer, the customer's orders, the order linesfor those orders, the customer's invoices, and the invoice lines for thoseinvoices.I'm thinking the term might be graph, but I'm not at all certain of this.Thanks,Steve J
