2

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).

Zombies
  • 25,039
  • 43
  • 140
  • 225
  • Which part of the relationship does the larger table contain: the "one" part or the "many" part? – Quassnoi Feb 18 '10 at 16:44
  • Could you please post some sample data and the resultset you want to get? – Quassnoi Feb 18 '10 at 16:45
  • The larger many table would be larger... I want to do a query which is really more focused on the one sided table, yet I want to pull in some results from the larger detail (bad DB design from awhile ago) without having a result set that is as large as the many sided table. In part, this is kind of like sql optimization but on a much more basic level. – Zombies Feb 18 '10 at 16:48
  • I wrote a sample table layout in my answer, could you please look at it and say: which resultset the query you want should return? – Quassnoi Feb 18 '10 at 16:59

2 Answers2

2

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

or

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?

Quassnoi
  • 413,100
  • 91
  • 616
  • 614
0

Isn't this just an left join on the key?

select * from small_table s left join large_table l on s.id = l.small_table_id

Every record in small_table, but only the relevant rows in large_table.

I'm missing something. Please elaborate or provide scrubbed sample data.

Broam
  • 4,602
  • 1
  • 23
  • 38
  • The many sided table doesn't have a PK, the FK (which is the PK in the smaller, one sided table) isn't unique in the many sided table.. sorry, brb editing. – Zombies Feb 18 '10 at 16:50