Render row without duplicates

--00504502cc01d51332047c2dd649
Content-Type: text/plain; charset=ISO-8859-1

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

--00504502cc01d51332047c2dd649--

Re: Render row without duplicates

Am 02.01.2010 13:43, schrieb bharani kumar:
> No Duplicate records,
>
select hospital1code from *yourtable* grop by hospital1

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: Render row without duplicates

bharani kumar wrote:
> Hi
>
> My fields something like
>
> hospital1,hospital2,hospital3,patientname,
>
[...]
> 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 ?
>
Would it be possible to reconsider your table design?

Instead of having the above, have something such as:

Person(ID,Name)

Hospital(ID,Name,Code)

LinkTable(ID,PersonID,HospitalID)

You'd then have something such as:

Person(1,John)
Hospital(800,"Bart's London",1234)
LinkTable(1000,1,800)

You'd then be able to find all of the distinct hospital codes by doing
select distinct code from hospital.

Apologies if this isn't possible.

Gary

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: Render row without duplicates

Benedikt Schackenberg wrote:
> Am 02.01.2010 13:43, schrieb bharani kumar:
>> No Duplicate records,
>>
> select hospital1code from *yourtable* grop by hospital1
>
This won't work as he's also looking for entities in the hospital2code
and hospital3code fields to be returned in the same resultset, but as a
single column. Essentially, hospital1code, hospital2code and
hospital3code need to be merged to a single column, deduped, and then
returned.

Gary

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: Render row without duplicates

--0016e6d77e323b387f047c2ed4f1
Content-Type: text/plain; charset=ISO-8859-1

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

--0016e6d77e323b387f047c2ed4f1--

Re: Render row without duplicates

--0016e64617cc8b3f70047c3260b5
Content-Type: text/plain; charset=ISO-8859-1

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

--0016e64617cc8b3f70047c3260b5--

Re: Render row without duplicates

--00504502b714fcaa28047c3eb226
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable

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 ient&sql_query=3DSELECT+%2A+FROM+%28SELECT+cHospital+FROM+ME D_PATIENT+where=
+cHospital+is+not+null+union+select+cHospital1+from+med_pati ent+where+cHosp=
ital1+is+not+null+union+select+cHospital2+from+med_patient+w here+cHospital2=
+is+not+null+%29A+ORDER+BY+%60A%60.%60cHospital%60+ASC&token =3D160eb1977a91=
a41f90271414c107d1c5>
[image:
Edit] atient&primary_key=3D.%60cHospital%60+%3D+%27%27&clause_is_u nique=3D0&sql_q=
uery=3DSELECT+cHospital2+from+med_patient+&goto=3Dsql.php&to ken=3D160eb1977=
a91a41f90271414c107d1c5>
[image:
Delete] t&sql_query=3DDELETE+FROM+%60medical%60.%60med_patient%60+WH ERE+.%60cHospit=
al%60+%3D+%27%27+LIMIT+1&zero_rows=3DThe+row+has+been+delete d&goto=3Dsql.ph=
p%3Fdb%3Dmedical%26table%3Dmed_patient%26sql_query%3DSELECT% 2BcHospital2%2B=
from%2Bmed_patient%2B%26zero_rows%3DThe%2Brow%2Bhas%2Bbeen%2 Bdeleted%26goto=
%3Dtbl_sql.php%26token%3D160eb1977a91a41f90271414c107d1c5&to ken=3D160eb1977=
a91a41f90271414c107d1c5>
[image: Edit] table=3Dmed_patient&primary_key=3D.%60cHospital%60+%3D+%2712 34%27&clause_is=
_unique=3D0&sql_query=3DSELECT+cHospital2+from+med_patient+& goto=3Dsql.php&=
token=3D160eb1977a91a41f90271414c107d1c5>
[image:
Delete] t&sql_query=3DDELETE+FROM+%60medical%60.%60med_patient%60+WH ERE+.%60cHospit=
al%60+%3D+%271234%27+LIMIT+1&zero_rows=3DThe+row+has+been+de leted&goto=3Dsq=
l.php%3Fdb%3Dmedical%26table%3Dmed_patient%26sql_query%3DSEL ECT%2BcHospital=
2%2Bfrom%2Bmed_patient%2B%26zero_rows%3DThe%2Brow%2Bhas%2Bbe en%2Bdeleted%26=
goto%3Dtbl_sql.php%26token%3D160eb1977a91a41f90271414c107d1c 5&token=3D160eb=
1977a91a41f90271414c107d1c5>
1234 [image:
Edit] atient&primary_key=3D.%60cHospital%60+%3D+%278524%27&clause_ is_unique=3D0&s=
ql_query=3DSELECT+cHospital2+from+med_patient+&goto=3Dsql.ph p&token=3D160eb=
1977a91a41f90271414c107d1c5>
[image:
Delete] t&sql_query=3DDELETE+FROM+%60medical%60.%60med_patient%60+WH ERE+.%60cHospit=
al%60+%3D+%278524%27+LIMIT+1&zero_rows=3DThe+row+has+been+de leted&goto=3Dsq=
l.php%3Fdb%3Dmedical%26table%3Dmed_patient%26sql_query%3DSEL ECT%2BcHospital=
2%2Bfrom%2Bmed_patient%2B%26zero_rows%3DThe%2Brow%2Bhas%2Bbe en%2Bdeleted%26=
goto%3Dtbl_sql.php%26token%3D160eb1977a91a41f90271414c107d1c 5&token=3D160eb=
1977a91a41f90271414c107d1c5>
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@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 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@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 ar=
e
>>> hospital1,hosptial2,hospital3,
>>>
>>>
>>> i know , i can display all hospital code with unique , but i dont in th=
e
>>> 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/
>



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

