SQL - Is there a query that will do "foreach A in table, if !B, insert B"?

Question

I have a table with 2 columns:

nid realm
1   domain_id
1   domain_site
2   domain_id
3   domain_id

I want every entry to have 1 entry for domain id, and 1 for domain site. So I want to end up with:

nid realm
1   domain_id
1   domain_site
2   domain_id
2   domain_site
3   domain_id
3   domain_site

If I was doing this in PHP, I'd just foreach through the whole list and insert the extra line whenever it didn't exist. Unfortunately I only have PHPmyAdmin access to this DB. Is there a way to do this in straight SQL?

(If it makes a difference: The table has about 3000 rows currently, of which I think about 2000 will need the extra line inserted. Also, this is a one-time thing so it does not need to be optimized/uber-slick.)

Answer 1

I think this will do it, but I don't have a place to test it right now and I'm used to Sql Server rather than MySQL:

INSERT INTO `table`
SELECT id.nid, r.realm
FROM (SELECT nid FROM `table` GROUP BY nid) id
CROSS JOIN (SELECT realm FROM `table` GROUP BY realm) r
LEFT JOIN `table` t ON t.nid=id.nid AND t.realm=r.realm
WHERE t.realm IS NULL

Answer 2

insert into MyTable
(nid, realm)
select nid, 'domain_id'
from MyTable m where not exists (
    select 1 
    from MyTable
    where MyTable.nid = m.nid and realm = 'domain_id'
)
union all
select nid, 'domain_site'
from MyTable m where not exists (
    select 1 
    from MyTable
    where MyTable.nid = m.nid and realm = 'domain_site'
)

Answer 3

INSERT IGNORE INTO `table`
SELECT `alt1`.`nid`, `alt2`.`realm`
FROM `table` AS `alt1`, `table` AS `alt2`

Answer 4

If you have a UNIQUE constraint over (nid, realm), you could do this:

INSERT IGNORE INTO nidTable (nid, realm)
  SELECT nid, 'domain_site'
  FROM nidTable WHERE realm = 'domain_id';