select statement

Question

select statement

smfiber

SSC Veteran

Points: 265
More actions
October 14, 2002 at 12:18 am

#158916

I was hoping someone could help on this one...
Here is my statement:
select * from test_td a, test_im b
where a.MfgPartNbr=1 and b.Mfgpartnbr=1
Table structure is this:
test_td = col_id, MfgPartNbr, Cost
test_im = col_id, MfgPartNbr, Cost
I want to compare the value of cost and return the lowest value.
in other words:
select * from test_td a, test_im b
where a.MfgPartNbr=1 and b.Mfgpartnbr=1
and cost=lowest_value
Thanks in advance.
Andrew

Andrew
http://eshopsoho.com

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

anbu SSC-Addicted Points: 407 More actions · Answer 1

Hi

I don't know if I have understood you correctly. But my understanding is that you have two tables with the same kind of Mfgpartnumber and cost values.And you need the minimum cost for each Mfgpartnumber.

If this is the case, then the following query will work.

select MfgPartNbr, min(cost) from (select * from test_td

union

select * from test_tm) x

group by MfgPartNbr

If not, please reply back.

Thanks,

Anbu

smfiber SSC Veteran Points: 265 More actions · Answer 2

Anbu,

AWESOME!!! THAT Works great. I see you can use max(cost) as well. Are there other arguments?

Also, what does the 'x' before group mean?

Thanks Again,

Andrew

Andrew
http://eshopsoho.com

Mike2265 Right there with Babe Points: 753 More actions · Answer 3

smfiber,

If you join it as you, for each record in one table you'll get all the records in the other table. Although the where clause will cut it down it's not a good way to go. Now if there's only one record for each mfgPartNbr in each table then you could do this.

select

MfgPartNbr,

Case

when (a.Cost - b.Cost) < 0 then b.Cost

else a.cost

end

from

test_td a

inner join

test_im b

on a.MfgPartNbr = b.Mfgpartnbr

where mfgPartNbr = <value>

If there's more than one record then either do a union between the two tables as a sub query and then select the max value.

Alternatively do a select max etc subquery for each table and join on this. But this and the above query won't work if there's a chance of there been no records for that part number in either table as an outer join would be required and you don't which table to depend on.

You should have a table that has the MfgPartNbr as a unique record. This can be joined to each of the tables as an outer join to ensure you have always have a record and then compare on this (using coalesce to avoid nulls)

Without knowing the structure in detail it's a bit hard to tell the best way but I hope this helps.

Cheers,

Mike.

anbu SSC-Addicted Points: 407 More actions · Answer 4

x is an imaginary table name, that can be anything. You can use all the aggregare functions there. The concept is very simple. I am creating an imaginary table x, which is actually the output of the query that I have there. And then, I query the table x.

julianhaynes Valued Member Points: 51 More actions · Answer 5

How about something like this...

SELECT

TD.col_id

,TM.colid

,CASE WHEN ISNULL(TD.Cost, 999999) < ISNULL(TN.Cost, 999999) THEN TD.Cost ELSE TM.Cost END AS 'Lowest Cost'

FROM

test_td AS TD

FULL OUTER JOIN test_im AS IM ON IM.MfgPartNbr = TD.MfgPartNbr

julianhaynes Valued Member Points: 51 More actions · Answer 6

Woops, that second ISNULL should be zero - assuming you don't have any negative costs.

Mike2265 Right there with Babe Points: 753 More actions · Answer 7

Thanks Julian - I'd never noticed the Full Outer Join before.

And with that knowledge in hand Julians is definately the way to go.

cheers,

Mike