select statement

Question

Post reply

select statement

smfiber

SSC Veteran

Points: 235
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 Old Hand Points: 347 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: 235 More actions · Answer 2

smfiber

SSC Veteran

Points: 235

October 14, 2002 at 12:49 am

#438458

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: 723 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 Old Hand Points: 347 More actions · Answer 4

anbu

Old Hand

Points: 347

October 14, 2002 at 1:09 am

#438462

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.