CODEWITHSUNDEEP
sqlsql-server-2008view
george9170
george9170

Reputation:

SQL View Creation

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

Upvotes: 0

Views: 154

Answers (4)

D'Arcy Rittich
D'Arcy Rittich

Reputation: 171589

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'

Upvotes: 1

James Westgate
James Westgate

Reputation: 11464

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

Upvotes: 0

bobince
bobince

Reputation: 536775

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';

Upvotes: 1

Smandoli
Smandoli

Reputation: 7019

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

Upvotes: 1

Related Questions