--00504502b714fcaa28047c3eb226--

Re: Render row without duplicates

--0016e6d7e0a9607859047c43d5ec
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable

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 m
> 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 nul=
l
> union select cHospital2 from med_patient where cHospital2 is not null ) A
> order by 1
>
>
> cHospital atient&sql_query=3DSELECT+%2A+FROM+%28SELECT+cHospital+FROM+ MED_PATIENT+whe=
re+cHospital+is+not+null+union+select+cHospital1+from+med_pa tient+where+cHo=
spital1+is+not+null+union+select+cHospital2+from+med_patient +where+cHospita=
l2+is+not+null+%29A+ORDER+BY+%60A%60.%60cHospital%60+ASC&tok en=3D160eb1977a=
91a41f90271414c107d1c5> [image:
> Edit] _patient&primary_key=3D.%60cHospital%60+%3D+%27%27&clause_is _unique=3D0&sql=
_query=3DSELECT+cHospital2+from+med_patient+&goto=3Dsql.php& token=3D160eb19=
77a91a41f90271414c107d1c5> [image:
> Delete] ent&sql_query=3DDELETE+FROM+%60medical%60.%60med_patient%60+ WHERE+.%60cHosp=
ital%60+%3D+%27%27+LIMIT+1&zero_rows=3DThe+row+has+been+dele ted&goto=3Dsql.=
php%3Fdb%3Dmedical%26table%3Dmed_patient%26sql_query%3DSELEC T%2BcHospital2%=
2Bfrom%2Bmed_patient%2B%26zero_rows%3DThe%2Brow%2Bhas%2Bbeen %2Bdeleted%26go=
to%3Dtbl_sql.php%26token%3D160eb1977a91a41f90271414c107d1c5& token=3D160eb19=
77a91a41f90271414c107d1c5>
> [image: Edit] l&table=3Dmed_patient&primary_key=3D.%60cHospital%60+%3D+%27 1234%27&clause_=
is_unique=3D0&sql_query=3DSELECT+cHospital2+from+med_patient +&goto=3Dsql.ph=
p&token=3D160eb1977a91a41f90271414c107d1c5> [image:
> Delete] ent&sql_query=3DDELETE+FROM+%60medical%60.%60med_patient%60+ WHERE+.%60cHosp=
ital%60+%3D+%271234%27+LIMIT+1&zero_rows=3DThe+row+has+been+ deleted&goto=3D=
sql.php%3Fdb%3Dmedical%26table%3Dmed_patient%26sql_query%3DS ELECT%2BcHospit=
al2%2Bfrom%2Bmed_patient%2B%26zero_rows%3DThe%2Brow%2Bhas%2B been%2Bdeleted%=
26goto%3Dtbl_sql.php%26token%3D160eb1977a91a41f90271414c107d 1c5&token=3D160=
eb1977a91a41f90271414c107d1c5>
> 1234 [image: Edit] ical&table=3Dmed_patient&primary_key=3D.%60cHospital%60+%3D+ %278524%27&clau=
se_is_unique=3D0&sql_query=3DSELECT+cHospital2+from+med_pati ent+&goto=3Dsql=
..php&token=3D160eb1977a91a41f90271414c107d1c5> [image:
> Delete] ent&sql_query=3DDELETE+FROM+%60medical%60.%60med_patient%60+ WHERE+.%60cHosp=
ital%60+%3D+%278524%27+LIMIT+1&zero_rows=3DThe+row+has+been+ deleted&goto=3D=
sql.php%3Fdb%3Dmedical%26table%3Dmed_patient%26sql_query%3DS ELECT%2BcHospit=
al2%2Bfrom%2Bmed_patient%2B%26zero_rows%3DThe%2Brow%2Bhas%2B been%2Bdeleted%=
26goto%3Dtbl_sql.php%26token%3D160eb1977a91a41f90271414c107d 1c5&token=3D160=
eb1977a91a41f90271414c107d1c5>
> 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@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 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@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 a=
re
>>>> hospital1,hosptial2,hospital3,
>>>>
>>>>
>>>> i know , i can display all hospital code with unique , but i dont in t=
he
>>>> 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/
>



