|
|
Posted by rukkie on 12/19/06 16:32
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
[Back to original message]
|