sub-query question

[Scott Frankel]

How does one embed a sub-query lookup to one table in order to
replace a foreign key id number with it's name in a SELECT on a
second table?

i.e.: given the following two tables, I want to replace the color_id
of 1
with the color_name 'red.' (The SQL to create the two tables follows
below.)

test=# SELECT * from users ;
color_id | name | the_date
----------+------+------------
1 | john | 2004-03-10
3 | jane | 2004-04-12
1 | joe | 2004-05-14
2 | jepe | 2004-06-16
(4 rows)

test=# SELECT * from colors;
color_id | color_name
----------+------------
1 | red
2 | green
3 | blue
(3 rows)
My attempts yield an 'f' which looks suspiciously like a boolean false.
Is there an ordering issue with my sub-query, such that the sub-query
doesn't have enough info to perform its lookup?

Here's my query:

SELECT (
u.color_id = (
SELECT c.color_name
FROM colors c
WHERE color_id = 1)) AS color_name,
u.name, u.the_date
FROM users u
WHERE u.color_id = 1
ORDER BY u.the_date DESC LIMIT 1;

It returns:

color_name | name | the_date
------------+------+------------
f | joe | 2004-05-14
(1 row)
Thanks!
Scott
Here's the SQL to create my test tables:

CREATE TABLE colors (color_id SERIAL PRIMARY KEY, color_name text);
CREATE TABLE users (color_id integer REFERENCES colors, name text,
the_date date);

INSERT INTO colors (color_name) VALUES ('red');
INSERT INTO colors (color_name) VALUES ('green');
INSERT INTO colors (color_name) VALUES ('blue');

INSERT INTO users (color_id, name, the_date) VALUES (1, 'john',
'2004-03-10');
INSERT INTO users (color_id, name, the_date) VALUES (3, 'jane',
'2004-04-12');
INSERT INTO users (color_id, name, the_date) VALUES (1, 'joe',
'2004-05-14');
INSERT INTO users (color_id, name, the_date) VALUES (2, 'jepe',
'2004-06-16');

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

[Franco Bruno Borghesi]

something == otherthing is a boolean expression, you are asking the
database to compare both values, u.color_id is not equal c.color_name,
that's why you get 'f'.

I guess that you want to replace the color_id from users by the
corresponding color_name from colors:

SELECT
c.color_name, u.name, u.the_date
FROM
users u
INNER JOIN colors c ON (u.color_id=c.c olor_id)
WHERE
u.color_id = 1
ORDER BY u.the_date DESC LIMIT 1;
If you were trying to do another thing, I'm sorry, I didn't get it :(

Scott Frankel wrote:

How does one embed a sub-query lookup to one table in order to
replace a foreign key id number with it's name in a SELECT on a
second table?

i.e.: given the following two tables, I want to replace the color_id
of 1
with the color_name 'red.' (The SQL to create the two tables follows
below.)

test=# SELECT * from users ;
color_id | name | the_date
----------+------+------------
1 | john | 2004-03-10
3 | jane | 2004-04-12
1 | joe | 2004-05-14
2 | jepe | 2004-06-16
(4 rows)

test=# SELECT * from colors;
color_id | color_name
----------+------------
1 | red
2 | green
3 | blue
(3 rows)
My attempts yield an 'f' which looks suspiciously like a boolean false.
Is there an ordering issue with my sub-query, such that the sub-query
doesn't have enough info to perform its lookup?

Here's my query:

SELECT (
u.color_id = (
SELECT c.color_name
FROM colors c
WHERE color_id = 1)) AS color_name,
u.name, u.the_date
FROM users u
WHERE u.color_id = 1
ORDER BY u.the_date DESC LIMIT 1;

It returns:

color_name | name | the_date
------------+------+------------
f | joe | 2004-05-14
(1 row)
Thanks!
Scott
Here's the SQL to create my test tables:

CREATE TABLE colors (color_id SERIAL PRIMARY KEY, color_name text);
CREATE TABLE users (color_id integer REFERENCES colors, name text,
the_date date);

INSERT INTO colors (color_name) VALUES ('red');
INSERT INTO colors (color_name) VALUES ('green');
INSERT INTO colors (color_name) VALUES ('blue');

INSERT INTO users (color_id, name, the_date) VALUES (1, 'john',
'2004-03-10');
INSERT INTO users (color_id, name, the_date) VALUES (3, 'jane',
'2004-04-12');
INSERT INTO users (color_id, name, the_date) VALUES (1, 'joe',
'2004-05-14');
INSERT INTO users (color_id, name, the_date) VALUES (2, 'jepe',
'2004-06-16');

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

[Tom Lane]

Scott Frankel <le*****@pacbel l.net> writes:

Here's my query: SELECT (
u.color_id = (
SELECT c.color_name
FROM colors c
WHERE color_id = 1)) AS color_name,
u.name, u.the_date
FROM users u
WHERE u.color_id = 1
ORDER BY u.the_date DESC LIMIT 1;

