-1

I have a table apartment as below

aid   |     aname   
 1    |    dream home
 2    |    My hub
 3    |    Lake view

another table apartment_details

id    |    aid    |    bhk    |   size    |    facing
 1    |     1     |     2     |   1200    |     east
 2    |     1     |     2     |   1200    |     west
 3    |     1     |     2     |   1000    |     south 
 4    |     1     |     2     |   1000    |     north

I have written the query as

    SELECT distinct ap.aid, ap.aname, al.bhk,  (select group_concat(distinct concat(al.bhk,'BHK - ',al.size)) from apartment_details as al where al.id = ap.aid) as details

When I tried to display details using foreach I get the output as

       2BHK - 1200
       2BHK - 1200
       2BHK - 1000
       2BHK - 1000

In this query it is considering bhk, size, facing in distinct and the output obtained is based on facing. This looks something like I am displaying duplicate data or something the same data is repeating as there is no facing displayed. How can I display only distinct values based on bhk, size and not facing so that I get the output as

       2BHK - 1200
       2BHK - 1000

Can anyone help me in solving this issue? Thanks in advance

rji rji
  • 697
  • 3
  • 17
  • 37
  • How is it possible that the same apartment can have two different sizes? (360 degree views is also a little unusual, but not inconceivable). – Strawberry Dec 17 '14 at 10:21

1 Answers1

0

To my way of thinking, in general, there is no problem in SQL for which GROUP_CONCAT is the solution. So, with that in mind, let's start with this:

SELECT DISTINCT bhk,size FROM apartment_details
Strawberry
  • 33,750
  • 13
  • 40
  • 57
  • As is it a subquery it throws an error as operand should contain 1 column for that I have used group_concat – rji rji Dec 17 '14 at 09:11