Is it possible to get another row value through a foreign key?

Question

Recently, I've been working on a simple recording archival system, done with PHP and MySQL.

Basically, you register and login to the website and you can add, edit and delete recordings (aka metadata). Here's the current database situation:

My problem is the following: when I populate a table on the website and call "user" from "assets", it returns me a number from "usersId". I want "users" to return "usersUid" instead, but staying connected to "usersId". Is that even possible or should I just connect "user" directly "usersUid"?

JOIN the 2 tables and get the users `usersUid` from the `users` table — RiggsFolly, Jul 05 '21 at 12:38
Yeah, that's what I thought. INNER JOIN should the best choice for this situation, right? — O Tal Antiquado, Jul 05 '21 at 12:41
`select u.usersName, u.usersUid, .... a.* FROM assets a LEFT JOIN users u ON u.usersId = a.user` — RiggsFolly, Jul 05 '21 at 12:44
Thanks, Riggs, I'll try that. I just have one question: instead of "u.usersName, u.usersUid, ...." (I'm presuming the ellipsis represent the other rows from the users table) could I write "users.*"? — O Tal Antiquado, Jul 05 '21 at 12:49

score 0 · Answer 1 · edited Jul 05 '21 at 12:49

0

Is this a raw query or you are using some ORM, if that's a raw query then simply a join and select would work fine. like this:

select usersUid from assets as a inner join user as u on u.usersId = a.user

and if you are using ORM of any sort you'll find some alternative to implement the above join.

NOTE: Please change the join according to the need. Below is a reference for the same.

Joins in MySQL

edited Jul 05 '21 at 12:49

Dharman

30,962
25
85
135

answered Jul 05 '21 at 12:43

cupid22

161
7

It's a raw query. I'll try Riggs' solution first, but thank you anyways! – O Tal Antiquado Jul 05 '21 at 12:46
Sure, no worries. – cupid22 Jul 05 '21 at 12:47

Is it possible to get another row value through a foreign key?

1 Answers1