如果行存在，如何交叉引用两个sql表然后显示一列的输出？

Question

Please provide some insight, as I am about 99.9% ignorant about working with sql. I have put all columns in parentheses. I want to:

1. Grab a number (id) from the v14_users table (for the user whose profile page is currently being viewed).

2. Find the same number (uid) column in the v14_profileurl_urls table

Then, depending on the result of step 2 either:

3a. If (uid) column found, display/print (name) column for same row of v14_profileurl_urls table

or

3b. If (uid) column is not found, display nothing.

The result of 3a would be displayed with something like:

<?php echo $profileurl_urls->name; ?>

Thanks for any help! -Moni

Answer 1

I'm making some assumptions about your database schema but you can use the following if my assumptions are correct:

SELECT COALESCE(profile.name, 'No Profile') AS Name
FROM v14_users AS users LEFT JOIN
     v14_profileurl_urls AS profile ON 
         users.id = profile.uid

You can replace the string 'No Profile' with the empty string '' if you really don't want to return anything.

A coalesce returns the first non-NULL value in the list, or NULL if there are no non-NULL values. Since I've hardwired in the string 'No Profile' as the last item in the list if there is not profile record or the profile name is null then 'No Profile' will always appear.