painful SQL query!
Posted: Sun Jan 25, 2009 2:47 am
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:
Secondly, I have a table that contains the site name, userid, and a number
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
Sample Data:
Code: Select all
id | site1id | site2id | site3id
--------------------------
1 | 123 | 456 | 789
2 | 233 | 838 | 849
3 | 839 | 282 | 473
--------------------------Code: Select all
id | site_id | user_id | number
--------------------------------
1 | site1 | 123 | 77
2 | site1 | 233 | 89
3 | site1 | 839 | 99
--------------------------------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