How to get unique records while joining tables?

[iren]

I have table1 and table2. Table2 has multiple IDs. All I need is to merge these two tables by their IDs in order to get column COST from table2 in addition to all table1’s fields. I just need a file Newtable of unique records How can I do it?

Thank you in advance

Iren

 

[AlexCuse]

What database are you using?

Are you trying to sum the column COST in table 2?

I think something like this would work:

select a.ID, a.OtherField, a.AnotherField, b.COST
from Table1 a inner join
(
select ID, sum(cost) from Table2 group by ID
)
on a.ID = b.ID

Hope it helps,

Alex

Ignorance of certain subjects is a great part of wisdom

 

[iren]

Alex,

I am not trying to get a sum. Just tying to get a field from table2.....

 

[AlexCuse]

If there are multiple ID's on table2, you will need to use some kind of aggregate (sum, max, or min) to trim cost to one row. Unless you want to show all data from table 1 several times?

Can you post some sample data and desired result?

ALex

Ignorance of certain subjects is a great part of wisdom

 

[PHV]

Have a look here:

http://www.devshed.com/c/a/MySQL/Understanding-SQL-Joins

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[iren]

Alex,

I see...then I can put min(cost_key) as it is the same value for the same ID?

I need ID, fIELD1, FIELD 2, COST_KEY
And (OOPS!) I need a condition : inv in thable2 should not be equal 2

TABLE 2

ID COST_KEY inv
mem1 123 2
mem1 123
mem1 123
mem2 224 2
mem2 224
mem3 335
mem3 335
mem3 335 2
mem3 335



TABLE1

ID FIELD1 FIELD2
mem1 a11 a22
mem2 b11 b22
MEM3 c11 c22


NEWTABLE

ID FIELD1 FIELD2 COST_KEY

mem1 a11 a22 123
mem2 b11 b22 224
MEM3 c11 c22 335

 

[PHV]

Something like this ?
SELECT DISTINCT A.ID, A.FIELD1, A.FIELD2, B.COST_KEY
FROM TABLE1 A INNER JOIN TABLE2 B ON A.ID = B.ID
WHERE B.INV <> 2

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[AlexCuse]

It looks like you have the same Cost_Key assigned to each ID? If taht is the case, PH's suggestion should work well. Any different cost_keys will mess things up for you though, so keep an eye on that.

Good Luck,

Alex

Ignorance of certain subjects is a great part of wisdom