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 <jlyons4...@gmail.com> 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 <
> bharanikumariyer...@gmail.com> 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 <
>> bharanikumariyer...@gmail.com> 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 <jlyons4...@gmail.com> 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 <
>>>> bharanikumariyer...@gmail.com> 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/
