Combining two select statements without on clausule

Question

I have two tables that are not related at all

First table is called pocetna_baner that stores 3 pictures that are going to be displayed on index page.

pocetna_baner table:

Second table is for blog and I want to take information of 3 blogs ordered by datum desc:

blog table

I tried using UNION, UNION ALL, JOIN without clauses, full outer join, but just cannot get it to work because I never had this situation where there is no on clause

union all with queries that have a different number of columns -> I searched for answers and got to this one which seemed okay but unfortunately couldn't get it to work

This is what I have by now

(SELECT null as blog_id, null as naslov, null as tekst1, null as kategorija, p.ime_slike FROM 
pocetna_baner p )
UNION ALL
(SELECT b.blog_id, b.naslov, b.tekst1, b.kategorija ,null as ime_slike
FROM blog b ORDER BY b.datum DESC LIMIT 3  )

And result is this

My expected output is only to take b.blog_id, b.naslov, b.tekst1, b.kategorija FROM blog b ordered by datume desc limit 3 and add column with ime_slike from second table

Union must have same columns.If you want to use Union on different columns then put null value — sultania23, Jul 25 '19 at 12:58
If your two tables are completely unrelated, and doesn't contain similar information, you should not be using UNION. Make 2 different queries instead. — Jonas Rosenqvist, Jul 25 '19 at 13:01
@sultania23 so if I am correct you are suggesting adding collumns to first table that are going to be null in order to match number of collumns in query ? — bakero98, Jul 25 '19 at 13:06
Don't add column to table just put their value null at the time of query execution. — sultania23, Jul 25 '19 at 13:11

forpas · Accepted Answer · 2019-07-25T13:44:37.640

You can construct the ON clause by creating 1 counter column in each table and matching on them:

select
  b.blog_id, b.naslov, b.tekst1, b.kategorija, p.ime_slike
from (
  select 
    blog_id, naslov, tekst1, kategorija,
    (@row_number1:=@row_number1 + 1) num
  from blog, (select @row_number1:=0) t 
  order by datum desc limit 3
) b left join (
  select ime_slike,
  (@row_number2:=@row_number2 + 1) num
  from pocetna_baner, (select @row_number2:=0) t
  order by id limit 3
) p on p.num = b.num

If you are sure that the ids in the table pocetna_baner have values 1, 2 and 3 then you can simplify to this:

select
  b.blog_id, b.naslov, b.tekst1, b.kategorija, p.ime_slike
from (
  select 
    blog_id, naslov, tekst1, kategorija,
    (@row_number1:=@row_number1 + 1) num
  from blog, (select @row_number1:=0) t 
  order by datum desc limit 3
) b left join pocetna_baner p 
on p.id = b.num

score 0 · Answer 2 · answered Jul 25 '19 at 13:14

I have just given an example by this code. Please change as per needs.

SELECT * FROM ( 
SELECT p.ime_slike,null as blog_id,null as naslov,null as kategorija FROM 
pocetna_baner p 
UNION ALL 
SELECT b.blog_id, b.naslov, b.tekst1, b.kategorija ,null as ime_slike
FROM blog b 
ORDER BY datum DESC LIMIT 3 ) AS k

Combining two select statements without on clausule

2 Answers2