Thread: How do I extract ONE particular field, when multiple table contain the same field name?
How do I extract ONE particular field, when multiple table contain the same field name?
From
olle.wijk@systecon.se (Olle Wijk)
Date:
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
Re: How do I extract ONE particular field, when multiple table contain the same field name?
From
yaubi@yaubi.Com (Yoann)
Date:
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 Wherexi.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 WhereXItem.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 nextline 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 catWHERE xi.system = 1 AND xi.category_ID= cat.ID; good luck Yoann
Re: How do I extract ONE particular field, when multiple table contain the same field name?
From
"Jeff Eckermann"
Date:
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 > >
What you want to know is probably this: Select XID, xi.DENOM, PRICE, FRT, CTID From XItem xi, Category c Wherexi.System=1 and xi.Category=c.Index Regards, Patrik Kudo
Re: How do I extract ONE particular field, when multiple table contain the same field name?
From
Christof Glaser
Date:
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