-1

I just want to compare two results from SELECT Query. so how to combine columns from two tables without any relevance(nothing could be same, so can't use JOIN.. ON.. query). And here is my Query Code:

SELECT QueryName,Recommendation,Price
FROM PaidGame
WHERE PriceFinal != '0';

SELECT QueryName,Recommendation,Price
FROM FreeGame
WHERE PriceFinal = '0';

My question is, is there any possible way for me to combine these columns in one output. Here is my output, I want to get these 6 columns together, just for comparison. enter image description here

enter image description here

And here is what I expected, I just want to make them show together. enter image description here

Jiacheng Gao
  • 365
  • 3
  • 9
  • more details, I want to see 6 columns for the up question. not just 3 – Jiacheng Gao Nov 09 '18 at 00:16
  • Please provide a short example of input and expected output. – Henning Koehler Nov 09 '18 at 00:17
  • Learn how to ask question here. https://stackoverflow.com/help/how-to-ask – Eric Nov 09 '18 at 00:18
  • sorry about the fewer details about output, and expected output, I just add the screenshot for the output – Jiacheng Gao Nov 09 '18 at 01:13
  • Your question says there are no similar columns, yet the output image shows that QueryName is the same. Why not join on that? – Sam M Nov 09 '18 at 02:38
  • Sorry, here's an easier way to understand my question. I am wondering how to combine two tables side by side. – Jiacheng Gao Nov 09 '18 at 03:19
  • @JiachengGao is there any primary key in both the table(s) defining the order in which they appear ? Always remember that MySQL stores data in unordered fashion. Also, what is your MySQL server version ? Do you have access to latest version of MySQL (8.0.2 and above) ? – Madhur Bhaiya Nov 09 '18 at 08:16

3 Answers3

0

You could do a union or union all to combine the two selects or compare the checksum of the 3 columns to see if they are equal

Jassem Abdal
  • 1
  • 1
  • thanks, I understand, but I just want to make them show together, I changed my question, you could see what I expected, 6 columns. – Jiacheng Gao Nov 09 '18 at 02:14
  • You could do row_number() on each table into temp tables then join or outer join the two tables based on the row number – Jassem Abdal Nov 10 '18 at 13:10
0
SELECT QueryName,Recommendation,Price FROM PaidGame WHERE PriceFinal != '0'
UNION ALL
SELECT QueryName,Recommendation,Price FROM FreeGame WHERE PriceFinal = '0';

This is what you are after. The keyword ALL may not be necessary.

More examples: https://www.w3schools.com/sql/sql_union.asp

Updated

SELECT P.QueryName, P.Recommendation, P.Price, F.QueryName, F.Recommendation, F.Price
FROM PaidGame P, FreeGame F

Or

SELECT P.QueryName, P.Recommendation, P.Price, F.QueryName, F.Recommendation, F.Price
FROM PaidGame P 
JOIN FreeGame F
Mohammad C
  • 1,321
  • 1
  • 8
  • 12
  • yes, you are right, but I don't want to combine these 6 columns to 3, I just want to see 6 columns, just like columns: QueryName, Recommendation, Price, QueryName, Recommendation, Price – Jiacheng Gao Nov 09 '18 at 02:08
  • Check the updated. This will show all combinations so u will have repetitions coz they don't have anything in common. Also is the where conditions needed. Free games are always 0 and Paid games are never 0. Right? – Mohammad C Nov 09 '18 at 09:50
  • Your edited answer wont work. It is a poor Cross JOIN and will give out 36 rows, if there are 6 rows only in both the tables. Also, Please don't use Old comma based Implicit joins and use Modern [Explicit `Join` based syntax](https://stackoverflow.com/q/5654278/2469308) – Madhur Bhaiya Nov 09 '18 at 09:54
  • 1
    what you updated works for me, thanks a lot, sorry for the late reply – Jiacheng Gao Nov 12 '18 at 17:05
0

Aside from the issue that this would be better done in the presentation layer (e.g. XSL, report etc), you can do it.

My solution would be to create an index table, then join to each of the table querie,s adding in a row identifier. Something like this (hopefully this translates to Mysql - I'm more of a TSQL person):

create table #index (indexnum int)

declare @minnum as int set @minnum=1 while @minnum<1000 BEGIN

insert into #index select @minnum set @minnum=@minnum+1 END


create table #paidgame  (queryname varchar(100), recommendation varchar(100), price varchar (100))

create table #freegame  (queryname varchar(100), recommendation varchar(100), price varchar (100))

insert into #paidgame select 'Game 1', 'Good', '£4.99' UNION ALL select 'Game 2', 'Good', '£3.99' UNION ALL select 'Game 3', 'Good', '£5.99' 

insert into #freegame select 'Game 4', 'Good', '£0.00' UNION ALL select 'Game 5', 'Good', '£0.00' UNION ALL select 'Game 6', 'Good', '£0.00' 


select * from

#index i left join (select * from (

SELECT QueryName,Recommendation,Price, dense_rank() OVER (ORDER BY queryname) as prank FROM #PaidGame pg ) aa ) t1 on t1.prank=i.indexnumn

left join 

(select * from (SELECT QueryName,Recommendation,Price, dense_rank() OVER (ORDER BY queryname) as frank  FROM #FreeGame fg ) bb) t2 on i.indexnumn=t2.frank

where t1.queryname is not null and t2.queryname is not null
iainc
  • 862
  • 6
  • 20