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;