List:General Discussion« Previous MessageNext Message »
From:bharani kumar Date:January 15 2010 6:54pm
Subject:Re: Render row without duplicates
View as plain text  
select id,cHospital from med_patient where cHospital is not null  union
          select id,cHospital1 from med_patient where cHospital1 is not
null  union
          select id,cHospital2 from med_patient where cHospital2 is not
null  order by 1


throwing error like

*Notice*: Undefined index: cHospital1 in *
F:\wamp\www\mmr-final\admin\all_hospital_list.php* on line *97*

*Notice*: Undefined index: cHospital2 in *
F:\wamp\www\mmr-final\admin\all_hospital_list.php* on line *98*

*Notice*: Undefined index: cHospital1 in *
F:\wamp\www\mmr-final\admin\all_hospital_list.php* on line *97*

*Notice*: Undefined index: cHospital2 in *
F:\wamp\www\mmr-final\admin\all_hospital_list.php* on line *98*

*Notice*: Undefined index: cHospital1 in *
F:\wamp\www\mmr-final\admin\all_hospital_list.php* on line *97*

*Notice*: Undefined index: cHospital2 in *
F:\wamp\www\mmr-final\admin\all_hospital_list.php* on line *98*

*Notice*: Undefined index: cHospital1 in *
F:\wamp\www\mmr-final\admin\all_hospital_list.php* on line *97*

*Notice*: Undefined index: cHospital2 in *
F:\wamp\www\mmr-final\admin\all_hospital_list.php* on line *98*

*Notice*: Undefined index: cHospital1 in *
F:\wamp\www\mmr-final\admin\all_hospital_list.php* on line *97*

*Notice*: Undefined index: cHospital2 in *
F:\wamp\www\mmr-final\admin\all_hospital_list.php* on line *98*

*Notice*: Undefined index: cHospital1 in *
F:\wamp\www\mmr-final\admin\all_hospital_list.php* on line *97*

*Notice*: Undefined index: cHospital2 in *
F:\wamp\www\mmr-final\admin\all_hospital_list.php* on line *98*
 *S.No* *Hospital*  1 1234  2
  3
  4 4321  5
  6 1234

and also i dont want null row result ....


Thanks


On Sun, Jan 3, 2010 at 8:28 PM, Jim Lyons <jlyons4435@stripped> wrote:

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



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