CODEWITHSUNDEEP
mysqlsql
Tyler Tran
Tyler Tran

Reputation: 65

*SQL Error: Error Code: 1242. Subquery returns more than 1 row

I had a database have 2 table website and domains, and a website can store more than 1 domain, and I was create a view:

create view website_infor
as
select
    w.name 'Website Name',
    (
        select url
        from domains
        where company_id = w.id
    )
    'Domains URL'
from
    website w;

And SQL response error:

Error Code: 1242. Subquery returns more than 1 row.

Now I want get all domain match with website ID, someone can help me? Thanks more and sorry for my English so bad.

Upvotes: 1

Views: 109

Answers (3)

Nick
Nick

Reputation: 147166

The JOINs suggested by the other answers will return multiple rows of data for each website (one for each domain). If you only want to get one row of data for each website, use a GROUP_CONCAT instead i.e.

create view website_infor
as
select
    w.name 'Website Name',
    (
        select GROUP_CONCAT(url)
        from domains
        where company_id = w.id
    )
    'Domains URL'
from
    website w;

If for example you have a website example which refers to example.net and example.com, a JOIN will return two rows:

Website Name | Domains URL
-------------|------------
example      | example.net
-------------|------------
example      | example.com
-------------+------------

while the GROUP_CONCAT will return one row:

Website Name | Domains URL
-------------|-------------------------
example      | example.net, example.com
-------------+-------------------------

Upvotes: 2

Yogesh Sharma
Yogesh Sharma

Reputation: 50163

You just need to do JOINs with domain table 

SELECT 
       w.name AS 'Website Name',
       d.url AS 'Domains URL'     
FROM  website w
INNER JOIN domains d ON d.company_id = w.id

Upvotes: 1

DineshDB
DineshDB

Reputation: 6193

You have to use JOIN instead of Sub-Query:

SELECT
    w.name 'Website Name',
    D.url 'Domains URL'
FROM website w
JOIN domains D ON W.id = D.company_id

Upvotes: 1

Related Questions