Select with inner join

Question

i need select some data from two tables , please help me use inner join for this selection . players in selction2 must not be in selection1... first select :

$rs = "SELECT * 
        FROM `player` 
        WHERE `status`=1 AND `credit`>=1 AND `username` NOT LIKE '$user' 
        ORDER BY ls ASC,credit DESC 
        LIMIT 0 ,10;

Second: this players must remove from result of selection1

$rs2 = "SELECT * 
        FROM `ip_log` 
        WHERE `playerid`='$ui' AND `win`='1' AND `date`='$date' ";`

`INNER JOIN` is used for finding rows in the two tables that have matching data. If you want to only print rows that _don't_ match, you can't use `INNER JOIN` for that. You can do it with `LEFT OUTER JOIN`. — Barmar, Jul 10 '15 at 01:04
It's unclear: Do you want to show log messages for everyone except the users in selection 1, or do you want to show all the players except the ones with log messages in selection 2? — Barmar, Jul 10 '15 at 01:11
tables are related with user id ...`playerid`='$ui'..... Selection2 from Ip_log must not be in result of selection1 — hamid, Jul 10 '15 at 01:19

score 0 · Answer 1 · edited May 23 '17 at 11:51

You can use LEFT JOIN for this:

This shows the log messages for everyone not in selection 1.

SELECT l.*
FROM ip_log AS l
LEFT JOIN 
    (SELECT username
     FROM player
     WHERE status = 1 AND credit >= 1 AND username NOT LIKE '$user'
     ORDER BY ls ASC, credit DESC 
     LIMIT 10) AS p
ON l.player = p.username
WHERE win = 1 and date = '$date'
AND p.username IS NULL

This shows the top 10 player data, except the ones with log messages in selection 2

SELECT p.*
FROM player AS p
LEFT JOIN ip_log AS l ON l.player = p.username AND l.win = 1 AND l.date = '$date'
WHERE p.status = 1 AND p.credit >= 1 AND p.username NOT LIKE '$user'
AND l.player IS NULL
ORDER BY p.ls ASC, p.credit DESC
LIMIT 10

In both cases, testing a column in the second table with IS NULL makes it return only the rows in the first table that don't have a match in the second table. See

Return row only if value doesn't exist

score 0 · Answer 2 · answered Jul 10 '15 at 01:44

0

You can do it with LEFT JOIN

SELECT player.*,ip_log.* FROM `player` LEFT JOIN `ip_log` ON player.id!=ip_log.playerid GROUP BY player.id

answered Jul 10 '15 at 01:44

Mojtaba

4,852
5
21
38

can you add more condition... all conditions that used in two selections – hamid Jul 10 '15 at 02:53
You can add your conditions easily. Just add them after the first condition after 'ON'. e.g. ON player.id!=ip_log.playerid AND player.status='1' – Mojtaba Jul 10 '15 at 16:39

Select with inner join

2 Answers2