painful SQL query!

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
User avatar
s.dot
Tranquility In Moderation
Posts: 5001
Joined: Sun Feb 06, 2005 7:18 pm
Location: Indiana

painful SQL query!

Post by s.dot »

I have a table of user ids for three different web sites. Each row is composed of a member who is a member of all three web sites and contains their *different* user id in each web site.

Sample Data:

Code: Select all

id | site1id | site2id | site3id
--------------------------
1 | 123 | 456 | 789
2 | 233 | 838 | 849
3 | 839 | 282 | 473
--------------------------
Secondly, I have a table that contains the site name, userid, and a number

Code: Select all

id | site_id | user_id | number
--------------------------------
1 | site1 | 123 | 77
2 | site1 | 233 | 89
3 | site1 | 839 | 99
--------------------------------
I want to create a query that finds the `user_id` where `number` is between 50 and 100 for all 3 site_ids.

For example, if the sites were yahoo.com, google.com, and live.com .. I'd want to query where each userid in the first table has number between 50 and 100 for yahoo.com google.com and live.com in the second table (which is a bit tricky to correspond the rows since they have a different user id at each site).

EDIT| Perhaps I can explain this better with an example.

A user has username bob at site1, bob2 at site2, and bob3 at site 3
I want to query finding `number` between 50 and 100 with bob as the id for site1, bob2 as the id for site2, or bob3 as the id for site3
Set Search Time - A google chrome extension. When you search only results from the past year (or set time period) are displayed. Helps tremendously when using new technologies to avoid outdated results.
User avatar
sergio-pro
Forum Commoner
Posts: 88
Joined: Sat Dec 27, 2008 12:26 pm

Re: painful SQL query!

Post by sergio-pro »

Hi

Try this one, its not the best way it can be done. But this seem to be working.

Code: Select all

 
 
SELECT DISTINCT userIds.id
FROM (
SELECT id, 1 AS siteId, i1.site1id AS siteUserId FROM `user_ids` AS i1
UNION
SELECT id, 2, i2.site2id FROM `user_ids` AS i2
UNION
SELECT id, 3, i3.site3id FROM `user_ids` AS i3) AS userIds
INNER JOIN site_ids sids ON
           sids.`site_id` = userIds.siteId
      AND  sids.`user_id` = userIds.siteUserId
      AND  sids.`number` BETWEEN 50 AND 100
 
 
Post Reply