Need Query to display the expected result

vajeed · February 5, 2020, 1:06pm

Hi all,

am stuck in between

expected output is below

case 1.

Documenttype Filepath Remarks Filename DocumentNo Hijri Gregorian issuedate
CR Certificate SAB General Contracting. c:/filename test NULL 123 NULL NULL NULL
Insurance_GOSI NULL dummy NULL 342 NULL NULL NULL
Sponser ID NULL NULL NULL NULL NULL NULL NULL

currently i have only 2 record in my database but which have to add the 3rd row that is "Sponser ID" is not exist but i want that row with
rest columns (Filepath , Remarks ,Filename,DocumentNo,Hijri , Gregorian, issuedate) as null

case 2.

Documenttype Filepath Remarks Filename DocumentNo Hijri Gregorian issuedate
CR Certificate SAB General Contracting. c:/filename test NULL 123 NULL NULL NULL
Insurance_GOSI NULL NULL NULL NULL NULL NULL NULL
Sponser ID NULL dummy xyz 342 NULL NULL NULL

similarly in case 2. i have second record null but still i want this record to be display.

how can i do that ?

mike01 · February 5, 2020, 3:15pm

without DDL, sample data and expected results, we can't help you. What you have above doesn't make any sense to me

vajeed · February 5, 2020, 6:18pm

please let me know if am missing something in my code

yosiasz · February 5, 2020, 6:30pm

you are missing the following as @mike01 asked for.
2 options: give us remote full access to your SQL Server or provide sample data that emulates what you have so that we can run it on our SQL Server

Please provide sample data as DDL and DML as follows. This is a sample you will need to provide your own for the question you are asking. Help us help you

--the following is DDL Data Definition Language
create table #jeevad(Documenttype varchar(50),
 Filepath varchar(50),Remarks varchar(50), Filename varchar(50),
DocumentNo int, Hijri  nvarchar(50),Gregorian nvarchar(50), issuedate date)

--the following is DML Data Manipulation Language
insert into #jeevad
select 'CR Certificate SAB General Contracting.', 
'c:/filename test', NULL, 123, NULL, NULL, NULL union

--etc etc etc

then you see we have something to work with. You will have provided us a sample of your data. Otherwise it is just guess work.

mike01 · February 6, 2020, 5:00pm

I cleaned up your inserts and you need to use SponsorCode instead of name

CREATE TABLE #tbl_HR_Master_DocumentMaster_Sponsor(
[DocumentMasterId] [int] IDENTITY(1,1) NOT NULL,
[FK_SponsorId] [int] NULL,
[FK_DocumentTypeId] [int] NULL,
[FilePath] [nvarchar](max) NULL,
[Filename] [nvarchar](200) NULL,
[DocumentNo] [nvarchar](100) NULL,
[ExpiryDateHijri] [date] NULL,
[ExpiryDateGregorian] [date] NULL,
[issuedate] [date] NULL,
[Remarks] [nvarchar](max) NULL,
[CreatedDate] [datetime] NULL,
[ModifiedDate] [datetime] NULL,
[StatusFlag] [int] NULL
)

insert into #tbl_HR_Master_DocumentMaster_Sponsor values(1,1,'a.jpg',null,123,null,null,null,null,null,null,null)
insert into #tbl_HR_Master_DocumentMaster_Sponsor values(1,22,'b.jpg',null,123,null,null,null,null,null,null,null)
insert into #tbl_HR_Master_DocumentMaster_Sponsor values(1,24,'c.jpg',null,123,null,null,null,null,null,null,null)
insert into #tbl_HR_Master_DocumentMaster_Sponsor values(2,1,'x.jpg',null,123,null,null,null,null,null,null,null)
insert into #tbl_HR_Master_DocumentMaster_Sponsor values(3,1,'y.jpg',null,123,null,null,null,null,null,null,null)

CREATE TABLE #tbl_HR_Master_Sponsors(
[SponsorID] [int] IDENTITY(1,1) NOT NULL,
[SponsorCode] [nvarchar](50) NOT NULL,
[Name] [nvarchar](100) NOT NULL,
[Address] [nvarchar](1000) NULL,
[ZipCode] [nvarchar](20) NULL,
[Phoneno] [nvarchar](20) NULL,
[Fax] [nvarchar](20) NULL,
[Email] [nvarchar](max) NULL,
[StatusFlag] [int] NULL,
[CreatedDate] [datetime] NULL,
[ModifiedDate] [datetime] NULL)

