Posted by Captain Paralytic on 12/19/06 16:02

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

[Back to original message]