Jump to content

Archived

This topic is now archived and is closed to further replies.

erewash

selection logic with lookup table

Recommended Posts

I'm sure this is really basic and it is just a matter of getting the JOIN right, but my brain is addled by trying to work out the logic;

I need to run a selection, and read data into an array, from two simple tables, plus one lookup table. So, each 'people' record will have one or more addresses, and each record _may_ have one or many 'prog' records.

[code]
$sql = "SELECT pe.people_id, pe.loginname, pe.password, ..... ,
                    addr.institution, addr.department, ..... ,
                    lk.pprog_id
            FROM (my_people AS pe LEFT JOIN my_address AS addr ON pe.people_id=addr.people_id)
                LEFT JOIN my_people_prog AS lk ON pe.people_id=lk.ppeople_id
                  WHERE surname <> '' ";
                                      [plus various other selection criteria posted from form]
[/code]

The first part of the selection - against people and address - works fine, but introducing the 'prog' gives me one row for each 'prog' it finds that matches, which is embarrassing.... could someone please advise??

Next scary step will be to enable the selection on the basis of the descriptor, rather than the code, for the 'prog' record - I guess this is best achieved through clever use of drop-downs on the selection form....

Thank you very much.

Share this post


Link to post
Share on other sites
Okay, not meaning to waste peoples' time - I find that ORDER BY at the end of the select seems to do the job for me now - had been putting it in the wrong place! A case of tying myself in knots... but is there a neater way to achieve what I am after?

Thank you!

Share this post


Link to post
Share on other sites

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.