Re: SQL or programming?

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: Tom Moreau (tom_at_dont.spam.me.cips.ca)
Date: 06/08/04 

Date: Tue, 8 Jun 2004 10:30:05 -0400

Problem 1:

select
    a.Item
, b.Cost
, b.Category
from
(
    Item
, min (Cost) as Cost
from
    TableB
group by
    Item
) as x
join TableB as b on b.Item = x.Item
        and b.Cost = x.Cost
right join TableA as a on a.Item = b.Item

It will give you output even if there are no corresponding entries in
TableB.

Problem 2:

select
    a.Item
, b.Cost
, b.Category
from
(
    Item
, max (Cost) as Cost
from
    TableB
group by
    Item
) as x
join TableB as b on b.Item = x.Item
        and b.Cost = x.Cost
right join TableA as a on a.Item = b.Item 

-- 
Tom
---------------------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Ivan Debono" <ivanmdeb@hotmail.com> wrote in message
news:uFRiZKWTEHA.3752@TK2MSFTNGP12.phx.gbl...
Hi all,
I'm no expert in SQL syntax and I would solve the following 2 problems
through code. But I thought I could be better off writing a SQL statement.
The problem is... I don't how to do it!!
Well, here's the scenario:
I have a Table A containing a list of items. Any of these items can be
listed more than once in a Table B and have a different cost. Apart from the
cost field in Table B, each item has a category field.
So here are my problems:
1. I would like to list the items in Table A with their lowest cost which is
found in Table B.
2. List the items in Table A grouping by the most category.
As an example, I have
Table A:
Item 1
Item 2
Item 3
Item 4
Table B:
Item 1, $10, Category1
Item 1, $15, Category2
Item 2, $16, Category1
Item 2, $12, Category2
Item 3, $12, Category1
Item 3, $10, Category3
Item 4, $10, Category4
For problem 1, I need to have the results:
Item 1, $10, Category1
Item 2, $12, Category2
Item 3, $10, Category3
Item 4, $10, Category4
For problem 2, I need to have the results:
Item 1, $10, Category1
Item 2, $16, Category1
Item 3, $12, Category1
Item 4, $10, Category4
Is it possible to achieve this?? If yes, any examples would be
appreciated!!!
Thanks,
Ivan

Relevant Pages

  • SQL or programming?
    ... I'm no expert in SQL syntax and I would solve the following 2 problems ... listed more than once in a Table B and have a different cost. ... cost field in Table B, each item has a category field. ... Item 1, $10, Category1 ...
    (microsoft.public.sqlserver.programming)
  • Re: Using a "match" table to store multiple columns for parent data
    ... Category1 = MAX, ... FROM ProductCategories PC ... If you want more product categories, you will need to extend the query. ... Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx ...
    (comp.databases.ms-sqlserver)
  • Re: howto use the query results as view column?
    ... how can create a view like this (id, name, category1, category2, ... category3) with high performance? ... Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx ... Books Online for SQL Server 2005 at ...
    (comp.databases.ms-sqlserver)
  • Re: Hierachical Relationship driving me nuts.
    ... If you are using SQL Server 2005, ... I need to then be able to retrieve a particular category and its children (to a specified depth) and display it in a repeater. ... ---category1 child ...
    (microsoft.public.dotnet.framework.aspnet)
  • Re: SQL or programming?
    ... Selecting the category which occurs the most for items in Table B. So if ... category1 occurs most even though some items cost less, ... > Thomas A. Moreau, BSc, PhD, MCSE, MCDBA ... > Columnist, SQL Server Professional ...
    (microsoft.public.sqlserver.programming)