multiple queries query - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

multiple queries query

Author

Topic

shmuelzon
Starting Member

4 Posts

Posted - 2010-07-15 : 01:23:17

Hey all,

I'm working on a little project and have the following tables:

Files:
FileID - auto increment integer (Primary key)
FilePath - string

MetadataTypes:
TypeID - auto increment integer (Primary key)
TypeName - string

Metadata:
FileID - integer (Primary key)
TypeID - integer (Primary key
Value - String

I'm trying to figure out a query that will result with the following

FileID | Path | Title | Artist | Album...

The FileID and Path are taken from the Files table, the artist column should actually be the result of a query done in the metadata table such as:
SELECT Value FROM Metadata WHERE FileID=XX AND TypeID=4

The same for title, artist, etc. except a different TypeID. Assuming I get the type IDs beforehand, how do i write such a query?

Thanks in advance!

shmuelzon
Starting Member

4 Posts

Posted - 2010-07-15 : 01:47:11

I was able to reach the following:

SELECT
f.FileID,
f.Path,
(SELECT Value FROM Metadata WHERE TypeID=130 AND FileID=f.FileID) as Artist,
(SELECT Value FROM Metadata WHERE TypeID=4 AND FileID=f.FileID) as Title,
(SELECT Value FROM Metadata WHERE TypeID=129 AND FileID=f.FileID) as Album,
(SELECT Value FROM Metadata WHERE TypeID=132 AND FileID=f.FileID) as "Track No.",
(SELECT Value FROM Metadata WHERE TypeID=131 AND FileID=f.FileID) as Genre
FROM Files f, Metadata m
Where f.FileID = m.FileID

Is there a better/efficient way to do this?
Also, this way I receive the same record multiple times, looks like it's once for every column that isn't null...
Can I filter these duplicates in the query?

Thanks again

Devart
Posting Yak Master

102 Posts

Posted - 2010-07-15 : 02:10:23

Hello,

You can try this:

SELECT
f.FileID,
f.Path,
(SELECT Value FROM Metadata WHERE TypeID=130 AND FileID=f.FileID) as Artist,
(SELECT Value FROM Metadata WHERE TypeID=4 AND FileID=f.FileID) as Title,
(SELECT Value FROM Metadata WHERE TypeID=129 AND FileID=f.FileID) as Album,
(SELECT Value FROM Metadata WHERE TypeID=132 AND FileID=f.FileID) as "Track No.",
(SELECT Value FROM Metadata WHERE TypeID=131 AND FileID=f.FileID) as Genre
FROM Files f

Best Regards,

Devart Team

shmuelzon
Starting Member

4 Posts

Posted - 2010-07-15 : 02:18:05

That works out great!
Thanks a lot!

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2010-07-15 : 04:35:36

[code]
try this one without subqueries
SELECT
f.FileID,
f.Path,
CASE WHEN m.Typeid = 130 THEN m.Value END as Artist,
CASE WHEN m.Typeid = 4 THEN m.Value END as Title,
CASE WHEN m.Typeid = 129 THEN m.Value END as Album,
CASE WHEN m.Typeid = 132 THEN m.Value END as "Track No.",
CASE WHEN m.Typeid = 131 THEN m.Value END as Genre
FROM Files f,
JOIN Metadata m ON f.FileID = m.FileID
[/code]

shmuelzon
Starting Member

4 Posts

Posted - 2010-07-15 : 04:51:40

hey,

That specific query returned 6 records for each file,
one where all the meta tags are null, and one for each tag where the rest of them are null

any way to merge them together?

also, it seems that the sub-queries version worked almost three times faster than the cases. (all in the milliseconds range, but still)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources