SQL - how to get data from two tables

Question

I have these tables:

table_a

user_id
article_id
created_at

articles

user_id
created_at
...

I need to obtain all rows from both tables for the respective user (lets say user_id=1) and sorted them by the column created_at. How to do that?

I have tried to do it this way:

Model.find_by_sql('SELECT table_a.* FROM table_a JOIN articles ON articles.user_id = 1 WHERE table_a.user_id = 1')

But this query won't work.

ON articles.user_id = table_a.user_id You should tell how you join the tables — Michael Laffargue
– Michael Laffargue, Commented Jun 20, 2012 at 13:18
Please define won't work and give examples. Unexpected data, error messages, every time you run it the power in your office goes out and a purple goblin steals your mouse and keyboard? — MatBailie
– MatBailie, Commented Jun 20, 2012 at 13:19
If I think about the query now, the JOIN probably is not needed there... I just need to get all rows from both tables with user_id=1 — user984621
– user984621, Commented Jun 20, 2012 at 13:20
@user984621 I don't know your business model but usually you got User<-->UserArticles<-->Articles. — Michael Laffargue
– Michael Laffargue, Commented Jun 20, 2012 at 13:21

andrewsha · Accepted Answer · 2012-06-20 13:31:23Z

1

I would give a try to following query: SELECT table_a.*, articles.* FROM table_a JOIN articles ON articles.user_id = table_a.user_id WHERE table_a.user_id = 1 ORDER BY table_a.created_at ASC;

answered Jun 20, 2012 at 13:31

andrewsha

3051 silver badge3 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Moyed Ansari · Accepted Answer · 2012-06-20 13:19:14Z

1

try this

SELECT table_a.* ,articles.*
FROM table_a 
LEFT JOIN articles ON articles.user_id = table_a.user_id 
WHERE table_a.user_id = 1
ORDER BY table_a. created_at

answered Jun 20, 2012 at 13:19

Moyed Ansari

8,4612 gold badges38 silver badges57 bronze badges

Comments

timod · Accepted Answer · 2012-06-20 13:19:35Z

1

SELECT
  table_a.*
FROM 
 table_a
JOIN 
 articles 
ON 
 articles.user_id = table_a.user_id 
WHERE 
 table_a.user_id = 1
ORDER BY
 table_a.created_at ASC;

answered Jun 20, 2012 at 13:19

timod

5933 silver badges13 bronze badges

Collectives™ on Stack Overflow

SQL - how to get data from two tables

table_a

articles

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

table_a

articles

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related