Data Link problem - Duplicate Entries

[IceRuby]

Hello there

I need to create a link between two tables via a primary key. This issue is the relationship between the tables (see below for example)

Primary Table Secondary table
1 to 0 or 1 or many match
OR OR
many 1

(i.e. the primary table can have 0, 1 or many matchings records within secondary table OR the secondary table could hold 1 record and primary table hold many records)

I have tried a left outer join and = link but still get duplicated entries.

Appreciate assistance

Thanks

 

[synapsevampire]

This is an age old question which is generally because the developer doesn't understand the data, or databases.

Your example isn't very helpful, try posting example data and expected output.

A left outer from Parent (Primary) to Child (secondary) will net all rows in the Parent (even if there aren't any in the Child) and all rows from the Child that match each key.

-k

 

[IceRuby]

Hi and thanks for feedback

See if I can provide a better example:

Primary table A Secondary table B
LINK GL Account GL Account
GL Amount Budget Amount
2102 $100.00
2610 $500.00 2610 $250.00
3210 $900.00 3210 $900.00
3210 $320.00
3210 $190.00
9999 $540.00 9999 $600.00
9999 $500.00
9999 $250.00

Primary table will always hold 1 or many records. Secondary table can hold no record, 1 record or many records which is why left join does not work.

Currently, any nulls within the Secondary table results in records being duplicated.

Required result set.
Primary table Secondary table
Sum of GL Amount Sum of Budget Amount
2102 $100.00
2610 $500.00 2610 $250.00
3210 $1410.00 3210 $900.00
9999 $540.00 9999 $1350.00

Thanks

 

[lyanch]

What you need is a full outer join. Barring that, you can create a union query to bring in all the records from both table.

Select from firsttable
UNION
select from second table

Lisa