Click here to Skip to main content
15,917,926 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
I have 2 tables:

Table 1:
+-----+------------+---------------------+
| id  | first_name | created_at          |
+-----+------------+---------------------+
| 522 | dddd       | 2013-04-11 18:44:07 |
| 523 | cccc       | 2013-03-01 06:19:12 |
| 524 | ffff       | 2013-03-06 21:07:06 |
| 525 | rrrr       | 2013-03-06 22:05:08 |
| 590 | tttt       | 2013-03-16 16:39:59 |
| 584 | yyyy       | 2013-03-22 14:46:28 |

Table 2:
+-----+---------------------+---------------------+
| id  | referred_by_user_id | created_at          |
+-----+---------------------+---------------------+
| 298 |                 522 | 2013-02-22 22:19:06 |
| 299 |                 584 | 2013-03-22 18:27:49 |
| 300 |                 584 | 2013-03-22 18:48:23 |
| 301 |                 584 | 2013-03-22 19:01:40 |
| 302 |                 584 | 2013-03-22 19:05:24 |
| 303 |                 584 | 2013-03-22 19:13:18 |
| 304 |                 590 | 2013-04-10 23:24:17 |
| 305 |                 522 | 2013-04-11 18:44:07 |


Is there a way to get all users id from table 1 to match table 2 referred_by_user_id from a date?
Example: from 2013-03-06 to 2013-03-22
+---------------------+------
| referred_by_user_id |  got
+---------------------+------
|                 524 |  0
|                 525 |  0
|                 590 |  1
|                 584 |  4


Check date from first table not the second table.
Posted
Updated 17-Apr-13 8:06am
v7
Comments
[no name] 17-Apr-13 13:59pm    
It would be nice if you illustrated what your desired output would look like.
Yafa Su 17-Apr-13 14:11pm    
Thanks for asking I update it.

To get all records from first table you need to use LEFT JOIN statement.
SQL
SELECT t1.id AS referred_by_user_id, COUNT(t2.referred_by_user_id) AS [got]
FROM Table1 AS t1 LEFT JOIN Table2 AS t2 ON t1.id = t2.referred_by_user_id
GROUP BY t1.id


Check date from first table not the second table. - i don't get it ;(
Does it mean you want to get only those referred_by_user_id's from first table which date is equal to second table for that particular id? When you explain it, i'll improve my answer.

[EDIT #1]
Sorry, i forgot about grouping options ;(

Now, result:
ref. got
522 2
523 0
524 0
525 0
584 5
590 1

[EDIT]
 
Share this answer
 
v3
Comments
Yafa Su 17-Apr-13 15:47pm    
forget about the date. I try your query it's not wat I want. I got only 1 referred_by_user_id and total of the second table. I want like ID 522 got 2, because in second table he has been refer 2 times, and ID 523 got 0, because second table he got nothing, and so on.
Maciej Los 17-Apr-13 15:59pm    
Sorry, i forgot about grouping options. Please, check query now.
Yafa Su 17-Apr-13 16:07pm    
Thanks man, you save my day. :)
Yafa Su 17-Apr-13 16:19pm    
can you add the created_at date to of the first table?
Maciej Los 17-Apr-13 16:25pm    
Yes, you can, but need to add created_at field in a group list:
SELECT t1.id AS referred_by_user_id, t1.created_at, COUNT(t2.referred_by_user_id) AS [got]
FROM @table1 AS t1 LEFT JOIN @table2 AS t2 ON t1.id = t2.referred_by_user_id
GROUP BY t1.id, t1.created_at
ORDER BY t1.id
SELECT t1.id, t2.created_at FROM Table1 t1
INNER JOIN Table2 t2 ON t2.referred_by_user_id = t1.id
WHERE t2.created_at = '2013-03-22' --<(or whatever date you want to retrieve here)--
 
Share this answer
 
Comments
Maciej Los 17-Apr-13 16:44pm    
Jason, inner join fetches records which are equal in both tables.
Please, correct your answer to avoid down-voting.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900