[MySQL] Relations, connect by two fields

Dev Shed Forums Sponsor:

February 4th, 2012, 10:44 AM

Pavcio

Registered User

Join Date: Feb 2012

Posts: 2

Time spent in forums: 54 m 34 sec
Reputation Power: 0

[MySQL] Relations, connect by two fields

Hello,

I request to you for help in solving my problem. I have two tables in a database that I need to connect in a fairly specific way.

Code:

TABLE 1: contains a list of all registered users

id     username     
1      user1          
2      user2         
3      user3        
4      user4        
5      user5

Code:

TABLE 2: lists the files that have been flagged by users as being inconsistent with the terms and conditions

id     user_id              uploader_id
1      1                       3 
2      2                       3
3      3                       2
4      1                       3
5      1                       4

How to extract of Table 1 only those users who appeared in table2 in field: user_id or uploader_id? I need to create links to users details based on their username (no id) so I need to connect in some way, these tables. I'll be very grateful for your help.

Regards

February 4th, 2012, 06:09 PM

fubes2000

manwich

Join Date: Oct 2003

Location: Canadanistan

Posts: 575

Folding Points: 388781 Folding Title: Super Ultimate Folder - Level 1

Time spent in forums: 1 Week 10 h 47 m
Reputation Power: 382

Something like:

Code:

SELECT ...
FROM table1 t1 INNER JOIN table2 t2
	ON ( t1.id = t2.user_id
		OR t1.id = t2.uploader_id )
WHERE t1.username LIKE 'ted_danson';

__________________

February 4th, 2012, 08:04 PM

r937

SQL Consultant

Join Date: Feb 2003

Location: Toronto Canada

Posts: 25,046

Time spent in forums: 3 Months 2 Days 22 h 44 sec
Reputation Power: 3829

Quote:

Originally Posted by fubes2000

Code:

	ON ( t1.id = t2.user_id
		OR t1.id = t2.uploader_id )

this can be simplified to...

Code:

ON t1.id IN ( t2.user_id , t2.uploader_id )

note that my parentheses are required but yours are superfluous

Comments on this post

fubes2000 agrees: I thought this might work, but I've never used an IN in this way before. Parentheses are a force of
habit I guess.

__________________
r937.com | rudy.ca
please visit Simply SQL and buy my book

February 4th, 2012, 08:51 PM

Pavcio

Registered User

Join Date: Feb 2012

Posts: 2

Time spent in forums: 54 m 34 sec
Reputation Power: 0

Both methods work very well. Thank you.

Now another problem.
I need a list of users who are in the field user_id, uploader_id in Table 2.
The problem is that the table1 has more than 1 million records so to create a list I need to use pagaination. To do pagination I need to know how many users appears in the fields user_id, uploader_id in table 2.

I used this query:

Code:

SELECT COUNT(DISTINCT(t1.id))
FROM table1 t1 INNER JOIN table2 t2
ON t1.id IN ( t2.user_id , t2.uploader_id )

It's takes far too long. Could you prompt some other solution?

February 4th, 2012, 09:09 PM

r937

SQL Consultant

Join Date: Feb 2003

Location: Toronto Canada

Posts: 25,046

Time spent in forums: 3 Months 2 Days 22 h 44 sec
Reputation Power: 3829

Quote:

Originally Posted by Pavcio

Could you prompt some other solution?

use the SQL_CALC_FOUND_ROWS option on the SELECT statement

the number of rows can then be retrieved with SELECT FOUND_ROWS()