Get join tables database

Question

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 17-Apr-13 7:56am

Yafa Su

Updated 17-Apr-13 8:06am

v7

Add a Solution

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.

2 solutions

Solution 1

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)--

Posted 17-Apr-13 9:14am

JasonMacD

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.

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Maciej Los · Accepted Answer · 2013-04-17T09:28:00

Solution 2

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:

[EDIT]

Posted 17-Apr-13 9:28am

Maciej Los

Updated 17-Apr-13 10:07am

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

Yafa Su 17-Apr-13 16:26pm

nvm I got it :). Thanks again

Maciej Los 17-Apr-13 16:33pm

Yafa Su, please see this article to better understand JOIN's: Visual Representation of SQL Joins[^]

Yafa Su 17-Apr-13 16:40pm

Thank you for the article I will have a look, I know i suck with join, just make me confuse.

Maciej Los 17-Apr-13 16:47pm

Please, stop tormenting yourself. Just start study ;)