kickstart

Hi Not sure why you would do that. Should be possible to have a query which will generate a list of dates, and cross join that with the rooms, and left join the result if that with a table of actual bookings. All the best Keith

Hi Keep the apn inside the sub query but specify the columns you require in the SELECTs rather than using SELECT *. All the best Keith

Hi Your code looks about right but not sure what you are expecting it to do. You are storing each rows name as a member of an array (assuming $name is initialised as an array somewhere). You will thus have an array with all the values in it. Same for amount. However I am not sure from the description what exactly is the problem you are having with the code. All the best Keith

Hi You are over writing the queries results set with the first results:- while($res = mysql_fetch_assoc( $res )) Change one of them to a different variable name (and other lines that refer to the one you change). All the best Keith

Hi Not sure but it seems what you want is all the teams in total points order, and within that a list of all the riders. You should be able to do that with a single select. To give you a rough idea:- <?php $result = mysql_query( "SELECT c.team_id, c.teamname, d.rider_name, d.total_points, e.TeamPoints FROM teams c INNER JOIN rider_stats d ON c.team_id = d.team_id INNER JOIN (SELECT a.team_id, SUM(total_points) AS TeamPoints FROM teams a INNER JOIN rider_stats b ON a.team_id = b.team_id GROUP BY a.team_id) e ON c.team_id = e.team_id ORDER BY e.TeamPoints DESC, d.total_points DESC" ) or die("SELECT Error: ".mysql_error()); $CurrTeam = ''; while ($row = mysql_fetch_assoc ($result)) { if ($CurrTeam != $row['team_id']) { echo 'Team '.$row['team_id'].' - '.$row['TeamPoints'].'<br />'; $CurrTeam == $row['team_id']; echo 'Team '.$row['rider_name'].' - '.$row['total_points'].'<br />'; } echo 'Team '.$row['rider_name'].' - '.$row['total_points'].'<br />'; } ?> The above is based on what I think you want and a guess on column names in the tables. It is best to avoid doing selects within a loop. All the best Keith

Hi You aren't outputting UPC or signup either. If you only bring back the relevant fields then you could do something like:- fwrite($file,"'".implode("','",$f)."'\n"); By the way just noticed you are using the same variable name ($f) for both the file and for the array you are retrieving rows into. All the best Keith

