query syntax to replace column value from another table? - MySQL

[John]

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?

[lark]

John DeStefano wrote: 

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 -- net
To reply to me directly, delete "despam".

[John]

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?

[Paul]

John DeStefano wrote: 
> +----+---------------+-------------+--------------+-------------+ 
> +----+---------------+-------------+--------------+-------------+
> 4 rows in set (0.00 sec)
>
> mysql> select * from nicknames;
> +----+----------+ 
> +----+----------+ 
> +----+----------+
> 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?[/ref]

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`)

[John]

On May 4, 6:11 pm, "Paul Lautman" <com> wrote: 
> 
> > +----+---------------+-------------+--------------+-------------+ 
> > +----+---------------+-------------+--------------+-------------+
> > 4 rows in set (0.00 sec)[/ref]
> 
> > +----+----------+ 
> > +----+----------+
> > 4 rows in set (0.00 sec)[/ref]
> 
>
> 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`)[/ref]

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"?

[lark]

John DeStefano wrote: 
>> 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`)[/ref]
>
> 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"?
>[/ref]

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 -- net
To reply to me directly, delete "despam".

[John]

On May 7, 9:58 am, lark <net> wrote: 

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

[Captain]

On 7 May, 15:13, John DeStefano <com> wrote: 
> 
> 
>
> YES... that works! Thank you very much, lark and Paul![/ref]

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!