June 9, 2011 at 6:09 am
suppose i have three tables called
Department
-------------
DeptID DeptName
------ ---------
1 Accounts
2 Sales
3 Purchase
Location
----------
LocID LocName
------ --------
1 China
2 UK
3 USA
4 Germany
Employee
----------
EmpID Name Salary LocID DeptID
----- ----- ------ ----- -------
1 jhon 15000 1 2
2 Sina 12500 3 1
3 Keith 17420 2 3
EMPID is PK of employee table and also employee table joined with location and department by locid and deptid.
in simple sql we can join very easily and show the data like
empid name salary locname deptname.
but in my case i do not want to hard code the join by tablename.columnname.
rather i want query the system tabele and get the relationship and build the join dynamically. i am not very good in sql. so please someone help with sample as a result i can construct the join dynamically.............please help.
June 9, 2011 at 6:32 am
Why don't you want to specify the joins properly. Are you saying that there are foreign keys and you want to read the system tables to find out what the foreign keys are and then use them in the join? Sounds like homework to me.
For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
June 9, 2011 at 6:57 am
basically i want form the join statement dynamically and construct join querying the relation from system table.......i am in a situation where join can't be hard coded...please help me.
June 9, 2011 at 7:15 am
It would be useful to know why though, as it's an unusual request. Instead you have avoided the question and simply restated your original post.
June 9, 2011 at 7:33 am
I still don't understand why. What have you tried so far? Here is a script that generates the table and data as well as a select statement against the system tables to show the columns associated with the foreign keys.
CREATE TABLE [dbo].[Departments](
[DeptID] [tinyint] NOT NULL,
[DeptName] [varchar](20) NULL,
CONSTRAINT [PK_Department] PRIMARY KEY CLUSTERED
(
[DeptID] ASC
)
)
GO
CREATE TABLE [dbo].[Location](
[LocID] [tinyint] NOT NULL,
[LocName] [varchar](20) NULL,
CONSTRAINT [PK_Location] PRIMARY KEY CLUSTERED
(
[LocID] ASC
))
GO
CREATE TABLE [dbo].[Employee](
[EmpID] [tinyint] NOT NULL,
[Name] [varchar](20) NULL,
[Salary] [int] NULL,
[LocID] [tinyint] NULL,
[DeptID] [tinyint] NULL,
CONSTRAINT [PK_Employee] PRIMARY KEY CLUSTERED
(
[EmpID] ASC
))
GO
/****** Object: ForeignKey [FK_Employee_Department] Script Date: 06/09/2011 09:16:37 ******/
ALTER TABLE [dbo].[Employee] WITH CHECK ADD CONSTRAINT [FK_Employee_Department] FOREIGN KEY([DeptID])
REFERENCES [dbo].[Departments] ([DeptID])
GO
ALTER TABLE [dbo].[Employee] CHECK CONSTRAINT [FK_Employee_Department]
GO
/****** Object: ForeignKey [FK_Employee_Location] Script Date: 06/09/2011 09:16:37 ******/
ALTER TABLE [dbo].[Employee] WITH CHECK ADD CONSTRAINT [FK_Employee_Location] FOREIGN KEY([LocID])
REFERENCES [dbo].[Location] ([LocID])
GO
ALTER TABLE [dbo].[Employee] CHECK CONSTRAINT [FK_Employee_Location]
GO
insert into Departments
select 1,'Accounts' union all
select 2,'Sales' union all
select 3,'Purchase'
insert into Location
select 1, 'China' Union all
select 2, 'UK' union all
select 3, 'USA' union all
select 4, 'Germany'
insert into Employee
select 1, 'John', 15000 ,1, 2 union all
select 2, 'Sina', 12500, 3, 1 union all
select 3, 'Keith', 17420, 2, 3
select o.name ParentTable, c.name ParentColumnName, p.name ReferencedTableName, r.name ReferencedColumnName
from sys.foreign_key_columns a
inner join sys.syscolumns c
on a.parent_object_id = c.id
and a.parent_column_id = c.colid
inner join sys.sysobjects o
on a.parent_object_id = o.id
inner join sys.syscolumns r
on a.referenced_object_id = r.id
and a.referenced_column_id = r.colid
inner join sys.sysobjects p
on a.referenced_object_id = p.id
where o.name in ('Employee','Departments','Location')
For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply