Create DB view to show results

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

Create DB view to show results

Author

Topic

s2002
Starting Member

49 Posts

Posted - 2007-09-10 : 14:38:23

I have 2 Table
Authors
ID Name
1 Clint
2 Voke

Books
BookID ID BookName Price
1 1 Book1 10
2 1 Boo21 12
3 2 Book3 6
4 1 Book4 13
5 1 Book5 2

Now I want to List All Authors and only show Most Expensive book Name of each Author.
So I need this Fields :ID,Name,BookName,BookID,Price.

How could I Write SQL query For It (I want to show results in DB Without Using SP).
I want to Create NEw Views Which Shows my required Results.

thanks,

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-10 : 14:43:24

Using SQL Server 2000 or SQL Server 2005?

E 12°55'05.25"
N 56°04'39.16"

X002548
Not Just a Number

15586 Posts

Posted - 2007-09-10 : 15:04:41

And when's the assignment due?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

s2002
Starting Member

49 Posts

Posted - 2007-09-10 : 18:07:21

I am using MSSQL 2005.
If you could -please- help me the below question Then I could solve mine
in sample "NorthWind" DB there are 2 tables "Products" and "Categories"
Now I want To show MostExpensive Product in each Category.
In other word, I want to Select All categories and then Select Only 1
Product-Expensive-

I hope You coild help me.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-10 : 18:17:04

Something like

SELECT Table1.Col1, Table1.Col2, Table2.Col1, Table2.Col2
FROM (
SELECT Table1.Col1, Table1.Col2, Table2.Col1, Table2.Col2,
ROW_NUMBER() OVER (PARTITION BY Table1.Col1 ORDER BY Table2.Col2 DESC) AS RecID
FROM Table1
INNER JOIN Table2 ON Table2.ID = Table1.ID
) AS d
WHERE RecID = 1

E 12°55'05.25"
N 56°04'39.16"

s2002
Starting Member

49 Posts

Posted - 2007-09-11 : 00:06:25

thanks for your replie pesso,
I have replace parameters to fit my scenario as this:
SELECT authors.ID, Authors.Name, Books.BookID.Col1, Books.BookName
FROM (
SELECT authors.ID, Authors.Name, Books.BookID.Col1, Books.BookName,
ROW_NUMBER() OVER (PARTITION BY authors.ID ORDER BY Books.BookName DESC) AS RecID
FROM authors
INNER JOIN Books ON Books.ID = authors.ID
) AS d
WHERE RecID = 1
--------------------
and These are List Of errors I got after Run Query:
Msg 258, Level 15, State 1, Line 3
Cannot call methods on int.
Msg 4104, Level 16, State 1, Line 3
The multi-part identifier "authors.ID" could not be bound.
Msg 4104, Level 16, State 1, Line 3
The multi-part identifier "Authors.Name" could not be bound.
Msg 4104, Level 16, State 1, Line 3
The multi-part identifier "Books.BookID.Col1" could not be bound.
Msg 4104, Level 16, State 1, Line 3
The multi-part identifier "Books.BookName" could not be bound.

Kristen
Test

22859 Posts

Posted - 2007-09-11 : 01:38:38

Note that in your Outer Select the individual tables in the Inner Select are no long in scope, so you cannot refer to them by their original table name:

SELECT authors.ID, Authors.Name, Books.BookID.Col1, Books.BookName
FROM (
SELECT authors.ID, Authors.Name, Books.BookID.Col1, Books.BookName,
ROW_NUMBER() OVER (PARTITION BY authors.ID ORDER BY Books.BookName DESC) AS RecID
FROM authors
INNER JOIN Books ON Books.ID = authors.ID
) AS d
WHERE RecID = 1

The results from the Inner Select are now aliased as "d", so you need to refer to them using that name instead, i.e.

SELECT d.ID, d.Name, d.BookID.Col1, d.BookName

Kristen

s2002
Starting Member

49 Posts

Posted - 2007-09-11 : 04:10:43

Thanks for Replies,
Now It's Work.
Now I want to imply Few changes:
1-add another table ("Pubs") with these fields:
Declare Pubs Table (PubID int(PK), PubName varchar(50)),Last Prize varchar(50))
2-Add another (PubID int (FK Pubs table)) column to Authors table.

Now I want to Add aother Colum While using above results (Pubinfo)
I want Pubinfo Column shows ('Pubname'+'LastProze')

I have tesed different methods but my problem is that:

1- Where sould I add this query.Should I use Subquery or inner join.
2-Pubinfo Column can't add both "PubName" and "LastPrize " from Pubs table"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-11 : 04:34:33

Add the table in the derived table specification and join properly.

E 12°55'05.25"
N 56°04'39.16"

s2002
Starting Member

49 Posts

Posted - 2007-09-11 : 09:31:54

this is my Join Statement

SELECT Authors.ID, Pubs.PubName + ' ' +Pubs.LastPrize AS Expr1
FROM Pubs INNER JOIN
Authors ON Pubs.PubID = Authors.PubID
--------------------------------------
Now When I alter Above query as

SELECT d.ID, d.Name, d.BookID, d.BookName,
FROM (
SELECT authors.ID, Authors.Name, Books.BookID, Books.BookName, Authors.ID as AuthorPub, Pubs.PubName + ' ' +Pubs.LastPrize AS Expr1
FROM Pubs INNER JOIN
Authors ON Pubs.PubID = Authors.PubID
ROW_NUMBER() OVER (PARTITION BY authors.ID ORDER BY Books.Price DESC) AS RecID
FROM authors
INNER JOIN Books ON Books.ID = authors.ID
) AS d
WHERE RecID = 1
------------------------------------------
I encounter the following error:

Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'FROM'.
Msg 102, Level 15, State 1, Line 6
Incorrect syntax near 'ROW_NUMBER'.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-11 : 09:48:05

[code]SELECT d.ID,
d.Name,
d.BookID,
d.BookName,
FROM (
SELECT authors.ID,
authors.Name,
books.BookID,
books.BookName,
authors.ID AS AuthorPub,
pubs.PubName + ' ' + pubs.LastPrize AS Expr1,
ROW_NUMBER() OVER (PARTITION BY authors.ID ORDER BY Books.Price DESC) AS RecID
FROM pubs
INNER JOIN authors ON authors.PubID = pubs.PubID
INNER JOIN books ON books.ID = authors.ID
) AS d
WHERE d.RecID = 1[/code]

E 12°55'05.25"
N 56°04'39.16"

s2002
Starting Member

49 Posts

Posted - 2007-09-11 : 10:21:44

thanks Peso,
You Really Help me Fine
sincerely yours

renu
Starting Member

47 Posts

Posted - 2007-10-12 : 02:41:51

select a.price,a.categoryname,b.productname
from (select max(p.unitprice)as price,c.categoryname
from products p left join categories c
on p.categoryid=c.categoryid
group by c.categoryname
)a left join
(
select p.productname,max(p.unitprice)as price,c.categoryname
from products p left join categories c
on p.categoryid=c.categoryid
group by p.productname,c.categoryname
)b
on a.price=b.price

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-10-12 : 02:47:46

I am sceptical about
"INNER JOIN Books ON Books.ID = authors.ID"

E 12°55'05.25"
N 56°04'39.16"

Kristen
Test

22859 Posts

Posted - 2007-10-12 : 03:09:15

Well, it might just be a new naming convention?