pallevillesen

1. create temporary table TEMPT as select * from... where blabl <> \"Unavailable\"; create temporary table TEMPT as SELECT * FROM tableA WHERE version != \'Unavailable"; 2. Do your search on the TEMPT instead... SELECT site, version, Max(date) FROM TEMPT GROUP BY site ORDER BY site 3. Drop TEMPT. Basically create the table you want with the \"Unavailable\" rows excluded... then use this table for searching... P.

There is no difference between: SELECT DISTINCT(manufacturer), register_size FROM processors and SELECT DISTINCT manufacturer, register_size FROM processors They both return distinct rows. P., denmark

You are too lazy! Look at http://www.mysql.com/doc/en/Date_and_time_..._functions.html extract(YEAR_MONTH(date)) is what you\'re looking for.... It will return the date \'2002-07-23\' like 200207 P., denmark

I guess it would be nice to see a small part of the table you want to search for wheels (my guess is, that it\'s called wheels ?) wheelsize is the width of the wheel and offset is on the diameter ? My problem is that if I have an Acura CL 1997 then it looks like 9 different wheels will fit or what ? I do NOT understand how the same car can be in 9 different vehicle groups ? Please clarify. If I come with a car and a make you should have the exact requirements (diameter range and exact size) and then search the wheels table using this, right ? P., denmark

Help please... :?: Try and show us a: select * from tblNews LIMIT 5; Your problem is probably comparing a DATE to a MONTH ? SO yuo should format your tblNews.Newsdate to a month (dependent on format) as well... Probably $news=mysql_query("SELECT * FROM (tblNews LEFT JOIN tblNews_Picture ON tblNews.News_ID = tblNews_Picture.News_ID), tblAuthor WHERE (tblNews.Author_ID = tblAuthor.Author_ID) AND ( MONTH(tblNews.NewsDate) = MONTH(CURDATE()) )"; will do the job... (HINT: if your NewsDate is a varchar - well you just need a number from [1, 12] to compare with the MONTH(CURDATE()), which returns a number [1,12]) P., denmark

Well, it is not possible in a single query to get distinct values from one coloumn (field) and multiple hits from another coloumn. If that is what you want you must make a subselect which is not directly possible, but you may use temporary tables (see other threads). An alternative is to get the not-distinct results and then parse the coloumn you want to be distinct, i.e. sort the rows on the coloumn you want to be distinct and then only grab unique values from this coloumn. Another alternative is to return distinct values first, then for each distinct value do a non-distinct query with this value in the WHERE clause (again using php or perl or some other language). Of all of the above I like temporary tables most or the parsing solution, since several sql queries might be quite slow. If you want us to be more detailed then you should post a more detailed description of your tables, fields and so on. Including your current sql query! I hope this will get you further, P., denmark

SELECT DISTINCT g.groupid, count(m.groupid) FROM groups g LEFT OUTER JOIN members m ON g.groupid = m.groupid GROUP BY m.groupid OUTER JOIN should produce a new BIG table with NULL values where nothing fits in.... Using a normal join as you did omits these rows... P.

Aaaahhhh....... You didn\'t say that you executed mysql code through some obscure (ok, I use it as well) frontend.... IF this frontend make a new connection or something weird - then the temporary tables might disappear for each call/connection.... I would try and login to mysql shell directly and run the query. ... or do it through php yourself... Also... try and keep the TempTable, i.e. do not drop it (just to check). NOTE: I\'m not saying anything bad about Myadmin, I lovet it - but I do get some weird errors when I try and submit complex queries through the \"write your own SQL query\" window.... P.

That is really wierd. Could you post the non-distinct output here ? I.e. a select * from TempTable, I\'m just curious if it\'s mysql or us. P.

Woups.... we\'re crossing eachother.... Are you using php? If so, the 3 statements need to be submitted as 3 queries. If not - note the parentheses have changed a little.... P.

A few (maybe) typos corrected: CREATE TEMPORARY TABLE TempTable AS SELECT images.model_id, images.group_id, images.folder, models.display_name, images.SingleSetCatRef, prices.Price, prices.image_size FROM ( (models INNER JOIN images ON models.id = images.model_id) INNER JOIN prices ON images.SingleSetCatRef = prices.CatRef ) WHERE images.SingleSetCatRef > \'\' LIMIT 200; SELECT DISTINCT * FROM TempTable; DROP TABLE TempTable; P.

Eh... yes that\'s what I mean. Does it work ? P.

That seems really weird! Your sql statement seems fine to me. You could try and store the non distinct data in a temp. table and then select dinstinct * from that table... i.e. 1. create temporary table tempt as select ....... (your select statement).... 2. select distinct * from tempt; 3. drop tempt; I have no clue why it\'s returning row when NOT using DISTINCT. An alternative is to try and remove all of your AS statements - but I\'m really not sure if this could help at all.... P.

You\'re testing if $query is defined... it is! I think you should check $result instead.... EDIT: Woups.... didn\'t read the first lines of your post, sorry.... so use [php:1:a5aeea7a6e] $Query = \"DELETE FROM $TableName WHERE Password = \'$Array[Remove]\'AND id= \'$Array[iDRemove]\'\"; $Result = mysql_db_query ($DBName, $Query, $Link); if ($Result > 0){ print(\"Success....\"); } else { print(\"no\"); } [/php:1:a5aeea7a6e] P.

OK, I\'m not sure if you are able to use a single sql statement, but maybe (my gut feeling is no). Temporary tables are smart, easy and quick. [php:1:cc66c890c2] $temp = mysql_query(\"create temporary table temphomes as select distinct homeID from Occu... WHERE....;\"); $result = mysql_query(\"select homename from temphomes where homename.homeID=temphomes.homeID;\"); $temp = mysql_query(\"drop table temphomes;\"); $temp = \"\"; [/php:1:cc66c890c2] P.

You got it. And about single queries - if they return different number of rows then I think it\'s impossible to do in mysql (because you need subselects). Temporary tables are fine. P., denmark

mysql> SELECT DATE_FORMAT(\'1997-10-04 22:23:00\', \'%Y-%m-%d\'); +------------------------------------------------+ | DATE_FORMAT(\'1997-10-04 22:23:00\', \'%Y-%m-%d\') | +------------------------------------------------+ | 1997-10-04 | +------------------------------------------------+ 1 row in set (0.00 sec) mysql> Use the date_format consistently throughout the query. I think you should read the mysql manual about date functions: http://www.mysql.com/doc/en/Date_and_time_..._functions.html P., denmark

Just to clarify before I start thinking: 1. Select all homes which are vacant in the queried time interval. 2. Keep all these homeIDs. 3. Return all homes names that have their ID in (2). Is this what you want? This is easiest done with a temporary table, which lists all the vacant \'hid\'. Then join this with your home table and voila... If still in trouble - reply here... P., denmark

You may rename your result coloumns by using AS... mysql> select 1+1 as testing; +---------+ | testing | +---------+ | 2 | +---------+ 1 row in set (0.00 sec) You should probably rename the sum(..) since sum is a bad word to have anywhere else than your code... P., denmark

I have it running from PHP now, You should use [php:1:72da5a2f34] $Query1=\"create temporary table pointsList AS select carNum, sum(driverPoints) from raceResults group by carNum\"; $Query=\"SELECT * from pointsList LEFT JOIN racePilotsAssoc ON pointsList.carNum=racePilotsAssoc.carNum LEFT JOIN raceDrivers ON pointsList.carNum=raceDrivers.carNum WHERE racePilotsAssoc.raceNum=$raceNum or racePilotsAssoc.raceNum IS NULL order by driverPoints\"; [/php:1:72da5a2f34] You missed the AS.... I hoped it would be possible to do this in a single php line. Maybe it is... P., denmark

SELECT player.name, c1.name, c2.name FROM player, data, clubs c1, clubs c2 WHERE (player.playerID = data.playerID) AND data.fromClub = c1.ClubID AND data.toClub = c2.ClubID I think this will work. You need to select TWO club names per row, hence you need to select from two copies of the clubs table. Try it, P., denmark

Probably because you\'re opening a new connection each time.. The rand() function is not truly random, so you need to give it a near random seed, i.e. use rand(seedvalue)... You may use something like rand(seconds(curdat())) or some other stuff. If you\'re using php you could take a variable and assign a random number to it and them use that as seed in you query. P., denmark

USE test; CREATE TEMPORARY TABLE dates AS (SELECT date FROM x GROUP BY date DESC LIMIT 3); SELECT names FROM x LEFT JOIN dates d ON x.date=dates.date WHERE x.date IS NOT NULL; DROP TABLE dates; All your wrong names are NULL in the x.date field after the join. Probably this could be done smarter - but it\'s sunday (i.e. no work or thinking). (Smarter solution is to make an inner join, but I\'m too lazy to look up the correct syntaxt for that.) Aargh... ok USE test; CREATE TEMPORARY TABLE dates AS (SELECT date FROM x GROUP BY date DESC LIMIT 3); SELECT names FROM x INNER JOIN dates d ON x.date=dates.date; DROP TABLE dates; The inner join only returns rows that have a dates.date value <> NULL after the join. P., denmark

Ok, so now it\'s working. I\'m using a temporary table which I consider cheating - will try on combining JOINS instead: (UPDATE: tried this - I do NOT think it is possible doing this without this temp. table). test.sql: USE test; CREATE TEMPORARY TABLE exclude AS (SELECT * FROM departments WHERE department = \'Produce\'); SELECT * FROM stores s LEFT JOIN exclude e ON s.id=e.id WHERE e.id IS NULL; DROP TABLE exclude; and just to prove: serine:~/bin% serine:~/bin% mysql -h aspargine -pXXXXX -e\'source ~/test.sql\' +----+--------+------+------------+ | id | store | id | department | +----+--------+------+------------+ | 2 | store2 | NULL | NULL | | 3 | store3 | NULL | NULL | | 5 | store5 | NULL | NULL | +----+--------+------+------------+ serine:~/bin% P.

AAAARRRRRRGHHHH!!!!! Ok., I guess it\'s time for actually creating the database. I will return since I\'m absolutely positive that this will work... somehow... And it\'s friday anyway! P.