Creating MySQL statement to pull any value that exists in one table, but not another

schwim

Hi there everyone,

I want to prune my user's table. Here's how it works. When they register on my site, they are created in the pn_user table. Then, when they enter the forum for the first time, their username is also entered into the pn_phpbb_user table.

I would like to run a simple mysql statement that will give me all of the users in pn_user that don't exist in pn_phpbb_user.

I can create the statements for each one, but I don't know how to join them together 🙂

thanks,
json

Lars_Berg

select <columns>
  from pn_user pu
 where not exists
          (select *
               from pn_ppbb_user pu
              where pn.columnName = pu.column_name)

Fill in your real column names as apropriate.

schwim

Hi there lars,

I'm sure it's because of my application of your code, but I'm getting an error:

select *
  from pn_users pu
 where not exists
          (select *
               from pn_phpbb_users pu
              where pn.pn_uname = pu.username);

I tried running this in phpMyAdmin and got the following error.

unknown column: pn.pn_uname

For clarification, I need

pn_name from the pn_users table only when username(with the same value) doesn't exist in the pn_phpbb_users table.

I am running MySQL 4.0.21

thanks,
json

schwim

Hey, think I got it:

select *
  from pn_users pu
 where not exists
          (select *
               from pn_phpbb_users pu
              where pn.pn_uname = pu.username);

Thanks very much for your assistance. I greatly appreciate it.

thanks,
json

laserlight

Perhaps it should be something like this instead:

select *
    from pn_users pu
    where not exists
        (select *
            from pn_phpbb_users bb_u
            where pu.pn_uname = bb_u.username);