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
