Help with query - strange behaviour

Hi,

I have a MySql database in which I have 2 columns of which I want to
make a list of all the distinct values from both columns. The 2nd
column can be empty if the first one is filled in ; if the 1st column
is filled in, then the 2nd column is by default also empty.Also the
distinct values should be in alphabetical order

I'm using this query :

$query5="SELECT DISTINCT col1,col2 FROM db WHERE (col1<>\" \") ORDER BY
col1,col2 asc";

But this is not working at all ... I got double entries and the list is
not correctly sorted ...

Has anyone out there some idea on how to get a sorted list of only the
distinct values of both columns ?

A newbie should be very happy with it ...

Thanks in advance !!

Re: Help with query - strange behaviour

Some addition :

the commands used in PHP to see the result are :

while($nt=mysql_fetch_array($result5)){
if ($nt[col1] <> "") {
echo $nt[col1] ."\n
";}
if ($nt[col2] <> "") {
echo $nt[col2] ."\n
";}
}
As a result I fsee some double entries, which is not the idea .... Very
strange behaviour or very stupid programming of me ?

rukkie schreef:

> Hi,
>
> I have a MySql database in which I have 2 columns of which I want to
> make a list of all the distinct values from both columns. The 2nd
> column can be empty if the first one is filled in ; if the 1st column
> is filled in, then the 2nd column is by default also empty.Also the
> distinct values should be in alphabetical order
>
> I'm using this query :
>
> $query5="SELECT DISTINCT col1,col2 FROM db WHERE (col1<>\" \") ORDER BY
> col1,col2 asc";
>
> But this is not working at all ... I got double entries and the list is
> not correctly sorted ...
>
> Has anyone out there some idea on how to get a sorted list of only the
> distinct values of both columns ?
>
> A newbie should be very happy with it ...
>
> Thanks in advance !!

Re: Help with query - strange behaviour

rukkie wrote:
> Some addition :
>
> the commands used in PHP to see the result are :
>
> while($nt=mysql_fetch_array($result5)){
> if ($nt[col1] <> "") {
you should use empty() and not <>


> As a result I fsee some double entries, which is not the idea .... Very
> strange behaviour or very stupid programming of me ?

>> I'm using this query :
>>
>> $query5="SELECT DISTINCT col1,col2 FROM db WHERE (col1<>\" \") ORDER BY
>> col1,col2 asc";

What about:
SELECT DISTINCT CONCAT(col1,col2) FROM db ORDER col1,col2 asc


--

//Aho

Re: Help with query - strange behaviour

Thanks for your suggestion, but I don't think it solves my problem.
Let 's take for example :

+-------+------+
|col1 | col2|
+-------+------+
A
B C
D
C B
A
E F


Then the result of the query should be A,B,C,D,E and F.

With the code I'm using I get, for some reason or another, for example
2 times A, although I only ask for distinct values ....
And moreover, if I change the select query to only SELECT DISTINCT col1
FROM ....I get the A value only once. Using SELECT DISTINCT col1, col2
FROM ... I get the value twice, although A does not appera in the col2

Using the proposed suggestion from you, will give me BC and CB as
distinct (due to the CONCAT), but the values in the cols are the same
.....

I hope this clarifies a bit what the problem is ...

J.O. Aho schreef:

> rukkie wrote:
> > Some addition :
> >
> > the commands used in PHP to see the result are :
> >
> > while($nt=mysql_fetch_array($result5)){
> > if ($nt[col1] <> "") {
> you should use empty() and not <>
>
>
> > As a result I fsee some double entries, which is not the idea .... Very
> > strange behaviour or very stupid programming of me ?
>
> >> I'm using this query :
> >>
> >> $query5="SELECT DISTINCT col1,col2 FROM db WHERE (col1<>\" \") ORDER BY
> >> col1,col2 asc";
>
> What about:
> SELECT DISTINCT CONCAT(col1,col2) FROM db ORDER col1,col2 asc
>
>
> --
>
> //Aho

Re: Help with query - strange behaviour

rukkie wrote:
> Thanks for your suggestion, but I don't think it solves my problem.
> Let 's take for example :
>
> +-------+------+
> |col1 | col2|
> +-------+------+
> A
> B C
> D
> C B
> A
> E F
>
>
> Then the result of the query should be A,B,C,D,E and F.
>
> With the code I'm using I get, for some reason or another, for example
> 2 times A, although I only ask for distinct values ....
> And moreover, if I change the select query to only SELECT DISTINCT col1
> FROM ....I get the A value only once. Using SELECT DISTINCT col1, col2
> FROM ... I get the value twice, although A does not appera in the col2
>
> Using the proposed suggestion from you, will give me BC and CB as
> distinct (due to the CONCAT), but the values in the cols are the same
> ....
>
> I hope this clarifies a bit what the problem is ...
>
> J.O. Aho schreef:
>
> > rukkie wrote:
> > > Some addition :
> > >
> > > the commands used in PHP to see the result are :
> > >
> > > while($nt=mysql_fetch_array($result5)){
> > > if ($nt[col1] <> "") {
> > you should use empty() and not <>
> >
> >
> > > As a result I fsee some double entries, which is not the idea .... Very
> > > strange behaviour or very stupid programming of me ?
> >
> > >> I'm using this query :
> > >>
> > >> $query5="SELECT DISTINCT col1,col2 FROM db WHERE (col1<>\" \") ORDER BY
> > >> col1,col2 asc";
> >
> > What about:
> > SELECT DISTINCT CONCAT(col1,col2) FROM db ORDER col1,col2 asc
> >
> >
> > --
> >
> > //Aho

Well your initial description of your table was nonsense, you said:
"if the 1st column is filled in, then the 2nd column is by default also
empty"
but "also empty" implies that the first column is empty and thus the
second is "also empty". But if the first columbn is "filled in" then
the secong column cannot be "also empty".

Anyway, now that you have shown the actual data and given an example of
the required result, I can tell you that the query you need is:

SELECT col1 FROM db
UNION
SELECT col2 FROM db

Re: Help with query - strange behaviour

Sorry for the wrong description indeed ; it should have been :
if the 1st col is empty then the 2nd col is by default empty
if the 1st col is filled in, then the 2nd col can be empty or filled in


Sorry for that ...

Captain Paralytic schreef:

> rukkie wrote:
> > Thanks for your suggestion, but I don't think it solves my problem.
> > Let 's take for example :
> >
> > +-------+------+
> > |col1 | col2|
> > +-------+------+
> > A
> > B C
> > D
> > C B
> > A
> > E F
> >
> >
> > Then the result of the query should be A,B,C,D,E and F.
> >
> > With the code I'm using I get, for some reason or another, for example
> > 2 times A, although I only ask for distinct values ....
> > And moreover, if I change the select query to only SELECT DISTINCT col1
> > FROM ....I get the A value only once. Using SELECT DISTINCT col1, col2
> > FROM ... I get the value twice, although A does not appera in the col2
> >
> > Using the proposed suggestion from you, will give me BC and CB as
> > distinct (due to the CONCAT), but the values in the cols are the same
> > ....
> >
> > I hope this clarifies a bit what the problem is ...
> >
> > J.O. Aho schreef:
> >
> > > rukkie wrote:
> > > > Some addition :
> > > >
> > > > the commands used in PHP to see the result are :
> > > >
> > > > while($nt=mysql_fetch_array($result5)){
> > > > if ($nt[col1] <> "") {
> > > you should use empty() and not <>
> > >
> > >
> > > > As a result I fsee some double entries, which is not the idea .... Very
> > > > strange behaviour or very stupid programming of me ?
> > >
> > > >> I'm using this query :
> > > >>
> > > >> $query5="SELECT DISTINCT col1,col2 FROM db WHERE (col1<>\" \") ORDER BY
> > > >> col1,col2 asc";
> > >
> > > What about:
> > > SELECT DISTINCT CONCAT(col1,col2) FROM db ORDER col1,col2 asc
> > >
> > >
> > > --
> > >
> > > //Aho
>
> Well your initial description of your table was nonsense, you said:
> "if the 1st column is filled in, then the 2nd column is by default also
> empty"
> but "also empty" implies that the first column is empty and thus the
> second is "also empty". But if the first columbn is "filled in" then
> the secong column cannot be "also empty".
>
> Anyway, now that you have shown the actual data and given an example of
> the required result, I can tell you that the query you need is:
>
> SELECT col1 FROM db
> UNION
> SELECT col2 FROM db

Re: Help with query - strange behaviour

Hi,

the suggestion to use UNION is not working for me, because I get a

"You have an error in your SQL syntax near 'UNION SELECT ...."

Can it be that not all versions support the UNION statement ?

Any other suggestion ?


rukkie schreef:

> Sorry for the wrong description indeed ; it should have been :
> if the 1st col is empty then the 2nd col is by default empty
> if the 1st col is filled in, then the 2nd col can be empty or filled in
>
>
> Sorry for that ...
>
> Captain Paralytic schreef:
>
> > rukkie wrote:
> > > Thanks for your suggestion, but I don't think it solves my problem.
> > > Let 's take for example :
> > >
> > > +-------+------+
> > > |col1 | col2|
> > > +-------+------+
> > > A
> > > B C
> > > D
> > > C B
> > > A
> > > E F
> > >
> > >
> > > Then the result of the query should be A,B,C,D,E and F.
> > >
> > > With the code I'm using I get, for some reason or another, for example
> > > 2 times A, although I only ask for distinct values ....
> > > And moreover, if I change the select query to only SELECT DISTINCT col1
> > > FROM ....I get the A value only once. Using SELECT DISTINCT col1, col2
> > > FROM ... I get the value twice, although A does not appera in the col2
> > >
> > > Using the proposed suggestion from you, will give me BC and CB as
> > > distinct (due to the CONCAT), but the values in the cols are the same
> > > ....
> > >
> > > I hope this clarifies a bit what the problem is ...
> > >
> > > J.O. Aho schreef:
> > >
> > > > rukkie wrote:
> > > > > Some addition :
> > > > >
> > > > > the commands used in PHP to see the result are :
> > > > >
> > > > > while($nt=mysql_fetch_array($result5)){
> > > > > if ($nt[col1] <> "") {
> > > > you should use empty() and not <>
> > > >
> > > >
> > > > > As a result I fsee some double entries, which is not the idea .... Very
> > > > > strange behaviour or very stupid programming of me ?
> > > >
> > > > >> I'm using this query :
> > > > >>
> > > > >> $query5="SELECT DISTINCT col1,col2 FROM db WHERE (col1<>\" \") ORDER BY
> > > > >> col1,col2 asc";
> > > >
> > > > What about:
> > > > SELECT DISTINCT CONCAT(col1,col2) FROM db ORDER col1,col2 asc
> > > >
> > > >
> > > > --
> > > >
> > > > //Aho
> >
> > Well your initial description of your table was nonsense, you said:
> > "if the 1st column is filled in, then the 2nd column is by default also
> > empty"
> > but "also empty" implies that the first column is empty and thus the
> > second is "also empty". But if the first columbn is "filled in" then
> > the secong column cannot be "also empty".
> >
> > Anyway, now that you have shown the actual data and given an example of
> > the required result, I can tell you that the query you need is:
> >
> > SELECT col1 FROM db
> > UNION
> > SELECT col2 FROM db

Re: Help with query - strange behaviour

rukkie wrote:

> Hi,
>
> the suggestion to use UNION is not working for me, because I get a
>
> "You have an error in your SQL syntax near 'UNION SELECT ...."
>
> Can it be that not all versions support the UNION statement ?
>
> Any other suggestion ?
>
>
> rukkie schreef:
>
> > Sorry for the wrong description indeed ; it should have been :
> > if the 1st col is empty then the 2nd col is by default empty
> > if the 1st col is filled in, then the 2nd col can be empty or filled in
> >
> >
> > Sorry for that ...
> >
> > Captain Paralytic schreef:
> >
> > > rukkie wrote:
> > > > Thanks for your suggestion, but I don't think it solves my problem.
> > > > Let 's take for example :
> > > >
> > > > +-------+------+
> > > > |col1 | col2|
> > > > +-------+------+
> > > > A
> > > > B C
> > > > D
> > > > C B
> > > > A
> > > > E F
> > > >
> > > >
> > > > Then the result of the query should be A,B,C,D,E and F.
> > > >
> > > > With the code I'm using I get, for some reason or another, for example
> > > > 2 times A, although I only ask for distinct values ....
> > > > And moreover, if I change the select query to only SELECT DISTINCT col1
> > > > FROM ....I get the A value only once. Using SELECT DISTINCT col1, col2
> > > > FROM ... I get the value twice, although A does not appera in the col2
> > > >
> > > > Using the proposed suggestion from you, will give me BC and CB as
> > > > distinct (due to the CONCAT), but the values in the cols are the same
> > > > ....
> > > >
> > > > I hope this clarifies a bit what the problem is ...
> > > >
> > > > J.O. Aho schreef:
> > > >
> > > > > rukkie wrote:
> > > > > > Some addition :
> > > > > >
> > > > > > the commands used in PHP to see the result are :
> > > > > >
> > > > > > while($nt=mysql_fetch_array($result5)){
> > > > > > if ($nt[col1] <> "") {
> > > > > you should use empty() and not <>
> > > > >
> > > > >
> > > > > > As a result I fsee some double entries, which is not the idea .... Very
> > > > > > strange behaviour or very stupid programming of me ?
> > > > >
> > > > > >> I'm using this query :
> > > > > >>
> > > > > >> $query5="SELECT DISTINCT col1,col2 FROM db WHERE (col1<>\" \") ORDER BY
> > > > > >> col1,col2 asc";
> > > > >
> > > > > What about:
> > > > > SELECT DISTINCT CONCAT(col1,col2) FROM db ORDER col1,col2 asc
> > > > >
> > > > >
> > > > > --
> > > > >
> > > > > //Aho
> > >
> > > Well your initial description of your table was nonsense, you said:
> > > "if the 1st column is filled in, then the 2nd column is by default also
> > > empty"
> > > but "also empty" implies that the first column is empty and thus the
> > > second is "also empty". But if the first columbn is "filled in" then
> > > the secong column cannot be "also empty".
> > >
> > > Anyway, now that you have shown the actual data and given an example of
> > > the required result, I can tell you that the query you need is:
> > >
> > > SELECT col1 FROM db
> > > UNION
> > > SELECT col2 FROM db

maybe, what does the documentation say?