--=20
Jim Lyons
Web developer / Database administrator
http://www.weblyons.com

--0016e6d7e0a9607859047c43d5ec--

Re: Render row without duplicates

--00504502b2b53d20bd047d3887fb
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable

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

> The sql command does eliminate nulls, that's what the clause "where ... i=
s
> 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. T=
he
> 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@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 nu=
ll
>> union select cHospital2 from med_patient where cHospital2 is not null ) =
A
>> order by 1
>>
>>
>> cHospital patient&sql_query=3DSELECT+%2A+FROM+%28SELECT+cHospital+FROM +MED_PATIENT+wh=
ere+cHospital+is+not+null+union+select+cHospital1+from+med_p atient+where+cH=
ospital1+is+not+null+union+select+cHospital2+from+med_patien t+where+cHospit=
al2+is+not+null+%29A+ORDER+BY+%60A%60.%60cHospital%60+ASC&to ken=3D160eb1977=
a91a41f90271414c107d1c5> [image:
>> Edit] d_patient&primary_key=3D.%60cHospital%60+%3D+%27%27&clause_i s_unique=3D0&sq=
l_query=3DSELECT+cHospital2+from+med_patient+&goto=3Dsql.php &token=3D160eb1=
977a91a41f90271414c107d1c5> [image:
>> Delete] ient&sql_query=3DDELETE+FROM+%60medical%60.%60med_patient%60 +WHERE+.%60cHos=
pital%60+%3D+%27%27+LIMIT+1&zero_rows=3DThe+row+has+been+del eted&goto=3Dsql=
..php%3Fdb%3Dmedical%26table%3Dmed_patient%26sql_query%3DSEL ECT%2BcHospital2=
%2Bfrom%2Bmed_patient%2B%26zero_rows%3DThe%2Brow%2Bhas%2Bbee n%2Bdeleted%26g=
oto%3Dtbl_sql.php%26token%3D160eb1977a91a41f90271414c107d1c5 &token=3D160eb1=
977a91a41f90271414c107d1c5>
>> [image: Edit] al&table=3Dmed_patient&primary_key=3D.%60cHospital%60+%3D+%2 71234%27&clause=
_is_unique=3D0&sql_query=3DSELECT+cHospital2+from+med_patien t+&goto=3Dsql.p=
hp&token=3D160eb1977a91a41f90271414c107d1c5> [image:
>> Delete] ient&sql_query=3DDELETE+FROM+%60medical%60.%60med_patient%60 +WHERE+.%60cHos=
pital%60+%3D+%271234%27+LIMIT+1&zero_rows=3DThe+row+has+been +deleted&goto=
=3Dsql.php%3Fdb%3Dmedical%26table%3Dmed_patient%26sql_query% 3DSELECT%2BcHos=
pital2%2Bfrom%2Bmed_patient%2B%26zero_rows%3DThe%2Brow%2Bhas %2Bbeen%2Bdelet=
ed%26goto%3Dtbl_sql.php%26token%3D160eb1977a91a41f90271414c1 07d1c5&token=3D=
160eb1977a91a41f90271414c107d1c5>
>> 1234 [image: Edit] dical&table=3Dmed_patient&primary_key=3D.%60cHospital%60+%3D +%278524%27&cla=
use_is_unique=3D0&sql_query=3DSELECT+cHospital2+from+med_pat ient+&goto=3Dsq=
l.php&token=3D160eb1977a91a41f90271414c107d1c5> [image:
>> Delete] ient&sql_query=3DDELETE+FROM+%60medical%60.%60med_patient%60 +WHERE+.%60cHos=
pital%60+%3D+%278524%27+LIMIT+1&zero_rows=3DThe+row+has+been +deleted&goto=
=3Dsql.php%3Fdb%3Dmedical%26table%3Dmed_patient%26sql_query% 3DSELECT%2BcHos=
pital2%2Bfrom%2Bmed_patient%2B%26zero_rows%3DThe%2Brow%2Bhas %2Bbeen%2Bdelet=
ed%26goto%3Dtbl_sql.php%26token%3D160eb1977a91a41f90271414c1 07d1c5&token=3D=
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@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 wrote:
>>>
>>>> Your table structure makes the SQL a little inelegant, but I'd say thi=
s
>>>> 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@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
>



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

--00504502b2b53d20bd047d3887fb--