List:General Discussion« Previous MessageNext Message »
From:bharani kumar Date:January 3 2010 8:50am
Subject:Re: Render row without duplicates
View as plain text  
Hi ,

Thanks*

*SELECT * FROM (SELECT cHospital FROM MED_PATIENT where cHospital is not
null union select cHospital1 from med_patient where cHospital1 is not null
union select cHospital2 from med_patient where cHospital2 is not null ) A
order by 1


 cHospital<http://localhost/phpmyadmin/sql.php?db=medical&table=med_patient&sql_query=SELECT+%2A+FROM+%28SELECT+cHospital+FROM+MED_PATIENT+where+cHospital+is+not+null+union+select+cHospital1+from+med_patient+where+cHospital1+is+not+null+union+select+cHospital2+from+med_patient+where+cHospital2+is+not+null+%29A+ORDER+BY+%60A%60.%60cHospital%60+ASC&token=160eb1977a91a41f90271414c107d1c5>
    [image:
Edit]<http://localhost/phpmyadmin/tbl_change.php?db=medical&table=med_patient&primary_key=.%60cHospital%60+%3D+%27%27&clause_is_unique=0&sql_query=SELECT+cHospital2+from+med_patient+&goto=sql.php&token=160eb1977a91a41f90271414c107d1c5>
 [image:
Delete]<http://localhost/phpmyadmin/sql.php?db=medical&table=med_patient&sql_query=DELETE+FROM+%60medical%60.%60med_patient%60+WHERE+.%60cHospital%60+%3D+%27%27+LIMIT+1&zero_rows=The+row+has+been+deleted&goto=sql.php%3Fdb%3Dmedical%26table%3Dmed_patient%26sql_query%3DSELECT%2BcHospital2%2Bfrom%2Bmed_patient%2B%26zero_rows%3DThe%2Brow%2Bhas%2Bbeen%2Bdeleted%26goto%3Dtbl_sql.php%26token%3D160eb1977a91a41f90271414c107d1c5&token=160eb1977a91a41f90271414c107d1c5>
     [image:
Edit]<http://localhost/phpmyadmin/tbl_change.php?db=medical&table=med_patient&primary_key=.%60cHospital%60+%3D+%271234%27&clause_is_unique=0&sql_query=SELECT+cHospital2+from+med_patient+&goto=sql.php&token=160eb1977a91a41f90271414c107d1c5>
 [image:
Delete]<http://localhost/phpmyadmin/sql.php?db=medical&table=med_patient&sql_query=DELETE+FROM+%60medical%60.%60med_patient%60+WHERE+.%60cHospital%60+%3D+%271234%27+LIMIT+1&zero_rows=The+row+has+been+deleted&goto=sql.php%3Fdb%3Dmedical%26table%3Dmed_patient%26sql_query%3DSELECT%2BcHospital2%2Bfrom%2Bmed_patient%2B%26zero_rows%3DThe%2Brow%2Bhas%2Bbeen%2Bdeleted%26goto%3Dtbl_sql.php%26token%3D160eb1977a91a41f90271414c107d1c5&token=160eb1977a91a41f90271414c107d1c5>
1234    [image:
Edit]<http://localhost/phpmyadmin/tbl_change.php?db=medical&table=med_patient&primary_key=.%60cHospital%60+%3D+%278524%27&clause_is_unique=0&sql_query=SELECT+cHospital2+from+med_patient+&goto=sql.php&token=160eb1977a91a41f90271414c107d1c5>
 [image:
Delete]<http://localhost/phpmyadmin/sql.php?db=medical&table=med_patient&sql_query=DELETE+FROM+%60medical%60.%60med_patient%60+WHERE+.%60cHospital%60+%3D+%278524%27+LIMIT+1&zero_rows=The+row+has+been+deleted&goto=sql.php%3Fdb%3Dmedical%26table%3Dmed_patient%26sql_query%3DSELECT%2BcHospital2%2Bfrom%2Bmed_patient%2B%26zero_rows%3DThe%2Brow%2Bhas%2Bbeen%2Bdeleted%26goto%3Dtbl_sql.php%26token%3D160eb1977a91a41f90271414c107d1c5&token=160eb1977a91a41f90271414c107d1c5>
8524


