CODEWITHSUNDEEP
mysqldatabase
chitti
chitti

Reputation: 121

How to get only one record instead of fetching 2 records when using IN

I am working on sql query in mysql for example I am inserting records like key and values pair

Student Table 

StdId StuName   phnNum 
1    John   87678

I am storing detailed Info about that student in student_metadata table

S.NO FieldName   FieldValue     indexVal
1    college       St.Anns         1
2    Address      Arizona      1
3    IdNum         321             1

Now I want to fetch student_metadata

SELECT 
 student.stdId AS StdId
 fieldValue AS fieldValue
FROM 
Student 
 LEFT JOIN 
 Student_metadata ON Student.StdId = student_metadata.indexVal
Where 
 Student_metadata.fieldName IN ('College','IdNum')

Here my problem is If I use 'IN' it gives me 2 rows. like the below

StudentId  fieldValue 
1       St.Anns
1           321

I want to fetch like the below

StudentId  fieldValue IdNum
1       St.Anns    321

Can any one suggest me in this

Upvotes: 1

Views: 150

Answers (2)

Sloan Thrasher
Sloan Thrasher

Reputation: 5040

You could use a pivot table style query:

SELECT
    a.`StuName`,
    COALESCE(b.`FieldVal`) as `college`,
    COALESCE(c.`FieldVal`) as `IdNum`
FROM `Student` a
LEFT JOIN `student_metadata` b
ON b.`indexVal` = a.`StdId` AND b.`FieldName` = 'college'
LEFT JOIN `student_metadata` c
ON c.`indexVal` = a.`StdId` AND c.`FieldName` = 'IdNum'
WHERE a.`StdId` = 1
GROUP BY a.`StdId`

Upvotes: 0

Gurwinder Singh
Gurwinder Singh

Reputation: 39507

You can use conditional aggregation to pivot:

select 
 s.stdid,
 max(case when m.fieldname = 'College' then fieldvalue end) as fieldvalue,
 max(case when m.fieldname = 'IdNum' then fieldvalue end) as idnum
from student s
left join student_metadata m on s.stdid = m.indexval
where m.fieldname in ('College','IdNum')
group by s.stdid

Upvotes: 3

Related Questions