insert into #tbl_HR_Master_Sponsors values('AL','Leyalnd','dummy',123,null,null,null,null,null,null)
insert into #tbl_HR_Master_Sponsors values('TT','tata','dummy',123,null,null,null,null,null,null)
insert into #tbl_HR_Master_Sponsors values('BZ','Benz','dummy',123,null,null,null,null,null,null)


CREATE TABLE #tbl_HR_Master_DocTypeCategory(
[DocTypeCategoryID] [int] IDENTITY(1,1) NOT NULL,
[Category] [nvarchar](100) NULL,
[StatusFlag] [int] NULL,
[CreatedDate] [datetime] NULL,
[ModifiedDate] [datetime] NULL)

insert into #tbl_HR_Master_DocTypeCategory values('sponsor',1,null,null)
insert into #tbl_HR_Master_DocTypeCategory values('Employee',1,null,null)
insert into #tbl_HR_Master_DocTypeCategory values('Vehicle',1,null,null)

CREATE TABLE #tbl_HR_Master_DocumentType(
[DocumentTypeID] [int] IDENTITY(1,1) NOT NULL,
[FK_DocTypeCategory] [int] NOT NULL,
[FK_DateType] [int] NOT NULL,
[DocumentCode] [nvarchar](20) NOT NULL,
[Description] [nvarchar](max) NOT NULL,
[DocumentNoRequired] [int] NULL,
[IssueDateRequired] [int] NULL,
[ExpiryDateRequired] [int] NULL,
[CreatedDate] [datetime] NULL,
[ModifiedDate] [datetime] NULL,
[StatusFlag] [int] NULL)

set IDENTITY_INSERT  #tbl_HR_Master_DocumentType  on 

insert into #tbl_HR_Master_DocumentType (DocumentTypeID,FK_DocTypeCategory,FK_DateType,DocumentCode,Description,DocumentNoRequired,IssueDateRequired,ExpiryDateRequired,CreatedDate,ModifiedDate,StatusFlag)
 values(1,3,1,'D1','CR',1,1,1,null,null,1)
insert into #tbl_HR_Master_DocumentType (DocumentTypeID,FK_DocTypeCategory,FK_DateType,DocumentCode,Description,DocumentNoRequired,IssueDateRequired,ExpiryDateRequired,CreatedDate,ModifiedDate,StatusFlag) values(22,3,2,'D1','GOSI',1,1,1,null,null,1)
insert into #tbl_HR_Master_DocumentType (DocumentTypeID,FK_DocTypeCategory,FK_DateType,DocumentCode,Description,DocumentNoRequired,IssueDateRequired,ExpiryDateRequired,CreatedDate,ModifiedDate,StatusFlag) values(24,3,2,'D1','SID',1,1,1,null,null,1)


SELECT t.description AS [Documenttype], 
       NULL                                    AS Filepath, 
       NULL                                    AS [Remarks], 
       NULL                                    AS [Filename], 
       NULL                                    AS DocumentNo, 
       NULL                                    AS ExpiryDateHijri, 
       NULL                                    AS ExpiryDateGregorian, 
       NULL                                    AS issuedate 
--,null as CreatedDate 
FROM   #tbl_hr_master_documenttype t
       INNER JOIN #tbl_hr_master_doctypecategory c
               ON t.fk_doctypecategory = c.doctypecategoryid 
WHERE  c.category = 'Sponsor' 
union all
Select t.description AS [Documenttype], 
       ds.filepath, 
       ds.[remarks], 
       ds.[filename], 
       ds.documentno, 
       ds.expirydatehijri, 
       ds.expirydategregorian
       ,ds.issuedate 
from  #tbl_hr_master_sponsors s
	join #tbl_hr_master_documentmaster_sponsor ds
		on s.SponsorID = ds.FK_SponsorID 
	join #tbl_hr_master_documenttype t
		on t.DocumentTypeID = ds.FK_DocumentTypeID
	join #tbl_hr_master_doctypecategory  c
		on c.DocTypeCategoryID = t.FK_DocTypeCategory
where s.SponsorCode = 'AL'