I dont want the Null row ,

how to remove the null display ,


Thanks



On Sat, Jan 2, 2010 at 11:38 PM, bharani kumar <
bharanikumariyerphp@stripped> wrote:

> Hi First i want to thanks to my mysql groups,
>
> Sorry , just now i find time to see mail,
>
> Am not sure, but i guess this union solves my problem,
>
> But let me check it, give me a time..plz
>
>
> On Sat, Jan 2, 2010 at 7:24 PM, Jim Lyons <jlyons4435@stripped> wrote:
>
>> Your table structure makes the SQL a little inelegant, but I'd say this
>> would give you what you seem to want:
>>
>> select Hospital1Code  from tab where Hospital1Code   is not null
>> union
>> select Hospital2Code  from tab where Hospital2Code   is not null
>> union
>> select Hospital3Code  from tab where Hospital3Code   is not null
>>
>> The "union" will eliminate duplicates.  Maybe this would be better
>>
>> select * from (
>>      select Hospital1Code  from tab where Hospital1Code   is not null
>>      union
>>      select Hospital2Code  from tab where Hospital2Code   is not null
>>      union
>>      select Hospital3Code  from tab where Hospital3Code   is not null
>> ) A order by 1
>>
>> Jim
>>
>>
>>
>>
>>
>>
>>
>> On Sat, Jan 2, 2010 at 6:43 AM, bharani kumar <
>> bharanikumariyerphp@stripped> wrote:
>>
>>> Hi
>>>
>>> My fields something like
>>>
>>> hospital1,hospital2,hospital3,patientname,
>>>
>>>
>>> Exact table look like
>>>
>>> PatientName  Hospital1Code  Hospital2Code  Hospital3Code
>>>
>>> Bharani           1234                NULL                 NULL
>>>
>>> Kumar             5678                1234                 NULL
>>>
>>> Senthil            9632                 5675                8524
>>>
>>> John               1234                  4567               8524
>>>
>>>
>>> Can u tell me the query which return output like ,
>>>
>>>
>>> HospitalID
>>>
>>> 1234
>>> 5678
>>> 9632
>>> 5675
>>> 8524
>>> 4567
>>> 8524
>>>
>>> Constraint are
>>>
>>> 1. No Duplicate records,
>>> 2.One single column as Output Result ,
>>>
>>>
>>>
>>> This query purpose is , i have around 1000 patients in my DB,
>>>
>>> Each patient may have one,two,three hospital code,that's y the field are
>>> hospital1,hosptial2,hospital3,
>>>
>>>
>>> i know , i can display all hospital code with unique , but i dont in the
>>> single column , with unique record,
>>>
>>> Can you tell me how to do this ?
>>>
>>>
>>> Thanks
>>>
>>
>>
>>
>> --
>> Jim Lyons
>> Web developer / Database administrator
>> http://www.weblyons.com
>>
>
>
>
> --
> Regards
> B.S.Bharanikumar
> http://php-mysql-jquery.blogspot.com/
>



-- 
Regards
B.S.Bharanikumar
http://php-mysql-jquery.blogspot.com/

Thread
Render row without duplicatesbharani kumar2 Jan
  • Re: Render row without duplicatesBenedikt Schackenberg2 Jan
    • Re: Render row without duplicatesGary Smith2 Jan
  • Re: Render row without duplicatesGary Smith2 Jan
  • Re: Render row without duplicatesJim Lyons2 Jan
    • Re: Render row without duplicatesbharani kumar2 Jan
      • Re: Render row without duplicatesbharani kumar3 Jan
        • Re: Render row without duplicatesJim Lyons3 Jan
          • Re: Render row without duplicatesbharani kumar15 Jan