如何仅显示与另一个表中的值匹配的行

问题描述：

How would it work in something like this? I've even tried using "AND tb1.name !=", but it didn't work.

$area = 0;
$stmt = $dbh->prepare('SELECT * FROM charinfo WHERE current_area != :area ORDER BY current_area');
$stmt->execute(array('area' => $area));
$result = $stmt->fetchAll();
$place = 1;

I run a game server, and on the website I have a Top 30 leaderboard which is working wonders (found the code directly off another topic here), the issue I'm having is not being able to use the JOIN function that everybody is suggesting in order to prevent the ADMIN's characters from being listed as well.

Here's the code that I have on my website right now, it shows the rank number 1-30, character name and the level in a table. Here's it working on my website

<?php
require("srvcs/config.php");
$stmt = $dbh->prepare('SELECT * FROM chars ORDER BY CAST(experience AS UNSIGNED ) DESC LIMIT 30;');
$stmt->execute();
$result = $stmt->fetchAll();
$place = 1;

echo '<table class="justShowme" style="width:600px;height:150px;">

                        <tr>
                            <td>Rank</td>
                            <td>Character Name</td>
                            <td>Level</td>
                            </tr>';

    foreach ($result as $index => &$item) {

        $exp = floor(pow($item['experience'] + 1, 1/4));
        $name = $item['name'];

        echo '<tr>';
        echo "<td><B>" . $place++ . "</B></td>";
        echo "<td>" . $name . "</td>";
        echo "<td>" . $exp . "</td>";
        echo '</tr>';
    }
    echo '</table></center>';
?>

I'm not very familiar with MySQL, so I'll just start by listing out what I know is necessary...

'chars' table includes the character information

'sID' column is unique and matches the subscriber 'ID' column, whoever owns the character

'subscriber' table includes the account information and admin status

'ID' is the subscriber ID which the 'sID' from chars table refers to
'admin' is the admin status of the account as Y or N

If a character has an sID value of a subscriber ID with the admin value as Y, it should not be listed.

If the character has an sID value of a subscriber ID with the admin value N, it will be listed and the table is listed as DESC and only show 30 rows of results.

How would I go about doing this?

Any help would be greatly appreciated! This is my first post, so tips on future help requests would be nice too :) Thank you in advance!

答

SELECT tb1.*
    FROM chars tb1
    JOIN subscriber tb2
    ON tb1.sID=tb2.ID
    WHERE admin = 'N'
    ORDER BY CAST(experience AS UNSIGNED ) DESC 
    LIMIT 30;

答

You could use a NOT IN subquery.

See below.

SELECT chars.* 
FROM chars 
WHERE sID NOT IN (SELECT ID FROM subscriber WHERE subscriber.admin = 'Y')
ORDER BY CAST(experience AS UNSIGNED ) DESC 
LIMIT 30;

如何仅显示与另一个表中的值匹配的行

相关推荐