By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
select @@version;
| @@version |
|---|
| 8.0.27 |
create table sampletable(fileid int,contolnames varchar(50))
create table Files(fileid int,file1 varchar(50),file2 varchar(50))
insert into sampletable values(1,'abc'),(2,'ghf'),(3,'hjy'),(4,'juk')
insert into Files values (1,'abc.pdf','abcdef.pdf'),(2,'ghf.pdf','ghfjkl.pdf'),
(3,'hjy.pdf','hjyui.pdf')
select * from Files
| fileid | file1 | file2 |
|---|---|---|
| 1 | abc.pdf | abcdef.pdf |
| 2 | ghf.pdf | ghfjkl.pdf |
| 3 | hjy.pdf | hjyui.pdf |
select * from sampletable
| fileid | contolnames |
|---|---|
| 1 | abc |
| 2 | ghf |
| 3 | hjy |
| 4 | juk |
select st.fileid,st.contolnames,case when st.fileid in (select fileid from Files)
then f.file1
when st.fileid in (select fileid from Files) then f.file2 end as Files
from sampletable st
join Files f on f.fileid=st.fileid
| fileid | contolnames | Files |
|---|---|---|
| 1 | abc | abc.pdf |
| 2 | ghf | ghf.pdf |
| 3 | hjy | hjy.pdf |
select * from
(
select a.fileid,contolnames,file1 as files
from Files a inner join sampletable b on a.fileid=b.fileid
union
select a.fileid,contolnames,file2 as files
from Files a inner join sampletable b on a.fileid=b.fileid
)A order by fileid
| fileid | contolnames | files |
|---|---|---|
| 1 | abc | abc.pdf |
| 1 | abc | abcdef.pdf |
| 2 | ghf | ghf.pdf |
| 2 | ghf | ghfjkl.pdf |
| 3 | hjy | hjy.pdf |
| 3 | hjy | hjyui.pdf |