Page 1 of 1

painful SQL query!

Posted: Sun Jan 25, 2009 2:47 am
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

Re: painful SQL query!

Posted: Sun Jan 25, 2009 5:36 am
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