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

Re: SQL Problem

From: Sean Davis <sdavis2(at)mail(dot)nih(dot)gov>
To: <operationsengineer1(at)yahoo(dot)com>
Cc: pgsql-novice(at)postgresql(dot)org
Subject: Re: SQL Problem
Date: 2005-06-28 22:53:23
Message-ID: 636e095367a4e01a8a57e3d8adebcd13@mail.nih.gov (view raw or flat)
Thread:
Lists: pgsql-novice
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


In response to

  • SQL Problem at 2005-06-28 22:41:48 from operationsengineer1

Responses

pgsql-novice by date

Next:From: Ramon OrticioDate: 2005-06-29 08:17:45
Subject: posgres file
Previous:From: operationsengineer1Date: 2005-06-28 22:41:48
Subject: SQL Problem

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