[SOLVED] How do I join a one-to-many table with results only appended to the smaller table?

How do I join a one-to-many table with results only appended to the smaller table?

Basically I have a one-to-many table. I want to append the columns of the larger table to the result set of the smaller table. I will end up with a result set the same size of the smaller table, but again, with the relevant information from the many sided table.

One catch: the many sided table doesn't have a primary key defined, although a composite key could be established (again it isn't there as constraint).

Solution

Since more than one record in t_large may correspond to a record in t_small, you need to choose what exactly you want to pick from t_large.

This means that you'll either need to aggregate all corresponding records from t_large (how?), or select a single record out of many (which?).

SELECT  s.*, SUM(l.value)
FROM    t_small s
LEFT JOIN
        t_large l
ON      s.id = l.small

SELECT  s.*, l.*
FROM    t_small s
LEFT JOIN
        t_large l
ON      l.id = 
        (
        SELECT  MIN(id)
        FROM    t_large li
        WHERE   li.small = s.id
        )

Now, imagine this table layout:

t_small

id     value
--     --
1      Small 1
2      Small 2


t_large

id     small_id  value
--     --        --
1      1         1
2      1         4
3      2         9
4      2         16

Could you please post the resultset which you'd like to see as a result of the query?