create table #mgr_tab(sno int,sname varchar(15), sid int,stype char(1))
create table #veg_tab(sid int, veg_name varchar(20))
create table #non_veg_tab(sid int, non_veg_name varchar(20))

insert into #mgr_tab values (1,'edible',10,'N')
insert into #mgr_tab values (2,'eating',20,'N')
insert into #mgr_tab values (3,'zebra',30,'V')
insert into #mgr_tab values (4,'zebra',40,'V')


insert into #veg_tab values(30,'carrot')
insert into #veg_tab values(40,'beans')


insert into #non_veg_tab values(10,'Deer')
insert into #non_veg_tab values(20,'Mutton')

select case com_owner.owner_type 
		when 'Company' then com_header.trade_name 
		when 'Person' then per.first_name end 
from hr_company_owner com_owner 
	 left outer join 
	 HR_PERSONS per 
on com_owner.owner_id= per.person_id 
	 left outer join 
    hr_company_header com_header 
on com_owner.owner_id=com_header.company_id

Msg 457, Level 16, State 1, Line 1
Implicit conversion of varchar value to varchar cannot be performed because the collation of the value is unresolved due to a collation conflict.

SELECT MGR.sno, MGR.sname, FOOD.item_name, FOOD.stype
FROM #mgr_tab MGR
JOIN (
   SELECT 'V', sid, veg_name FROM #veg_tab
   UNION ALL
   SELECT 'N', sid, non_veg_name FROM #non_veg_tab
) FOOD(stype, sid, item_name) 
   ON (MGR.sid = FOOD.sid AND MGR.stype = FOOD.stype)

;WITH FOOD(stype, sid, item_name) AS (
   SELECT 'V', sid, veg_name FROM #veg_tab
   UNION ALL
   SELECT 'N', sid, non_veg_name FROM #non_veg_tab
)
SELECT MGR.sno, MGR.sname, FOOD.item_name, FOOD.stype
FROM #mgr_tab MGR
   JOIN FOOD ON (MGR.sid = FOOD.sid AND MGR.stype = FOOD.stype)

/****** Object:  Table [dbo].[HR_COMPANY_HEADER]    Script Date: 01/22/2011 12:59:50 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[HR_COMPANY_HEADER](
	[COMPANY_ID] [int] IDENTITY(1,1) NOT NULL,
	[TRADE_NAME] [varchar](50) NULL,
	[COMMERCIAL_ACTIVITY] [varchar](50) NULL,
	[GENERATE_RESERVATION_DOCUMENT] [char](1) NULL,
	[LICENSE_NUMBER] [varchar](15) NULL,
	[LICENSE_ISSUED_DATE] [datetime] NULL,
	[LICENSE_EXPIRY_DATE] [datetime] NULL,
	[VERIFIER_ID] [int] NULL,
	[INFO_1] [varchar](50) NULL,
	[INFO_2] [varchar](50) NULL,
	[INFO_3] [varchar](50) NULL,
	[INFO_4] [varchar](50) NULL,
	[INFO_5] [varchar](50) NULL,
	
 CONSTRAINT [PK_HR_COMPANY_HEADER] PRIMARY KEY CLUSTERED 
(
	[COMPANY_ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
----------------------------------------


/****** Object:  Table [dbo].[HR_COMPANY_OWNER]    Script Date: 01/22/2011 13:05:34 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[HR_COMPANY_OWNER](
	[COMPANY_OWNER_ID] [int] IDENTITY(1,1) NOT NULL,
	[COMPANY_ID] [int] NULL,
	[PARTNER_STATUS] [int] NULL,
	[OWNER_TYPE] [varchar](50) NULL,
	[OWNER_ID] [int] NOT NULL,
	[INFO_1] [varchar](50) NULL,
	[INFO_2] [varchar](50) NULL,
	[INFO_3] [varchar](50) NULL,
	[INFO_4] [varchar](50) NULL,
	[INFO_5] [varchar](50) NULL,
	
 CONSTRAINT [PK_HR_COMPANY_OWNER] PRIMARY KEY CLUSTERED 
(
	[COMPANY_OWNER_ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[HR_COMPANY_OWNER]  WITH CHECK ADD  CONSTRAINT [FK_HR_COMPANY_OWNER_HR_COMPANY_HEADER] FOREIGN KEY([COMPANY_ID])
REFERENCES [dbo].[HR_COMPANY_HEADER] ([COMPANY_ID])
GO
ALTER TABLE [dbo].[HR_COMPANY_OWNER] CHECK CONSTRAINT [FK_HR_COMPANY_OWNER_HR_COMPANY_HEADER]
------------------------------------------------------------------------------


/****** Object:  Table [dbo].[HR_PERSONS]    Script Date: 01/22/2011 13:36:50 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[HR_PERSONS](
	[FIRST_NAME] [varchar](150) NOT NULL,
	[LAST_NAME] [varchar](150) NOT NULL,
	[TITLE] [varchar](30) NULL,
	[PREFIX] [varchar](30) NULL,
	[SUFFIX] [varchar](30) NULL,
	[MIDDLE_NAMES] [varchar](60) NULL,
	[PERSON_TYPE] [varchar](30) NOT NULL,
	[PERSON_ID] [int] NOT NULL,
	[NATIONAL_IDENTIFIER] [varchar](30) NULL,
	[EFFECTIVE_START_DATE] [datetime] NOT NULL,
	[EFFECTIVE_END_DATE] [datetime] NOT NULL,
	[DATE_EMPLOYEE_DATA_VERIFIED] [datetime] NULL,
	[ON_MILITARY_SERVICE] [int] NULL,
	[SECOND_PASSPORT_EXISTS] [int] NULL,
	
 CONSTRAINT [PK_hr_persons] PRIMARY KEY CLUSTERED 
(
	[PERSON_ID] ASC,
	[EFFECTIVE_START_DATE] ASC,
	[EFFECTIVE_END_DATE] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF