pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedHow do I extract ONE particular field, when multiple table contain the same field name?
6+ messages / 6 participants
[nested] [flat]
* How do I extract ONE particular field, when multiple table contain the same field name?
@ 2001-09-12 09:06 Olle Wijk <olle.wijk@systecon.se>
2001-09-12 12:54 ` Re: How do I extract ONE particular field, when multiple table contain Arne Weiner <aswr@gmx.de>
2001-09-12 13:47 ` Re: How do I extract ONE particular field, when multiple table contain the same field name? Yoann <yaubi@yaubi.Com>
2001-09-14 20:34 ` Re: How do I extract ONE particular field, when multiple table Patrik Kudo <kudo@partitur.se>
2001-09-14 21:05 ` Re: How do I extract ONE particular field, when multiple table contain the same field name? Christof Glaser <gcg@gl.aser.de>
2001-09-14 21:35 ` Re: How do I extract ONE particular field, when multiple table contain the same field name? Jeff Eckermann <jeckermann@verio.net>
0 siblings, 5 replies; 6+ messages in thread
From: olle.wijk@systecon.se @ 2001-09-12 09:06 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: How do I extract ONE particular field, when multiple table contain
2001-09-12 09:06 How do I extract ONE particular field, when multiple table contain the same field name? Olle Wijk <olle.wijk@systecon.se>
@ 2001-09-12 12:54 ` Arne Weiner <aswr@gmx.de>
4 siblings, 0 replies; 6+ messages in thread
From: Arne Weiner @ 2001-09-12 12:54 UTC (permalink / raw)
To: pgsql-sql
If you refere to more than one table in a query, you have to specify
which column to which table belongs. So your query must be
Select XID, xi.DENOM, PRICE, FRT, CTID From XItem xi, Category c Where
xi.System=1 and xi.Category=c.Index
Arne.
Olle Wijk wrote:
>
> 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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: How do I extract ONE particular field, when multiple table contain the same field name?
2001-09-12 09:06 How do I extract ONE particular field, when multiple table contain the same field name? Olle Wijk <olle.wijk@systecon.se>
@ 2001-09-12 13:47 ` Yoann <yaubi@yaubi.Com>
4 siblings, 0 replies; 6+ messages in thread
From: yaubi@yaubi.Com @ 2001-09-12 13:47 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: How do I extract ONE particular field, when multiple table
2001-09-12 09:06 How do I extract ONE particular field, when multiple table contain the same field name? Olle Wijk <olle.wijk@systecon.se>
@ 2001-09-14 20:34 ` Patrik Kudo <kudo@partitur.se>
4 siblings, 0 replies; 6+ messages in thread
From: Patrik Kudo @ 2001-09-14 20:34 UTC (permalink / raw)
To: Olle Wijk <olle.wijk@systecon.se>; +Cc: pgsql-sql
What you want to know is probably this:
Select XID, xi.DENOM, PRICE, FRT, CTID From XItem xi, Category c Where
xi.System=1 and xi.Category=c.Index
Regards,
Patrik Kudo
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: How do I extract ONE particular field, when multiple table contain the same field name?
2001-09-12 09:06 How do I extract ONE particular field, when multiple table contain the same field name? Olle Wijk <olle.wijk@systecon.se>
@ 2001-09-14 21:05 ` Christof Glaser <gcg@gl.aser.de>
4 siblings, 0 replies; 6+ messages in thread
From: Christof Glaser @ 2001-09-14 21:05 UTC (permalink / raw)
To: olle.wijk@systecon.se; pgsql-sql
On Wednesday, 12. September 2001 11:06, Olle Wijk wrote:
> 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'
Just write: table.field or table-alias.field instead of just 'field'
(like you did already in the WHERE clause):
Select XID, xi.DENOM, PRICE, FRT, CTID
From XItem xi, Category c
Where xi.System=1 and xi.Category=c.Index
Christof
--
gl.aser . software engineering . internet service
http://gl.aser.de/ . Planckstraße 7 . D-39104 Magdeburg
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: How do I extract ONE particular field, when multiple table contain the same field name?
2001-09-12 09:06 How do I extract ONE particular field, when multiple table contain the same field name? Olle Wijk <olle.wijk@systecon.se>
@ 2001-09-14 21:35 ` Jeff Eckermann <jeckermann@verio.net>
4 siblings, 0 replies; 6+ messages in thread
From: Jeff Eckermann @ 2001-09-14 21:35 UTC (permalink / raw)
To: Olle Wijk <olle.wijk@systecon.se>; pgsql-sql
You need to qualify "DENOM" with the table name: just write "xi.DENOM".
I find this to be good general practice when selecting from more than one
table.
----- Original Message -----
From: "Olle Wijk" <olle.wijk@systecon.se>
To: <pgsql-sql@postgresql.org>
Sent: Wednesday, September 12, 2001 4:06 AM
Subject: How do I extract ONE particular field, when multiple table contain
the same field name?
> 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
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
>
>
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2001-09-14 21:35 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2001-09-12 09:06 How do I extract ONE particular field, when multiple table contain the same field name? Olle Wijk <olle.wijk@systecon.se>
2001-09-12 12:54 ` Re: How do I extract ONE particular field, when multiple table contain Arne Weiner <aswr@gmx.de>
2001-09-12 13:47 ` Yoann <yaubi@yaubi.Com>
2001-09-14 20:34 ` Re: How do I extract ONE particular field, when multiple table Patrik Kudo <kudo@partitur.se>
2001-09-14 21:05 ` Christof Glaser <gcg@gl.aser.de>
2001-09-14 21:35 ` Jeff Eckermann <jeckermann@verio.net>
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