Jump to content


Photo

selection logic with lookup table


  • Please log in to reply
1 reply to this topic

#1 erewash

erewash
  • Members
  • PipPip
  • Member
  • 11 posts

Posted 22 March 2006 - 09:34 AM

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.

$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]

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.

#2 erewash

erewash
  • Members
  • PipPip
  • Member
  • 11 posts

Posted 22 March 2006 - 10:49 AM

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!




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users