SQL View Creation

Question

I have two tables

Table FOO 
FooUniqueID| Year| Name| Worth|
---------------------------
1           2008   Bob    23.00 
2           2009   Bob    40200

Table Bar 
BarUniqueID | Name | Value
-----------------------
 1aBc         Year   2009

I would like to create a view. That will select everything from FOO where the Year is equal

select value from Bar where name = year

without using a sub query.

thank you

You are supposed to update your question and retag it, not add the update as comment — Kronass, Apr 19 '10 at 16:49
similar: http://stackoverflow.com/questions/227037/can-i-get-better-performance-using-a-join-or-using-exists — Glennular, Apr 19 '10 at 16:51

score 1 · Answer 1 · answered Apr 19 '10 at 16:46

1

It depends on what kind of program is at work. This would work for some SQL flavors, I believe.

select value from FOO, Bar where FOO.year = Bar.year

answered Apr 19 '10 at 16:46

Smandoli

6,800
3
46
80

D'Arcy Rittich · Answer 2 · 2010-04-19T16:54:13.143

1

create view baz as 
select f.FooUniqueID, f.Year, f.Name as FooName, f.Worth,
    b.BarUniqueID, b.Name as BarName, b.Value 
from foo f 
inner join bar b on f.Year = b.Value and b.name = 'Year'

edited Apr 19 '10 at 16:54

answered Apr 19 '10 at 16:49

D'Arcy Rittich

160,735
37
279
278

score 1 · Answer 3 · answered Apr 19 '10 at 16:50

1

I don't think there's much point creating a VIEW for this alone, it's a trivial join:

SELECT FOO.*
FROM Bar
JOIN FOO ON FOO.Year=Bar.Value
WHERE Bar.Name='Year';

answered Apr 19 '10 at 16:50

bobince

514,530
102
640
820

The view contains much more data, but I limited the problem to the underlying issue. I need to create a view that grabs a config value from another table. – Apr 19 '10 at 16:53
+1, that is still trivial, watch out for nesting too many views, performance will go down – KM. Apr 19 '10 at 17:32

score 0 · Accepted Answer · answered Apr 19 '10 at 16:51

0

SELECT 
  FooUniqueID, Year, Name, Worth
FROM
  FOO
JOIN
  BAR on FOO.Year = BAR.Value
WHERE
  BAR.Name = 'Year'

answered Apr 19 '10 at 16:51

James Westgate

10,989
7
61
67

SQL View Creation

4 Answers4