Hi What will probably help greatly is some suitable indexes on the tables (ie, on EntryID on ReferAFriend, and on Email on both tables). Best to also only return the columns that you actually want to write out (SELECT * is a useful feature when knocking up quick queries but shouldn't really be used for production code). On the php side you may as well put the carraige return write onto the end of the main write, rather than do 2 separate fwrite statements. All the best Keith

Hi No as the idea is to use an INNER JOIN so non matching records will be dropped. Using a LEFT OUTER JOIN would mean that records would still be brought back when there was no matching record on ReferAFriend. You can try this to remove the duplicates:- SELECT a.* FROM Entries a UNION ALL SELECT a.* FROM Entries a WHERE UPC != 0 UNION ALL SELECT a.* FROM Entries a INNER JOIN ReferAFriend b ON b.EntryID = a.ID INNER JOIN (SELECT DISTINCT Email FROM Entries) c ON b.Email = c.Email Not sure about changing a setting in MySQL, but in effect you are trying to perform 250000 separate selects which will take ages. All the best Keith

Hi Suspect you are not getting 1 record due to it timing out. Try this:- SELECT a.* FROM Entries a UNION ALL SELECT a.* FROM Entries a WHERE UPC != 0 UNION ALL SELECT a.* FROM Entries a INNER JOIN ReferAFriend b ON b.EntryID = a.ID INNER JOIN Entries c ON b.Email = c.Email (this is assuming that Email is unique on the Entries table) All the best Keith

Hi Not sure why you had to change it. The 3rd part of the query would only find any records where there was at least 1 matching record on the ReferAFriend table. With the change you have you are forcing a LOT of subselects which will be very inefficient. However I am unsure what you are trying to do with the change you did. I think I have misunderstood the linking between the 2 tables and how you want to pull rows out. All the best Keith

Hi The line:- FOREIGN KEY (Login_id) references login_detail(Login_id) on update cascade on delete cascade the bit in bold refers to a column on the table you are creating while the bit underlined refers to a column on another table. The table you are creating doesn't have a column called Login_id though. All the best Keith

Hi The table you are creating doesn't have a column called login_id. You need to define which column in the table you are creating refers to the column in the other table. All the best Keith

Hi Right, let me see if I have go this straight. A person will have a record in Entries and 0 to many records on ReferAFriend. What you want output:- 1 record per row on Entries + 1 record per row on Entries which has a UPC + 1 record per row on Entries per record on ReferAFriend If so I think I would use UNION ALL with 3 queries. SELECT a.FirstName, a.LastName, a.Email FROM Entries a UNION ALL SELECT a.FirstName, a.LastName, a.Email FROM Entries a WHERE UPC != 0 UNION ALL SELECT a.FirstName, a.LastName, a.Email FROM Entries a INNER JOIN ReferAFriend b ON a.Email = b.Email However I might still not have got my head around what you want. All the best Keith

Hi That SQL confuses the hell out of me. Can you take a step back and explain what it is you want, and how the 2 tables link together All the best Keith

Hi This might help SELECT * FROM nvcdata as T1 INNER JOIN (select apn, MAX(id) AS MaxId from nvcdata_history GROUP BY apn) T2 ON T1.apn=T2.apn INNER JOIN (select apn, MAX(bid) AS MaxBid from prop GROUP BY apn) T3 ON T2.apn=T3.apn INNER JOIN nvcdata_history T4 ON T2.apn = T4.apn AND T2.MaxId = T4.id INNER JOIN prop T5 ON T2.apn = T5.apn AND T2.MaxBid = T5.bid Note that your original code main problem was that you hadn't given the subselects alias', you had just given the tables within the subselect an alias. However the other issue was that you were selecting various columns in the subselect and using MAX aggregate function, but only having a single column in the GROUP BY clause. Most flavours of SQL will error on this but MySQL will cope, but the values of the other columns will be indeterminate. They will likely not relate to the row that the max id comes from. All the best Keith

Hi Not sure which subselect you are trying to remove. This removes 1 of them and does the job. SELECT Z.id, Z.MaxScore, CASE WHEN Z.MaxScore = Y.Category_1 THEN 'Category_1' WHEN Z.MaxScore = Y.Category_2 THEN 'Category_2' WHEN Z.MaxScore = Y.Category_3 THEN 'Category_3' ELSE 'Error' END AS MaxScoreCategory FROM (SELECT a.id, GREATEST( a.Category_1, a.Category_2, a.Category_3 ) AS MaxScore FROM myTable a )Z INNER JOIN myTable Y ON Z.id = Y.id Removing all of the subselects (but not sure how efficient this will be as repeating a aggregate function) SELECT id, GREATEST(Category_1, Category_2, Category_3) AS MaxScore, CASE WHEN GREATEST(Category_1, Category_2, Category_3) = Category_1 THEN 'Category_1' WHEN GREATEST(Category_1, Category_2, Category_3) = Category_2 THEN 'Category_2' WHEN GREATEST(Category_1, Category_2, Category_3) = Category_3 THEN 'Category_3' ELSE 'Error' END AS MaxScoreCategory FROM myTable However these are both trying to bodge a way round a problem caused by incorrect table design. The scores should be hived off into a separate table. All the best Keith

Hi Something like this would do it SELECT user, COUNT(hits) FROM Table1 INNER JOIN Table2 ON Table1.code = table2.code GROUP BY user All the best Keith

Hi If you do the above then I think you would still need to cast it to be a number. All the best Keith

Hi That is because it is ordering it in alphabetical order. To do what you want you would need to extract the numeric part of the string and sort by that, then the while string within it. All the best Keith

Hi Can't think of a time when it wouldn't be best to use a link table with a many to many relationship. All the best Keith

Hi Can't see anything obvious wrong. But you do seem to be using an old version of php. Also you seem to have put carraige returns into the SQL which are not really useful. <form name="meeting_edit" id="meeting_edit" action="#" method="post"> <table width="90%" rules="ALL" id="meeting_t1" style="border: 1" cellpadding="6"> <tbody> <tr> <th>ID</th> <th>Day</th> <th>Time</th> <th>Name</th> <th>Address</th> <th>Location</th> <th></th> <th></th> </tr> <?php $sql = "SELECT t.* FROM meeting t" . " INNER JOIN" . " (SELECT meetname, date, COUNT(*) FROM meeting" . " GROUP BY meetname, date HAVING COUNT(*) > 1) as X" . " ON t.meetname = X.meetname AND t.date = x.date" . " ORDER BY t.meetname, t.date ASC"; $result2 = mysql_query($sql) or die(mysql_error()); while($row = mysql_fetch_assoc($result2)) { ?> <tr> <td><?php echo $row['id'];?></td> <td><?php echo $row['date'];?></td> <td><?php echo date("g:i a", strtotime($row['time']));?></td> <td><?php echo $row['meetname'];?></td> <td><?php echo $row['address'];?></td> <td><?php echo $row['location'];?></td> <td><a href="?id=<?php echo $row['id'];?>&mode=edit">E</a></td> <td><a href="?id=<?php echo $row['id'];?>&mode=delete">X</a></td> </tr> <?php } ?> </tbody> </table> </form> Give that a try. Should at least give you the error message if it fails. All the best Keith

Hi You should use all the non aggregate fields in the SELECT within the GROUP BY. However MySQL is pretty tolerant of not doing this (most flavours of SQL would just error). Is device_status really a column with a date in it? All the best Keith

Hi Just knocked up a test table and tried it and the SQL should work. Can you post the php code you are trying to now use it in? All the best Keith

Hi The SQL looks OK and normally even with poor indexing I wouldn't expect it to be that slow. My guess would be that there should be more columns used to JOIN the tables together (e, maybe userid between mdl_feedback_completed and mdl_feedback_tracking), and without this you are landing up getting a massive number of records (very easy for it to be in the millions). All the best Keith

Hi Mikosikos idea would work but it would only show you the meetname and meetday data, whereas I presume there are other details on the meeting table that you want to see to make a human decision if it is a duplicate. Getting round that is why your original code used a JOIN. Expanding your original code:- SELECT t.* FROM meeting t INNER JOIN (SELECT meetname, meetday, COUNT(*) FROM meeting GROUP BY meetname, meetday HAVING COUNT(*) > 1) x ON t.meetname = x.meetname AND t.meetday = x.meetday ORDER BY t.meetname, t.meetday All the best Keith