Posted by strawberry on 12/19/06 17:00

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?

[Back to original message]