List:General Discussion« Previous MessageNext Message »
From:Jim Lyons Date:January 3 2010 2:58pm
Subject:Re: Render row without duplicates
View as plain text  
The sql command does eliminate nulls, that's what the clause "where ... is
not null" does.  It appears you have an application that is rendering the
results so I would check the application to see if it is somehow putting a
null row on the screen.  Or when you copied the SQL into your program you
left out one of the conditions.  Try running the command in a stand-alone
program, like the mysql interactive program on SQLYOG.


Also, the following SQL gives the same result but is a little simpler.  The
derived table is unnecessary:

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
 order by 1

Jim


On Sun, Jan 3, 2010 at 2:50 AM, bharani kumar <bharanikumariyerphp@stripped
> wrote:

> 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/
>



-- 
Jim Lyons
Web developer / Database administrator
http://www.weblyons.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