 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussion GroupsGeneralPHPASPPerlColdFusionFlashHTML, CSS, ScriptsBrowsers |
Webmaster Forum / PHP / Database Access / December 2006Help 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 !! 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 <br>";} if ($nt[col2] <> "") { echo $nt[col2] ."\n <br>";} } 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, > [quoted text clipped - 18 lines] > > Thanks in advance !! > 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  Signature//Aho 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: > > Some addition : > > [quoted text clipped - 14 lines] > What about: > SELECT DISTINCT CONCAT(col1,col2) FROM db ORDER col1,col2 asc > Thanks for your suggestion, but I don't think it solves my problem. > Let 's take for example : [quoted text clipped - 47 lines] > > > > //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 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: > > Thanks for your suggestion, but I don't think it solves my problem. > > Let 's take for example : [quoted text clipped - 61 lines] > UNION > SELECT col2 FROM db 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 [quoted text clipped - 69 lines] > > UNION > > SELECT col2 FROM db > Hi, > [quoted text clipped - 81 lines] > > > UNION > > > SELECT col2 FROM db maybe, what does the documentation say? |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2010 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.