I think you want

SELECT
(SELECT c.color_name
FROM colors c
WHERE color_id = u.color_id) AS color_name,
u.name, u.the_date
FROM users u
WHERE u.color_id = 1
ORDER BY u.the_date DESC LIMIT 1;

The sub-select can refer to a variable of the outer query, as long
as you are careful to qualify it so it can't be mistaken for a variable
of the sub-select itself.

You could also express this query as a join. If you are pulling a whole
lot of users rows, the join way would probably be more efficient.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddres sHere" to ma*******@postg resql.org)

[Michael Fuhr]

On Fri, Nov 12, 2004 at 09:52:09AM -0800, Scott Frankel wrote:

How does one embed a sub-query lookup to one table in order to
replace a foreign key id number with it's name in a SELECT on a
second table?
You're talking about joins.

http://www.postgresql.org/docs/7.4/s...rial-join.html
http://www.postgresql.org/docs/7.4/s...pressions.html
i.e.: given the following two tables, I want to replace the color_id
of 1
with the color_name 'red.' (The SQL to create the two tables follows
below.)

test=# SELECT * from users ;
color_id | name | the_date
----------+------+------------
1 | john | 2004-03-10
3 | jane | 2004-04-12
1 | joe | 2004-05-14
2 | jepe | 2004-06-16

test=# SELECT * from colors;
color_id | color_name
----------+------------
1 | red
2 | green
3 | blue

There are at least four ways to write the join query you want:

SELECT color_name, name, the_date
FROM users NATURAL JOIN colors;

SELECT color_name, name, the_date
FROM users JOIN colors USING (color_id);

SELECT color_name, name, the_date
FROM users JOIN colors ON colors.color_id = users.color_id;

SELECT color_name, name, the_date
FROM users, colors
WHERE users.color_id = colors.color_id ;

Debate exists about which of the above is "better." I tend to use
the second and third forms because I think they most clearly document
how the tables are joined, and because I think of the WHERE clause
as meaning "...and here are the records I want from all that."

Of course that's just my opinion. I could be wrong.
---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

[Michael Fuhr]

On Fri, Nov 12, 2004 at 11:26:14AM -0700, Michael Fuhr wrote:

There are at least four ways to write the join query you want:

I may have misunderstood what results you're looking for, but the
examples I gave may nevertheless be useful. Sorry if they cause
any confusion.

--
Michael Fuhr
http://www.fuhr.org/~mfuhr/

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postg resql.org

[Scott Frankel]

Cooking with gas once again ;) Thanks for the info on JOINs!
Scott


On Nov 12, 2004, at 9:52 AM, Scott Frankel wrote:

How does one embed a sub-query lookup to one table in order to
replace a foreign key id number with it's name in a SELECT on a
second table?

i.e.: given the following two tables, I want to replace the color_id
of 1
with the color_name 'red.' (The SQL to create the two tables follows
below.)

test=# SELECT * from users ;
color_id | name | the_date
----------+------+------------
1 | john | 2004-03-10
3 | jane | 2004-04-12
1 | joe | 2004-05-14
2 | jepe | 2004-06-16
(4 rows)

test=# SELECT * from colors;
color_id | color_name
----------+------------
1 | red
2 | green
3 | blue
(3 rows)
My attempts yield an 'f' which looks suspiciously like a boolean false.
Is there an ordering issue with my sub-query, such that the sub-query
doesn't have enough info to perform its lookup?

Here's my query:

SELECT (
u.color_id = (
SELECT c.color_name
FROM colors c
WHERE color_id = 1)) AS color_name,
u.name, u.the_date
FROM users u
WHERE u.color_id = 1
ORDER BY u.the_date DESC LIMIT 1;

It returns:

color_name | name | the_date
------------+------+------------
f | joe | 2004-05-14
(1 row)
Thanks!
Scott
Here's the SQL to create my test tables:

CREATE TABLE colors (color_id SERIAL PRIMARY KEY, color_name text);
CREATE TABLE users (color_id integer REFERENCES colors, name text,
the_date date);

INSERT INTO colors (color_name) VALUES ('red');
INSERT INTO colors (color_name) VALUES ('green');
INSERT INTO colors (color_name) VALUES ('blue');

INSERT INTO users (color_id, name, the_date) VALUES (1, 'john',
'2004-03-10');
INSERT INTO users (color_id, name, the_date) VALUES (3, 'jane',
'2004-04-12');
INSERT INTO users (color_id, name, the_date) VALUES (1, 'joe',
'2004-05-14');
INSERT INTO users (color_id, name, the_date) VALUES (2, 'jepe',
'2004-06-16');

---------------------------(end of
broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html