Re: SQL question

Rick, Seems we really dont disagree except for symantecs. In Oracle, you can do this is a single sql statement. In mySQL, and clearly from your example, you have to create a select and loop through the results, drop the values into variables and then output it, which I would consider to be a report. I was wrong in thinking that you would have to do multiple selects as you elegantly show with the LEFT JOIN syntax you used. Very nice. Anyway, my point is that you cannot do it with one sql statement in mySQL as you can in Oracle. Hopefully, nobody takes me for bashing mySQL. I think mySQL is a very nice db but it has nowhere near the power and elegance of Oracle. Also, you have showed me some nice use of PHP which I have not seen before like this: $Int = ( $Interested ) ? 'Yes' : 'No'; Thanks, David Fordham ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ S U M M I T T E C H N O L O G I E S, I N C. http://www.summittech.com dfordham@summittech.com 4500 S Monaco Ste 1827 303.637.4576 Denver, CO 80237 888.833.7360 Toll Free ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ > Addressed to: dfordham@summittech.com > php-general@lists.php.net > chayes@antenna.nl > > ** Reply to note from "David Fordham" <dfordham@summittech.com> Fri, 08 Dec > 2000 16:23:23 -0700 >> >> Using other tools it may not be possible and specifically, it is not >> possible in MYSQL. You will have to use cursor logic and roll through >> a select and drop certain values into variables and do additional >> selects off those variables. Basically, you will need to create a >> report. > > > I disagree... > > > In Mysql: > > > SELECT Name, Category, > SUM( if( Users.UserID = Interest.UserID, 1, 0 )) > AS Interested > FROM Users, Categories LEFT JOIN Interest USING( UserID )) > GROUP BY Name, Category > ORDER BY Name, Category > > > This returns: > > +-------+-----------+------------+ > | Name | Category | Interested | > +-------+-----------+------------+ > | Anna | Bicycles | 0 | > | Anna | Trees | 1 | > | Anna | Windmills | 0 | > | Chris | Bicycles | 1 | > | Chris | Trees | 0 | > | Chris | Windmills | 1 | > | Harry | Bicycles | 0 | > | Harry | Trees | 1 | > | Harry | Windmills | 1 | > +-------+-----------+------------+ > 9 rows in set (0.01 sec) > > > > In PHP you can then do: > > echo "<TABLE>\n"; > > > $OldName = ''; > > while( list( $Name, $Category, $Interested ) > = mysql_fetch_row( $Result )) { > > if( $Name != $OldName ) { > echo "<TR><TD colspan=2><BR><BR>&nbsp;-- $Name > --</TD></TR>\n"; > $OldName = $Name; > } > > $Int = ( $Interested ) ? 'Yes' : 'No'; > > echo > "<TR><TD>$Category</TD><TD>$Int</TD></TR>\n"; > } > > > echo( "</TABLE>\n"; > > > which looks pretty much like the requested result to me... > > > -- Anna -- > Bicycles No > Trees Yes > Windmills No > > -- Chris -- > Bicycles Yes > Trees No > Windmills Yes > > -- Harry -- > Bicycles No > Trees Yes > Windmills Yes > > > No cursors, no multiple selects, and just a simple pass thru the result > to display the data. If you want only one user, add a WHERE clause to > select the user. > > > Notes: > > o The MySQL query is tested, and the resutls are as shown. The > PHP code is off the top of my head, and untested. > > o I changed Categories.Name to Categories.Category to eliminate the > need for aliasing the field names. > > > >> >>> Dear list, >>> >>> I want to make a query with a column saying yes/no depending on interest of >>> a user. >>> >>> Suppose i have three tables: >>> >>> USER (userID, name) >>> 1 Chris >>> 2 Harry >>> 3 Anna >>> >>> CATEGORIES (catID, name) >>> 4 bicycles >>> 5 windmills >>> 20 trees >>> >>> USER_INTEREST (userID, catID) >>> 1 4 >>> 1 5 >>> >>> 2 4 >>> 2 20 >>> >>> 3 20 >>> >>> >>> I would like to get this query result: >>> >>> CATEGORIE.naam |user_2_is_interested >>> --------------------------------- >>> bicycles|yes >>> windmills|yes >>> trees|no >>> >>> As you see i want every categorie in one row (each once) and the next row >>> says whether a user is interested. >>> >>> So i made this query: >>> >>> SELECT DISTINCT CATEGORIE.naam naam, >>> IF (1=1,"yes","no") user_2_is_interested >>> FROM categorie , user_cat >>> >>> As you see my IF condition is now 1=1. Of course i want to see whether the >>> current user ($UID) is interested in this specific categorie. >>> But as soon as i try to adapt the WHERE or the IF i get all sorts of >>> doubles. >>> Help? >>> Chris >>> >>> >>> -------------------------------------------------------------------- >>> -- C.Hayes Droevendaal 35 6708 PB Wageningen the Netherlands -- >>> -------------------------------------------------------------------- >>> >>> >>> >>> -- >>> PHP General Mailing List (http://www.php.net/) >>> To unsubscribe, e-mail: php-general-unsubscribe@lists.php.net >>> For additional commands, e-mail: php-general-help@lists.php.net >>> To contact the list administrators, e-mail: php-list-admin@lists.php.net >>> >>> >>> >> >> >> -- PHP General Mailing List (http://www.php.net/) To unsubscribe, >> e-mail: php-general-unsubscribe@lists.php.net For additional >> commands, e-mail: php-general-help@lists.php.net To contact the list >> administrators, e-mail: php-list-admin@lists.php.net >> >> > > > Rick Widmer > Internet Marketing Specialists > http://www.developersdesk.com >