Select from db1 where user_id doesn't match other dbs
Moderator: General Moderators
Select from db1 where user_id doesn't match other dbs
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
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
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
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
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
play with the select that I gave to you... solution is there....
you can do it!! 
HINT:... look the WHERE clause
HINT:... look the WHERE clause
Re: Select from db1 where user_id doesn't match other dbs
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;Re: Select from db1 where user_id doesn't match other dbs
look my marks ^^^ you don't JOIN a table with a DATABASE... you JOIN tables with tablesJKM wrote:I'm using MySQL Query Browser, and it returned "No database selected".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;
Re: Select from db1 where user_id doesn't match other dbs
Hmm, I got it to work now: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
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;table a = ~5000 rows
table b = ~30000 rows
table c = ~20000 rows
table d = ~15000 rows
- John Cartwright
- Site Admin
- Posts: 11470
- Joined: Tue Dec 23, 2003 2:10 am
- Location: Toronto
- Contact:
Re: Select from db1 where user_id doesn't match other dbs
You probably don't have the neccesary keys to make joins fast. Post your table structures.
Another thing to make debugging easier is run
for some details on how mysql is collecting the rows.
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 50Re: Select from db1 where user_id doesn't match other dbs
Do you have INDEXES in your tables?.... post your data structures....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
NOTE: I didn't see the previous post asking for the same
Re: Select from db1 where user_id doesn't match other dbs
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'.
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;