Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Select and Varbinary

Author  Topic 

shapper
Constraint Violating Yak Guru

450 Posts

Posted - 2012-02-01 : 13:29:03
Hello,

I have the following SQL tables:

create table dbo.Cubes
(
Id int identity not null
constraint Cubes_Id_PK primary key clustered (Id),
Name nvarchar (120) not null
constraint Cubes_Name_U unique
)

create table dbo.CubesFiles
(
CubeId int not null,
FileId int not null,
constraint CubesFiles_CubeId_FileId_PK primary key clustered (CubeId, FileId)
)

create table dbo.Files
(
Id int identity not null
constraint Files_Id_PK primary key clustered (Id),
Data varbinary (max) filestream null
constraint Files_Data_DF default (0x),
[Key] uniqueidentifier rowguidcol not null
constraint Files_Key_U unique,
Mime nvarchar (200) not null
) filestream_on [STORAGE];


I need to select the last 80 cubes which contains files of type Image.

A file of type image has Mime = "image/jpg" or Mime = "image/gif".

I would like to select the Cubes without the File Data field (varbinary).

1 - How can I do this?

2 - Should I add a field Type in Cubes?

When a Cube is created I would check the Files Mimes and add a Type.

This seems redundant if I am able to do (1).

But what do you think?

Thank You,

Miguel

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-02-01 : 13:37:00
[code]
SELECT c.Name,f.*
FROM dbo.CubesFiles cf
INNER JOIN dbo.Cubes c
ON c.Id = cf.CubeId
INNER JOIN dbo.Files f
ON f.Id = cf.FileId
WHERE f.Data IS NULL
AND (f.Mime = 'image/jpg'
OR f.Mime = 'image/gif')
[/code]


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

shapper
Constraint Violating Yak Guru

450 Posts

Posted - 2012-02-01 : 13:51:45
Hello visakh16,

I didn't want to select the Cubes where the File.Data is null.
I just do not want to load File.Data into Memory.

For the selected Cubes, can I return one more field, CubeType which value becomes:

1 - "PDF" if Mime = "application/pdf";
2 - "Image" if Mime = 'image/jpg' OR Mime = 'image/gif';

Thank You,
Miguel
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-02-02 : 10:31:33
[code]
SELECT c.Name,
f.[Key],f.[Mime],
CASE WHEN f.[Mime]='application/pdf' THEN 'PDF'
ELSE 'Image'
END AS CubeType
FROM dbo.CubesFiles cf
INNER JOIN dbo.Cubes c
ON c.Id = cf.CubeId
INNER JOIN dbo.Files f
ON f.Id = cf.FileId
WHERE f.Mime IN ('image/jpg','image/gif','application/pdf')
[/code]




------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page
   

- Advertisement -