CODEWITHSUNDEEP
mysqlsqlsubquerymysql-error-1064
chwajahat
chwajahat

Reputation: 35

Getting Value in Subquery

I donot want r.reguser_id as test column in results, And if i remove this Column from Select statement then i am having problem in WHERE bu.business_id = r.reguser_id and got an error of More then 1 row Returns.

SELECT r.reguser_name ,  r.reguser_id as test,

(Select r.reguser_id as c, GROUP_CONCAT(i.industry_name) as Industry

From reguser r inner JOIN business_industry bu ON r.reguser_id = bu.business_id  join industry i ON bu.industry_id = i.industry_id

WHERE  bu.business_id = test

GROUP BY bu.business_id
) as indus

FROM reguser r

Kindly tell me how can i resolve this issue ? Thank you.

Upvotes: 0

Views: 40

Answers (2)

Tom Thomas
Tom Thomas

Reputation: 629

SELECT t1.user_name FROM
(SELECT r.reguser_name as user_name,  r.reguser_id as test,

(Select r.reguser_id as c, GROUP_CONCAT(i.industry_name) as Industry

From reguser r inner JOIN business_industry bu ON r.reguser_id = bu.business_id  join     industry i ON bu.industry_id = i.industry_id

WHERE  bu.business_id = test

GROUP BY bu.business_id
) as indus

FROM reguser r)t1
WHERE t1.user_name IS NOT NULL

i think you need to have some sort of where clause if you are using sql developer when querying this way, i.e, giving an alias to a select query. Hope this helps..

Upvotes: 0

MikkaRin
MikkaRin

Reputation: 3084

SELECT r.reguser_name ,  r.reguser_id,

(Select r1.reguser_id as c, GROUP_CONCAT(i.industry_name) as Industry

From reguser r1 inner JOIN business_industry bu ON r1.reguser_id = bu.business_id  join industry i ON bu.industry_id = i.industry_id

WHERE  bu.business_id = r.reguser_id

GROUP BY bu.business_id
) as indus

FROM reguser r

Don't use the same alliases for main table and table in subquery. It's really bad practice

Upvotes: 1

Related Questions