Tricky SQL - 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

SQL Server 2000 Forums

Transact-SQL (2000)

Tricky SQL

Author

Topic

ckuo@kahluadesigns.com
Yak Posting Veteran

58 Posts

Posted - 2002-08-06 : 01:20:27

Hi,

I'm stuck on this sql statement and was wondering if anyone had any clues on how I would best do this, preferably in a single statement. Here are sample tables.

PRODUCTS TABLE

ProductID-------InternalProductCode-----Name----Price
1---------------101---------------------Test----99.99
2---------------101---------------------Test----129.99
3---------------101---------------------Test----159.99

PRODUCTCHART TABLE

InternalProductCode-----Field1--Field2--Field3
101---------------------1-------1-------1
101---------------------1-------1-------2
101---------------------1-------1-------3
101---------------------1-------1-------4

How would I return the Name, Min. Price, Max.Price of a series of products that share the same InternalProductCode and return a span of values for Field3. so the record would look like

Name----MinPrice--------MaxPrice--------MinField3-------MaxField3
Test----99.99-----------159.99----------1---------------4

Thanks in advance
-Ching

rrb
SQLTeam Poet Laureate

1479 Posts

Posted - 2002-08-06 : 01:43:11

Is this what you want?

select Name, Min(Price) as MinPrice, Max(Price) as MaxPrice, Min(Field3) as MinField3, Max(Field3) as MaxField3
from ProductsTable a inner join ProductChartTable b
on a.code = b.code
group by Name

--
I hope that when I die someone will say of me "That guy sure owed me a lot of money"

Edited by - rrb on 08/06/2002 01:45:13

ckuo@kahluadesigns.com
Yak Posting Veteran

58 Posts

Posted - 2002-08-06 : 12:02:05

Thanks,
One other question. Not all records will have an entry in the ProductChart table, but I still want to return the MinField3 and MaxField3 for if it exists. With the inner join, if a record doesnt exist in the joined table, it will not return any records right? How can I return the record from the Products table regardless or whether it has a matching record in the ProductChart table?

-Ching

jasper_smith
SQL Server MVP & SQLTeam MVY

846 Posts

Posted - 2002-08-06 : 12:32:29

Use a left join rather than an inner join. In the example above the Products Table will be the preserved table.

from ProductsTable a left join ProductChartTable b
on a.code = b.code

HTH
Jasper Smith

joshb
Yak Posting Veteran

52 Posts

Posted - 2002-08-06 : 12:35:03

Change the INNER JOIN to a LEFT OUTER JOIN.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources