CODEWITHSUNDEEP
phpmysql
SystemX17
SystemX17

Reputation: 3715

MySQL: How do I return only unique entries from one table that don't exist in another based on different columns?

I am working on a backend for a very simple MMORPG. I am working in PHP and MySQL I have the following tables:

characters
----------
id,charactername,country,server,guild,xp,level,etc..

guilds
------
id,guildname,country,server

What I am looking for is a way to collect all the unique guilds from the characters table, find out which ones aren't in the guilds table and update guilds. The tricky part seems to be that the same name guild can exist on different country/server combinations. Here are some examples of valid guild entries to show that they don't conflict:

21,"Hardworking Ogres","uk","development"
3151,"Hardworking Ogres","us","development"
231,"Hardworking Ogres","us","live"

So I have the guilds table with a unique index on the guildname, country and server - which works well so that I am not overwriting entries. I have previously been doing a DISTINCT dump of all the guild/country/server from the characters table into an multidimensional array. Then dumping a list of the existing guild/country/server from the guilds table. I am then running a function similar to array_diff to get a list of unique ones that don't exist in the guilds table and running an INSERT query to update it.

I am sure there is an easier way in MySQL to return a unique list of any guild/country/server combinations that don't exist in the guilds table and then run the insert saving time querying 2 massive MySQL requests and then comparing them. I just don't know where to look next, so my question is: Is there a way to accomplish this in MySQL?

Upvotes: 0

Views: 69

Answers (2)

h2ooooooo
h2ooooooo

Reputation: 39540

You can use a simple LEFT JOIN and check where guilds.id is NULL:

SELECT DISTINCT
    ch.country,
    ch.server,
    ch.guild
FROM
    `characters` as `ch`
LEFT JOIN
    `guilds` as `gu`
ON
    (gu.guild = ch.guild AND gu.server = ch.server AND gu.country = ch.country)
WHERE
    gu.id IS NULL

Upvotes: 1

ypercubeᵀᴹ
ypercubeᵀᴹ

Reputation: 115600

SELECT DISTINCT
    c.country, c.server, c.guild
FROM 
    characters AS c
WHERE
    NOT EXISTS
    ( SELECT *
      FROM guilds AS g
      WHERE g.country = c.country
        AND g.server = c.server
        AND g.guildname = c.guild
    ) ;

Before running the query:
if the index in guilds is (country, server, guildname), add an index (not unique) on characters, with the columns in the same order.

Upvotes: 1

Related Questions