CODEWITHSUNDEEP
mysqlsqlpostgresql
normalcepalin
normalcepalin

Reputation: 335

How can I get data from two tables

I was told to create an endpoint to GET /feed where users can view all articles or gifs, showing the most recently posted articles or gifs first. Here is my article and gif table

CREATE TABLE article(
    article_id SERIAL PRIMARY KEY,
    title VARCHAR(300),
    article text ,
    user_id INTEGER REFERENCES user(id) ON DELETE CASCADE,
    created_on TIMESTAMP DEFAULT Now() 
  )

CREATE TABLE gif(
    gif_id SERIAL PRIMARY KEY,
    title text NOT NULL ,
    cloudinary_id VARCHAR (3000),
    url VARCHAR(3000) ,
    user_id INTEGER REFERENCES user(id) ON DELETE CASCADE,
    created_on TIMESTAMP DEFAULT Now()
      )

How can I query my db to show both the article and gif table based on when they where created.

Upvotes: 0

Views: 86

Answers (4)

Adder
Adder

Reputation: 5868

You could try to use UNION ALL to select from both tables.

The following code is tested as syntactically ok in my dbfiddle for MySQL 8.0.

select * from (      
    (select 'article' as what, article_id as id, title, NULL as cloudinary_id, NULL as url, user_id, created_on from article order by created_on desc limit 0,100
     )UNION ALL(
    select 'gif' as what, gif_id as id, substr(title, 1, 300), cloudinary_id, url, user_id, created_on from gif order by created_on desc limit 0,100
       )
)  as t order by created_on desc limit 0,100;

Upvotes: 0

atakan
atakan

Reputation: 715

I'd use a simple UNION approach to get a simple Feed from the given tables. The more clever approach would be using joins after iterating your tables a little bit more.

The first rule of SQL Union, you need to gather everything under a common name when you need to display data from different tables like articles and gifs.

SELECT feed_id, title, article, url, user_id, created_on, feed_type  FROM
(
    SELECT
        feed_id = article_id,
        title,
        article,
        url='', // put an empty url column to virtual table since you need to merge it with the gif table.
        user_id,
        created_on,
        'article' as feed_type
    FROM
        article
    UNION
    SELECT
        feed_id = gif_id,
        title,
        article='', // put an empty article column to virtual table since you need to merge it with the gif table.
        url,
        user_id,
        created_on
        'gif' as feed_type
    FROM
        gif
) AS MyFeedTable
ORDER BY created_on desc

More on MySQL unions can be found here

Upvotes: 0

Thorsten Kettner
Thorsten Kettner

Reputation: 94859

The two tables are not related, except for the person who created the data. So, if you want to show the entries created by a user, you will naturally write two queries:

select * from article where user_id = $user_id order by created_on desc;

and 

select * from gif where user_id = $user_id order by created_on desc;

In your app or Website you can then decide how to display the data. In two separate grids? In only one? Just loop through the data and display it according to your needs.

Upvotes: 0

loic queruel
loic queruel

Reputation: 191

Using dummy columns to account for the different structures and add a union to join them:

SELECT * FROM (
    (SELECT article_id, title, article , NULL as cloudinary_id, NULL as url, user_id, created_on, 'article' as table_name  FROM article)
    UNION ALL
    (SELECT gif_id, title, NULL as article, cloudinary_id , url,  user_id, created_on , 'gif' as table_name  FROM gif)
) results
ORDER BY created_on ASC

Upvotes: 1

Related Questions