Nested SQL?

[blyxx86]

Good Morning,

I'm attempting to write a single SQL statement that will accomplish the following...

[code=txt]
table_a
PK INT table_a_id
VARCHAR ref_num

table_b
PK INT table_b_id
FK INT table_a_id
DATETIME insert_date
VARCHAR note
[/code]

This is a sample of a single row I want to pull from the data...

a.ref_num   b.note
0001          Note1
                 Note2
                 Note3

The major thing I want to accomplish is having a.ref_num appear only once and loop through the notes to appear as a single row.

I currently am using two arrays within PHP to handle the construction of this, but I feel like it is losing efficiency if it can be done within the SQL syntax.

Any help would be appreciated.

Thanks!

[r035198x]

No it's best done using PHP. If you think the way you constructed your PHP is not efficient then post your PHP code in the PHP forum so the PHP experts can have a look.

[Atli]

Yea, I would agree with r0.

You *could* probably do this using various tricks, but it would certainly not improve the efficiency of the application.

The simplest, and usually most efficient, ways to get these sort of datasets would be to either:

1. Get a list of IDs from table a and then get a list of notes from table b individually for each ID.
   This would probably be more efficient if you have large datasets, where each ID has a large amount of notes.
2. Get the entire list, where each note from table b is accompanied with the ID from table a.
   This would be better for smaller datasets, or in the case you have a lot of IDs with only a few notes each.

Either way, the actual sorting and the logic would be done in PHP.

[blyxx86]

Thank you both for the answers.

I will post my code in the PHP section to see if it can be improved.