Advanced (for me) SQL query

[Jeff Hutchison]

Imagine a single table (fil) that has columns and entries like this:

Filename	metadata
file001.dwg	1
file001.pdf	1
file002.dwg	4
file003.pdf	banana
file004.dwg	2
file004.pdf	cartoon
file005.pdf	exlax

How would I write a query that outputs the dwg metadata if no pdf exists, but outputs the pdf data if a pdf exists for each filename? Hoping to avoid REBAR obviously :P

i.e. this output:

Filename	metadata
file001.pdf	1
file002.dwg	4
file003.pdf	banana
file004.pdf	cartoon
file005.pdf	exlax

[Jeff Hutchison]

So... I slept on it and came up with a solution!

The S_Longname column is the full filename, but there's also an S_Ext column that I'm using to determine whether it's a dwg or not... 

Select everything from the table where the S_Longname is in a table consisting of all the dwg only's, all the non-dwg only's, and the non-dwg where both exist.

Select *

From fm110fil
Where S_LONGNAME in
(
SELECT /* Get all the dwg only files */
CONCAT(left(fil.[S_LONGNAME],len(fil.[S_LONGNAME])-len(fil.[S_EXT])),fil.S_EXT)
 FROM [AdeptDB].[dbo].[fm110fil] fil
 left join (select * from fm110fil where s_ext = '.dwg') dwgs on
 left(fil.[S_LONGNAME],len(fil.[S_LONGNAME])-len(fil.[S_EXT])) = left(dwgs.[S_LONGNAME],len(dwgs.[S_LONGNAME])-len(dwgs.[S_EXT]))
  left join (select * from fm110fil where s_ext not like '.dwg') nds on
 left(fil.[S_LONGNAME],len(fil.[S_LONGNAME])-len(fil.[S_EXT])) = left(nds.[S_LONGNAME],len(nds.[S_LONGNAME])-len(nds.[S_EXT]))
 where dwgs.S_EXT is not null and nds.S_EXT is null

UNION SELECT /* Get all the pdf only files */
concat(left(fil.[S_LONGNAME],len(fil.[S_LONGNAME])-len(fil.[S_EXT])),fil.S_EXT)
 FROM [AdeptDB].[dbo].[fm110fil] fil
 left join (select * from fm110fil where s_ext = '.dwg') dwgs on
 left(fil.[S_LONGNAME],len(fil.[S_LONGNAME])-len(fil.[S_EXT])) = left(dwgs.[S_LONGNAME],len(dwgs.[S_LONGNAME])-len(dwgs.[S_EXT]))
  left join (select * from fm110fil where s_ext not like '.dwg') nds on
 left(fil.[S_LONGNAME],len(fil.[S_LONGNAME])-len(fil.[S_EXT])) = left(nds.[S_LONGNAME],len(nds.[S_LONGNAME])-len(nds.[S_EXT]))
 where dwgs.S_EXT is null and nds.S_EXT is not null

UNION SELECT /* Get only pdfs where both exist */
concat(left(fil.[S_LONGNAME],len(fil.[S_LONGNAME])-len(fil.[S_EXT])),nds.S_EXT)
 FROM [AdeptDB].[dbo].[fm110fil] fil
 left join (select * from fm110fil where s_ext = '.dwg') dwgs on
 left(fil.[S_LONGNAME],len(fil.[S_LONGNAME])-len(fil.[S_EXT])) = left(dwgs.[S_LONGNAME],len(dwgs.[S_LONGNAME])-len(dwgs.[S_EXT]))
  left join (select * from fm110fil where s_ext not like '.dwg') nds on
 left(fil.[S_LONGNAME],len(fil.[S_LONGNAME])-len(fil.[S_EXT])) = left(nds.[S_LONGNAME],len(nds.[S_LONGNAME])-len(nds.[S_EXT]))
 where dwgs.S_EXT is not null and nds.S_EXT is not null
)