Skip site navigation (1) Skip section navigation (2)

Re: SQL Problem

From: <operationsengineer1(at)yahoo(dot)com>
To: pgsql-novice(at)postgresql(dot)org
Cc: pgsql-novice(at)postgresql(dot)org
Subject: Re: SQL Problem
Date: 2005-06-30 16:26:21
Message-ID: 20050630162621.69632.qmail@web33312.mail.mud.yahoo.com (view raw or flat)
Thread:
Lists: 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(at)mail(dot)nih(dot)gov> wrote:

> 
> On Jun 28, 2005, at 6:41 PM,
> <operationsengineer1(at)yahoo(dot)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 

In response to

pgsql-novice by date

Next:From: O'Toole, Adam TroyDate: 2005-06-30 16:33:27
Subject: getting Salt value from server
Previous:From: Michael GlaesemannDate: 2005-06-30 15:52:23
Subject: Re: Aliased table names ...oddity?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group