Page 1 of 1
Select from db1 where user_id doesn't match other dbs
Posted: Fri Mar 05, 2010 10:18 pm
by JKM
Hi there,
I want to run this query, but I need some help:
SELECT * FROM db1.table1 WHERE active=1 AND pref=2
But I want to cross check with the following databases/tables
db2.table1.user_id, db2.table2.user_id AND db3.table1.user_id
The query should return rows from db1.table1 where the user_id (from db1.table1) isn't listed in the other databases/tables
Re: Select from db1 where user_id doesn't match other dbs
Posted: Fri Mar 05, 2010 11:12 pm
by mikosiko
you can start from here....
Code: Select all
SELECT tb1.*
FROM tb1 LEFT JOIN tb2 ON tb1.tb1id = tb2.tb2id
LEFT JOIN tb3 ON tb1.tb1id = tb3.tb3id
WHERE tb2.tb2id IS NULL
AND tb3.tb3id IS NULL;
Re: Select from db1 where user_id doesn't match other dbs
Posted: Sat Mar 06, 2010 7:19 am
by JKM
I'm sorry, but I want it like this (I was a bit tired when I wrote the first post)
It should match db2.table1.user_id OR db2.table2.user_id
It should not be listed in db.3.table1.user_id
Re: Select from db1 where user_id doesn't match other dbs
Posted: Sat Mar 06, 2010 10:46 am
by mikosiko
play with the select that I gave to you... solution is there....

you can do it!!
HINT:... look the WHERE clause
Re: Select from db1 where user_id doesn't match other dbs
Posted: Sat Mar 06, 2010 11:12 am
by JKM
Code: Select all
SELECT database1.user_list.*
FROM database1.user_list
LEFT JOIN database2 ON database1.user_list.user_id = database2.forum_bans.user_id OR database1.user_list.user_id = database2.comment_bans.user_id
LEFT JOIN database1 ON database1.external_banlist.user_id = database1.user_list.user_id
WHERE database2.forum_bans.user_id IS NOT NULL OR database2.comment_bans.user_id IS NOT NULL
AND database1.external_banlist.user_id IS NULL;
I'm using MySQL Query Browser, and it returned "No database selected".
Re: Select from db1 where user_id doesn't match other dbs
Posted: Sat Mar 06, 2010 3:57 pm
by mikosiko
JKM wrote:Code: Select all
SELECT database1.user_list.*
FROM database1.user_list
LEFT JOIN [color=#FF0000][b]database2[/b][/color] ON database1.user_list.user_id = database2.forum_bans.user_id OR database1.user_list.user_id = database2.comment_bans.user_id
LEFT JOIN [color=#FF0000][b]database1[/b][/color] ON database1.external_banlist.user_id = database1.user_list.user_id
WHERE database2.forum_bans.user_id IS NOT NULL OR database2.comment_bans.user_id IS NOT NULL
AND database1.external_banlist.user_id IS NULL;
I'm using MySQL Query Browser, and it returned "No database selected".
look my marks ^^^ you don't JOIN a table with a DATABASE... you JOIN tables with tables
Re: Select from db1 where user_id doesn't match other dbs
Posted: Sat Mar 06, 2010 5:40 pm
by JKM
Hmm, I got it to work now:
Code: Select all
SELECT a.user_list.*
FROM database1.user_list a
LEFT JOIN database2.forum_bans b ON a.user_id = b.user_id
LEFT JOIN database2.comment_bans c ON a.user_id = c.user_id
LEFT JOIN database1.external_banlist d ON a.user_id = d.user_id
WHERE a.active=1
AND d.user_id IS NULL
AND (a.user_id IS NOT NULL OR b.user_id IS NOT NULL)
ORDER BY a.id DESC
LIMIT 50;
but the query took like 10 minutes, and the mysql process used ~200% CPU. Is there a way to solve this?
table a = ~5000 rows
table b = ~30000 rows
table c = ~20000 rows
table d = ~15000 rows
Re: Select from db1 where user_id doesn't match other dbs
Posted: Sat Mar 06, 2010 5:42 pm
by John Cartwright
You probably don't have the neccesary keys to make joins fast. Post your table structures.
Another thing to make debugging easier is run
Code: Select all
EXPLAIN SELECT a.user_list.*
FROM database1.user_list a
LEFT JOIN database2.forum_bans b ON a.user_id = b.user_id
LEFT JOIN database2.comment_bans c ON a.user_id = c.user_id
LEFT JOIN database1.external_banlist d ON a.user_id = d.user_id
WHERE a.active=1
AND d.user_id IS NULL
AND (a.user_id IS NOT NULL OR b.user_id IS NOT NULL)
ORDER BY a.id DESC
LIMIT 50
for some details on how mysql is collecting the rows.
Re: Select from db1 where user_id doesn't match other dbs
Posted: Sat Mar 06, 2010 5:44 pm
by mikosiko
JKM wrote:
LIMIT 50;but the query took like 10 minutes, and the mysql process used ~200% CPU. Is there a way to solve this?
table a = ~5000 rows
table b = ~30000 rows
table c = ~20000 rows
table d = ~15000 rows
Do you have INDEXES in your tables?.... post your data structures....
NOTE: I didn't see the previous post asking for the same
Re: Select from db1 where user_id doesn't match other dbs
Posted: Fri Mar 12, 2010 12:07 pm
by JKM
I solved it.
Another JOIN issue:
I want to make a list for which admins (only from the class='Moderators') that issued most warnings to users with this query, it seems like I'm getting a list for admins that isn't in the class='Moderators'.
Code: Select all
SELECT w.warnadm, COUNT(w.warnadm) AS admin_warnings FROM forum.warnings w
INNER JOIN forum.adminlist a
ON w.warnadm=a.adminid
WHERE a.class='Moderators'
GROUP BY w.warnadm
ORDER BY admin_warnings DESC;