Re: SQL Problem - Mailing list pgsql-novice

sean,

thank you for the help.  your suggestion got me really
close, but i had to make a slight modification to
prevent dupe rows - one for each employee.  i needed
to put employee_id in my select and use it to equate
to assembly_notes_contact_id.

here is the final code...

SELECT notes.assembly_notes_id, notes.assembly_notes,
notes.assembly_notes_contact_id, emp.employee_id,
emp.first_name, emp.last_name

FROM t_product AS prod, t_link_product_assembly_notes
AS link, t_assembly_notes AS notes, t_employee as emp

WHERE link.assembly_notes_id = notes.assembly_notes_id
AND link.product_id = prod.product_id
AND notes.assembly_notes_contact_id = emp.employee_id
AND link.product_id = $product

i also tried to optimize the query by putting the
quickest where clause first, second quickest second,
etc...  i realize this can be dynamic as more rows may
change the total query/return times.

thanks again for the guidance.


--- Sean Davis <sdavis2@mail.nih.gov> wrote:

>
> On Jun 28, 2005, at 6:41 PM,
> <operationsengineer1@yahoo.com> wrote:
>
> > i'm not sure if this is the best place to ask (if
> > there is a better mailing list, please let me
> know)...
> >
> > i have a sql problem.  i have the following
> query...
> >
> > $sql_notes = "SELECT notes.assembly_notes,
> > notes.assembly_notes_contact_id " .
> >
> > "FROM t_product AS prod,
> t_link_product_assembly_notes
> > AS link, t_assembly_notes AS notes " .
> >
> > "WHERE link.product_id = prod.product_id " .
> > "AND link.assembly_notes_id =
> notes.assembly_notes_id
> > ".
> > "AND link.product_id = " . $product_id;
> >
> > it works great except for one problem.  i want the
> > assembly_notes_contact (the actual name) instead
> of
> > the assembly_notes_contact_id.  the result of this
> > query is stored in an array and then i iterate
> through
> > the array before displaying the data.
> >
> > assembly_notes_contact_id is a foreign key to the
> > employee table's primary key.  i want to be able
> to
> > query the db and get the employee name (instead of
> his
> > id) into the recordset array.  the employee name
> is
> > the same as the assembly_notes_contact.
> >
> > is this possible?  if so, how?
>
> You just need to do another join to the employee
> table.
>
> $sql_notes = "SELECT notes.assembly_notes,
> notes.assembly_notes_contact_id,employee.name " .
>
> "FROM t_product AS prod,
> t_link_product_assembly_notes
> AS link, t_assembly_notes AS notes,t_employee as
> employee " .
>
> "WHERE link.product_id = prod.product_id " .
> "AND link.assembly_notes_id =
> notes.assembly_notes_id".
> "AND
>
notes.assembly_notes_contact_id=employee.employee_id".
> "AND link.product_id = " . $product_id;
>
> You will, of course, have to change the table names,
> etc. to match your
> table descriptions.
>
> Sean
>
>


__________________________________________________
Do You Yahoo!?
Tired of spam?  Yahoo! Mail has the best spam protection around
http://mail.yahoo.com