pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: yaubi@yaubi.Com
To: pgsql-sql@postgresql.org
Subject: Re: How do I extract ONE particular field, when multiple table contain the same field name?
Date: 12 Sep 2001 06:47:01 -0700
Message-ID: <70c78e1b.0109120547.61c0aa19@posting.google.com> (raw)
References: <6baa8767.0109120106.67f94cf5@posting.google.com>

olle.wijk@systecon.se (Olle Wijk) wrote in message news:<6baa8767.0109120106.67f94cf5@posting.google.com>...
> Hi,
> 
> I am pretty new att using SQL-quires, could anyone help me with this
> one:
> 
> I want to do the following SQL-query:
> 
> Select XID, DENOM, PRICE, FRT, CTID From XItem xi, Category c Where
> xi.System=1 and xi.Category=c.Index
> 
> the problem is that the field 'DENOM' is present in both table 'XItem'
> and
> 'Category' (it is a text description field where you explain whatever
> you
> want). Therefore I get the following complaint when I run the query:
>  
> Error msg: "The specified fiel 'DENOM' could refer to more than one
> table listed
> in the FROM clause of your SQL-statement"
> 
> The DENOM-field I actually want is the one belonging to the
> XItem-table.
> I would most appreciate if someone could give me a hint how to alter
> the
> SELECT-statement above so that it does what I want.
> 
> Regards
> 
> /olw


When joinning tables, as you did, it's hardly recommended using
aliases, as you did too. (XItem <=> xi and Category <=> c). These
aliases can be used in all the SELECT statement including WHERE
clause, as you did again :), and SELECT clause. So, to refer to the
DENOM field from the table XItem, you should write :
xi.DENOM instead of DENOM alone. Your SELECT statement will be :

 Select XID, xi.DENOM, PRICE, FRT, CTID From XItem xi, Category c
Where
 xi.System=1 and xi.Category=c.Index

Notice than if you don't want to use aliases (what a wrong idea !),
you can write like the following :

 Select XID, XItem.DENOM, PRICE, FRT, CTID From XItem, Category Where
 XItem.System=1 and XItem.Category=Category.Index

Just some remarks about writing SQL statement.
it's preferabled (in my point of view) to :
 - write key words (like SELECT, FROM, WHERE, ...) in upper case
 - go to the next line when you change of key word
 - always use aliases
 - keep always the same alias for the same table in all the queries
 - write the fields in lower case
 - write the first lettre of a table name in upper, the rest in lower
case
 - use a "_ID" suffixe to the field name when it correspond to the
primary key of another table
 - name "ID" the primary key of a table

I would write your sql statement like :

 SELECT xi.ID, xi.denom, xi.price, xi.frt, cat.ID 
 FROM XItem AS xi, Category AS cat
 WHERE xi.system = 1 AND xi.category_ID = cat.ID;

good luck
Yoann



view thread (6+ messages)  latest in thread

Message-ID: <70c78e1b.0109120547.61c0aa19@posting.google.com>
Permalink:  ../70c78e1b.0109120547.61c0aa19@posting.google.com/
Also on:    postgresql.org/message-id/70c78e1b.0109120547.61c0aa19@posting.google.com

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-sql@postgresql.org
  Cc: yaubi@yaubi.Com
  Subject: Re: How do I extract ONE particular field, when multiple table contain the same field name?
  In-Reply-To: <70c78e1b.0109120547.61c0aa19@posting.google.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox