I have two MySQL tables, and in a query, I would like to replace the
value of a column from one table with that of a column from another
table. I can't figure out whether this is a join (or what type), or
something else.

For example, if you have two tables:
people: id | name
nicknames: id | nickname

.... and a pseudo-query:
select people.id,
people.name {but instead, return correlating value of
nicknames.nickname},
from people, nicknames
where people.id = nicknames.id;

How do you get the {} stuff?

John DeStefano wrote:
> I have two MySQL tables, and in a query, I would like to replace the
> value of a column from one table with that of a column from another
> table. I can't figure out whether this is a join (or what type), or
> something else.
>
> For example, if you have two tables:
> people: id | name
> nicknames: id | nickname
>
> ... and a pseudo-query:
> select people.id,
> people.name {but instead, return correlating value of
> nicknames.nickname},
> from people, nicknames
> where people.id = nicknames.id;
>
> How do you get the {} stuff?
>

select
people.id,
people.name,
nicknames.nickname

from
people
join nicknames
on
people.id = nicknames.id

or if you'd like to stick to what you already have:

select
people.id,
people.name,
nicknames.nickname

from
people,
nicknames
where people.id = nicknames.id

--
lark -- hamzee@sbcdeglobalspam.net
To reply to me directly, delete "despam".

Yes, that works perfectly. Thank you!

But what if you need more than one column "replacement" from the same
two tables? If I add some more columns to "people":
mysql> select * from people;
+----+---------------+-------------+--------------+-------------+
| id | name | best_friend | worst_friend | worst_enemy |
+----+---------------+-------------+--------------+-------------+
| 1 | James Robert | 2 | 3 | 4 |
| 2 | Lawrence | 1 | 3 | 4 |
| 3 | Odorless | 2 | 4 | 1 |
| 4 | William Small | 3 | 1 | 2 |
+----+---------------+-------------+--------------+-------------+
4 rows in set (0.00 sec)

mysql> select * from nicknames;
+----+----------+
| id | nickname |
+----+----------+
| 1 | Jim Bob |
| 2 | Screech |
| 3 | Stinky |
| 4 | Tiny |
+----+----------+
4 rows in set (0.00 sec)


If I try to set more than one friend/enemy column equal to
nicknames.id, in order to display the nickname value instead of the ID
number, the query fails. Can you do multiple joins to do this?

John DeStefano wrote:
> Yes, that works perfectly. Thank you!
>
> But what if you need more than one column "replacement" from the same
> two tables? If I add some more columns to "people":
> mysql> select * from people;
> +----+---------------+-------------+--------------+-------------+
>> id | name | best_friend | worst_friend | worst_enemy |
> +----+---------------+-------------+--------------+-------------+
>> 1 | James Robert | 2 | 3 | 4 |
>> 2 | Lawrence | 1 | 3 | 4 |
>> 3 | Odorless | 2 | 4 | 1 |
>> 4 | William Small | 3 | 1 | 2 |
> +----+---------------+-------------+--------------+-------------+
> 4 rows in set (0.00 sec)
>
> mysql> select * from nicknames;
> +----+----------+
>> id | nickname |
> +----+----------+
>> 1 | Jim Bob |
>> 2 | Screech |
>> 3 | Stinky |
>> 4 | Tiny |
> +----+----------+
> 4 rows in set (0.00 sec)
>
>
> If I try to set more than one friend/enemy column equal to
> nicknames.id, in order to display the nickname value instead of the ID
> number, the query fails. Can you do multiple joins to do this?

You are not setting a "column" equal to nickname.id, you are joining 2 rows
where the id's match.

Try this to see what I mean:
SELECT
*
FROM `people`
JOIN `nicknames` USING(`id`)

On May 4, 6:11 pm, "Paul Lautman" <paul.laut...@btinternet.com> wrote:
> John DeStefano wrote:
> > Yes, that works perfectly. Thank you!
>
> > But what if you need more than one column "replacement" from the same
> > two tables? If I add some more columns to "people":
> > mysql> select * from people;
> > +----+---------------+-------------+--------------+-------------+
> >> id | name | best_friend | worst_friend | worst_enemy |
> > +----+---------------+-------------+--------------+-------------+
> >> 1 | James Robert | 2 | 3 | 4 |
> >> 2 | Lawrence | 1 | 3 | 4 |
> >> 3 | Odorless | 2 | 4 | 1 |
> >> 4 | William Small | 3 | 1 | 2 |
> > +----+---------------+-------------+--------------+-------------+
> > 4 rows in set (0.00 sec)
>
> > mysql> select * from nicknames;
> > +----+----------+
> >> id | nickname |
> > +----+----------+
> >> 1 | Jim Bob |
> >> 2 | Screech |
> >> 3 | Stinky |
> >> 4 | Tiny |
> > +----+----------+
> > 4 rows in set (0.00 sec)
>
> > If I try to set more than one friend/enemy column equal to
> > nicknames.id, in order to display the nickname value instead of the ID
> > number, the query fails. Can you do multiple joins to do this?
>
> You are not setting a "column" equal to nickname.id, you are joining 2 rows
> where the id's match.
>
> Try this to see what I mean:
> SELECT
> *
> FROM `people`
> JOIN `nicknames` USING(`id`)

I think that's definitely a step in the right direction. In these
results:

select * from people join nicknames using (`id`);
+----+---------------+-------------+--------------+-------------+----
+----------+
| id | name | best_friend | worst_friend | worst_enemy | id |
nickname |
+----+---------------+-------------+--------------+-------------+----
+----------+
| 1 | James Robert | 2 | 3 | 4 | 1 |
Jim Bob |
| 2 | Lawrence | 1 | 3 | 4 | 2 |
Screech |
| 3 | Odorless | 2 | 4 | 1 | 3 |
Stinky |
| 4 | William Small | 3 | 1 | 2 | 4 |
Tiny |
+----+---------------+-------------+--------------+-------------+----
+----------+

.... instead of getting the nickname of the names in the "name" column
in the "people" table, how would you return the "nickname" values from
"nicknames" of the three columns in the middle, which are returning
their "id" values from "people"?

John DeStefano wrote:
> On May 4, 6:11 pm, "Paul Lautman" <paul.laut...@btinternet.com> wrote:
>> John DeStefano wrote:
>>> Yes, that works perfectly. Thank you!
>>> But what if you need more than one column "replacement" from the same
>>> two tables? If I add some more columns to "people":
>>> mysql> select * from people;
>>> +----+---------------+-------------+--------------+-------------+
>>>> id | name | best_friend | worst_friend | worst_enemy |
>>> +----+---------------+-------------+--------------+-------------+
>>>> 1 | James Robert | 2 | 3 | 4 |
>>>> 2 | Lawrence | 1 | 3 | 4 |
>>>> 3 | Odorless | 2 | 4 | 1 |
>>>> 4 | William Small | 3 | 1 | 2 |
>>> +----+---------------+-------------+--------------+-------------+
>>> 4 rows in set (0.00 sec)
>>> mysql> select * from nicknames;
>>> +----+----------+
>>>> id | nickname |
>>> +----+----------+
>>>> 1 | Jim Bob |
>>>> 2 | Screech |
>>>> 3 | Stinky |
>>>> 4 | Tiny |
>>> +----+----------+
>>> 4 rows in set (0.00 sec)
>>> If I try to set more than one friend/enemy column equal to
>>> nicknames.id, in order to display the nickname value instead of the ID
>>> number, the query fails. Can you do multiple joins to do this?
>> You are not setting a "column" equal to nickname.id, you are joining 2 rows
>> where the id's match.
>>
>> Try this to see what I mean:
>> SELECT
>> *
>> FROM `people`
>> JOIN `nicknames` USING(`id`)
>
> I think that's definitely a step in the right direction. In these
> results:
>
> select * from people join nicknames using (`id`);
> +----+---------------+-------------+--------------+-------------+----
> +----------+
> | id | name | best_friend | worst_friend | worst_enemy | id |
> nickname |
> +----+---------------+-------------+--------------+-------------+----
> +----------+
> | 1 | James Robert | 2 | 3 | 4 | 1 |
> Jim Bob |
> | 2 | Lawrence | 1 | 3 | 4 | 2 |
> Screech |
> | 3 | Odorless | 2 | 4 | 1 | 3 |
> Stinky |
> | 4 | William Small | 3 | 1 | 2 | 4 |
> Tiny |
> +----+---------------+-------------+--------------+-------------+----
> +----------+
>
> ... instead of getting the nickname of the names in the "name" column
> in the "people" table, how would you return the "nickname" values from
> "nicknames" of the three columns in the middle, which are returning
> their "id" values from "people"?
>

well, you can replace each one of those fields with a select of its own
such as this:

select people.id, people.name,nickname, (select nickname from nicknames
where id =best_friend) as bestfriend from `people` join `nicknames` on
`people`.`id` = `nicknames`.`id`

add worst friend and enemy as appropraite after the best friend field.

--
lark -- hamzee@sbcdeglobalspam.net
To reply to me directly, delete "despam".

On May 7, 9:58 am, lark <ham...@sbcdeglobalspam.net> wrote:
> well, you can replace each one of those fields with a select of its own
> such as this:
>
> select people.id, people.name,nickname, (select nickname from nicknames
> where id =best_friend) as bestfriend from `people` join `nicknames` on
> `people`.`id` = `nicknames`.`id`
>
> add worst friend and enemy as appropraite after the best friend field.

YES... that works! Thank you very much, lark and Paul!

On 7 May, 15:13, John DeStefano <john.destef...@gmail.com> wrote:
> On May 7, 9:58 am, lark <ham...@sbcdeglobalspam.net> wrote:
>
> > well, you can replace each one of those fields with a select of its own
> > such as this:
>
> > select people.id, people.name,nickname, (select nickname from nicknames
> > where id =best_friend) as bestfriend from `people` join `nicknames` on
> > `people`.`id` = `nicknames`.`id`
>
> > add worst friend and enemy as appropraite after the best friend field.
>
> YES... that works! Thank you very much, lark and Paul!

But you really don't want to use Lark's idea. Practice JOINs by simply
using multiple JOINs, it's much tidier.

SELECT
`p`.`id`,
`p`.`name`,
`n1`.`nickname`,
`n2`.`nickname` `best_friend`,
`n3`.`nickname` `worst_friend`,
`n4`.`nickname` `worst_enemy`,
FROM `people` `p`
JOIN `nicknames` `n1` on `p`.`id` = `n1`.`id`
JOIN `nicknames` `n2` on `p`.`best_friend` = `n2`.`id`
JOIN `nicknames` `n3` on `p`.`worst_friend` = `n3`.`id`
JOIN `nicknames` `n4` on `p`.`worst_enemy` = `n4`.`id`

But surely your worst enemy is in fact your best friend!