CODEWITHSUNDEEP
mysqlsql
Leozz_den
Leozz_den

Reputation: 85

inner join in Sql Subquery

I am trying to run this query with using subquery but not able to fetch my result .is any one here to help me in geting it correct.

SELECT u.uv_id ID
     , d.sd_code Code
     , u.uv_name Title
     , u.uv_nirf Nirf
     , (SELECT sd_code 
          FROM vm_university_type t
          JOIN vm_seo_detail d
            ON t.ut_id = d.sd_ty_id 
           AND d.sd_ty = 'vm_university_type') AS 'University Type' 
     , a.ab_name 'Approved By' 
     , u.uv_seats Seats
     , g.ug_name 'University Group'
  FROM vm_universities u
  JOIN vm_seo_detail d
    ON u.uv_id = d.sd_ty_id 
   AND d.sd_ty = 'vm_universities'
  JOIN vm_university_type t
    ON u.uv_ut_id = t.ut_id
  JOIN vm_approved_by a
    ON u.uv_ab_id = a.ab_id
  JOIN vm_caste c
    ON u.uv_c_id = c.caste_id
  JOIN vm_university_groups g
    ON u.uv_ug_ids = g.ug_id;

i got this error Subquery returns more than 1 row

Upvotes: 1

Views: 58

Answers (3)

Mohanish
Mohanish

Reputation: 269

(SELECT `sd_code` FROM vm_university_type INNER JOIN vm_seo_detail ON (vm_university_type.ut_id = vm_seo_detail.sd_ty_id AND vm_seo_detail.sd_ty = 'vm_university_type') WHERE vm_university_type.ut_id=vm_universities.uv_ut_id ) AS 'University Type' ,

Upvotes: 1

Samir Selia
Samir Selia

Reputation: 7065

You do not need subquery at all. The column Code in SELECT statement contains University Type

Try below query.

SELECT vm_universities.uv_id AS 'ID' , vm_seo_detail.sd_code AS 'Code', 
vm_universities.uv_name AS 'Title' , vm_universities.uv_nirf AS 'Nirf', 
vm_approved_by.ab_name AS 'Approved By' , vm_universities.uv_seats as 'Seats',
vm_university_groups.ug_name AS 'University Group'
FROM vm_universities INNER JOIN vm_seo_detail
ON (vm_universities.uv_id = vm_seo_detail.sd_ty_id
      AND vm_seo_detail.sd_ty = 'vm_universities')
INNER JOIN vm_university_type
ON (vm_universities.uv_ut_id = vm_university_type.ut_id) 
INNER JOIN vm_approved_by
ON (vm_universities.uv_ab_id = vm_approved_by.ab_id) 
INNER JOIN vm_caste
ON (vm_universities.uv_c_id = vm_caste.caste_id) 
INNER JOIN vm_university_groups
ON (vm_universities.uv_ug_ids = vm_university_groups.ug_id);

Upvotes: 1

rustot
rustot

Reputation: 331

SELECT `sd_code` FROM vm_university_type INNER JOIN vm_seo_detail ON (vm_university_type.ut_id = vm_seo_detail.sd_ty_id AND vm_seo_detail.sd_ty = 'vm_university_type')

looks like you forgot WHERE section in subquery, that will bind it to main query. something like

WHERE vm_university_type.ut_id=vm_universities.uv_ut_id

ps. and why you need this subquery at all, if you already join with vm_university_type in main query?

Upvotes: 0

Related Questions