CODEWITHSUNDEEP
mysqlsqldatabase
user51
user51

Reputation: 10163

Mysql: How to get custom resultset as select statement output

I have below Mysql query -

Select ('GOOGLE.COM', 'MSN.COM', 'YAHOO.COM', 'YOUTUBE.COM', 'BING.COM', 'FACEBOOK.COM', 'LIVE.COM', 'MICROSOFT.COM', 'WIKIPEDIA.ORG', 'LINKEDIN.COM') as domain from dual;

I'm expecting the below resultset.

'GOOGLE.COM'

'MSN.COM'

'YAHOO.COM'

'YOUTUBE.COM'

'BING.COM'

'FACEBOOK.COM'

'LIVE.COM'

'MICROSOFT.COM'

'WIKIPEDIA.ORG'

'LINKEDIN.COM'

But I'm getting error Operand should contain 1 column(s). How can I fix my query so I can get correct results?

Upvotes: 0

Views: 115

Answers (1)

GMB
GMB

Reputation: 222462

If you want one record per value, you can use union all:

select 'GOOGLE.COM' domain
union all select 'MSN.COM'
union all select 'YAHOO.COM'
union all select 'YOUTUBE.COM'
union all select 'BING.COM'
union all select 'FACEBOOK.COM'
union all select 'LIVE.COM'
union all select 'MICROSOFT.COM'
union all select 'WIKIPEDIA.ORG'
union all select 'LINKEDIN.COM'

In other RDMBS, such as Postgres or SQL Server, this would have been as simple as:

SELECT domain 
FROM ( VALUES
        ('GOOGLE.COM'),
    ('MSN.COM'),
    ('YAHOO.COM'),
    ('YOUTUBE.COM'),
    ('BING.COM'),
    ('FACEBOOK.COM'),
    ('LIVE.COM'),
    ('MICROSOFT.COM'),
    ('WIKIPEDIA.ORG'),
    ('LINKEDIN.COM')
) AS t(domain);

But MySQL does not support this syntax. A workaround is to to create a table, that you can then link in your queries:

CREATE TABLE tmp (domain VARCHAR(50));
INSERT INTO tmp(domain) VALUES
    ('GOOGLE.COM'),
    ('MSN.COM'),
    ('YAHOO.COM'),
    ('YOUTUBE.COM'),
    ('BING.COM'),
    ('FACEBOOK.COM'),
    ('LIVE.COM'),
    ('MICROSOFT.COM'),
    ('WIKIPEDIA.ORG'),
    ('LINKEDIN.COM')
;

Upvotes: 2

Related Questions