Is there a way that I can select information from multiple rows in a table for another table?

Douglas_C

Hi community,

I have two tables and they have the information as follows:

table A (people):

- id
- name
- phone

table B (rooms):

- id
- room name
- person ID 1 (people.id)
- person ID 2 (people.id)

I would like to show the id, name and phone for both people in datatable for table B and also allow users to choose people in Editor via a dropdown list. I first tried to left join table B with table A twice with rooms.person ID 1 = people.id and rooms.personID 2 = people.id but it gave me an error message. Is there a way to do this?

allan

A leftJoin with an alias would be the way to do it - something like (I'm assuming PHP, but our .NET and Node.js libs would support the same thing with their own syntax):

Editor::inst( $db, 'rooms' )
    ->field(
        Field::inst( 'rooms.name' ),
        Field::inst( 'p1.name' ),
        Field::inst( 'p2.name' )
    )
    ->leftJoin( 'people as p1', 'rooms.personId1', '=', 'p1.id' )
    ->leftJoin( 'people as p2', 'rooms.personId2', '=', 'p2.id' )

You'd probably want to add Options and stuff to the fields, but that's the basic idea.

Allan

Douglas_C

Thanks, Allan, indeed I am using PHP and that solves the issue.