From: | yaubi(at)yaubi(dot)Com (Yoann) |
---|---|
To: | pgsql-sql(at)postgresql(dot)org |
Subject: | Re: How do I extract ONE particular field, when multiple table contain the same field name? |
Date: | 2001-09-12 13:47:01 |
Message-ID: | 70c78e1b.0109120547.61c0aa19@posting.google.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
olle(dot)wijk(at)systecon(dot)se (Olle Wijk) wrote in message news:<6baa8767(dot)0109120106(dot)67f94cf5(at)posting(dot)google(dot)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
From | Date | Subject | |
---|---|---|---|
Next Message | victor | 2001-09-12 14:05:09 | cache lookup failed |
Previous Message | Arne Weiner | 2001-09-12 12:54:23 | Re: How do I extract ONE particular field, when multiple table contain |