Select from db1 where user_id doesn't match other dbs

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
JKM
Forum Contributor
Posts: 221
Joined: Tue Jun 17, 2008 8:12 pm

Select from db1 where user_id doesn't match other dbs

Post 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
mikosiko
Forum Regular
Posts: 757
Joined: Wed Jan 13, 2010 7:22 pm

Re: Select from db1 where user_id doesn't match other dbs

Post 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;
 
JKM
Forum Contributor
Posts: 221
Joined: Tue Jun 17, 2008 8:12 pm

Re: Select from db1 where user_id doesn't match other dbs

Post 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
mikosiko
Forum Regular
Posts: 757
Joined: Wed Jan 13, 2010 7:22 pm

Re: Select from db1 where user_id doesn't match other dbs

Post by mikosiko »

play with the select that I gave to you... solution is there.... :) you can do it!! :)

HINT:... look the WHERE clause
JKM
Forum Contributor
Posts: 221
Joined: Tue Jun 17, 2008 8:12 pm

Re: Select from db1 where user_id doesn't match other dbs

Post 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".
mikosiko
Forum Regular
Posts: 757
Joined: Wed Jan 13, 2010 7:22 pm

Re: Select from db1 where user_id doesn't match other dbs

Post 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
JKM
Forum Contributor
Posts: 221
Joined: Tue Jun 17, 2008 8:12 pm

Re: Select from db1 where user_id doesn't match other dbs

Post 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
User avatar
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

Post 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.
mikosiko
Forum Regular
Posts: 757
Joined: Wed Jan 13, 2010 7:22 pm

Re: Select from db1 where user_id doesn't match other dbs

Post 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
JKM
Forum Contributor
Posts: 221
Joined: Tue Jun 17, 2008 8:12 pm

Re: Select from db1 where user_id doesn't match other dbs

Post 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;
Post Reply