0

Here is the db schema

Tables-

enter image description here

I want to write a query to search all files which have a metadata value like('abc') and get a specific corresponding metadata value for that file.

So for above search for all files which have abc1 as one of the values and give me its corresponding 'geo' key's value. I know it can be done by a subquery. But want to find the most efficient way.

blue01
  • 2,035
  • 2
  • 23
  • 38

1 Answers1

1

Could look like this. Should be the most efficient way, too.

SELECT m0.m_value
FROM   metadata m
JOIN   file_metadata fm ON fm.m_foreign_key = m.m_id
JOIN   file_metadata fm0 ON fm0.f_foreign_key = fm.f_foreign_key
--                      AND fm0.m_foreign_key <> fm.m_foreign_key
--  may or may not be necessary, depending on the selectivity of 'geo'
JOIN   metadata m0 ON m0.m_id = fm0.m_foreign_key
WHERE  m.m_value = 'abc1'
AND    m0.key = 'geo'

Be sure to assign aliases to multiple instances of the same table in one query.

For an overview of related query techniques see this lineup: How to filter SQL results in a has-many-through relation.

Community
  • 1
  • 1
Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
  • What do you mean by selectivity of 'geo'? – blue01 Oct 25 '11 at 05:06
  • @Harpreet If `m_key` is not unique - in particular, if there can be a `m.m_key = 'geo'` - the commented clause would make a difference. – Erwin Brandstetter Oct 25 '11 at 06:19
  • I don't think the 'techniques' (relational division, albeit in a simplified form) in the question are directly relevant here. – onedaywhen Oct 25 '11 at 07:32
  • @onedaywhen: I think they perfectly are. This is another case of an n:m relationship (has-many-through), and the question specifically asks about different query styles and their efficiency. ("...subquery. But want to find the most efficient way.") – Erwin Brandstetter Oct 25 '11 at 07:50
  • The answer by ypercube for that question is not a valid approach for this question. p.s. you didn't address my question there about the differences in performance not being significant. I have nothing further to add :) – onedaywhen Oct 25 '11 at 10:10
  • @onedaywhen: I added the link here for a lineup of *related query techniques*. The *valid approach for this question* is **my answer above**. I did not answer over there, because I think you made a good point in your comment and I didn't have anything to add. – Erwin Brandstetter Oct 25 '11 at 10:43