Tracker


		Advanced Search

HOME

TRACKER

MYSQL

SuperbHosting.net have generously sponsored dedicated servers to ensure a reliable and scalable dedicated hosting solution for BigResource.com.

GROUP BY Doesnt Work

i have a table that contains the folowing fields:

some_table:

company_number
something_number
price
date_price_established

for a certain company i need to get all the dates that prices were established. and i dont want any date_price_established to repeat themselfs.

for example, for the following database:

1,1,15.5,1.1.2006
1,1,16,1.1.2006
1,1,17.5,1.1.2006
1,1,16,3.1.2006

the results will be : 1.1.2006, 3.1.2006.

this is what i've tried:

SELECT date_price_established
FROM myDb.some_table
WHERE company_number = 1
GROUP BY date_price_established";

View Complete Forum Thread with Replies

Related Forum Messages:

Function Doesnt Work
Domain Name Now Doesnt Work
Where Clause Doesnt Work
Mysqldump Doesnt Work
Full Text Searsh Doesnt Work
Getting Group By To Work With Order By
WHERE Doesnt Equal
It Doesnt Use My Index!
Its Returning A Value That Doesnt Exist
Full Yearly Weekly Group By Group Report
Group By With Order BY, Or INSERT INTO (SELECT * GROUP BY)?
SELECT Doesnt Select Anything WHERE Doule
GROUP BY With Order Inside Group
Top Record From Each Group In GROUP BY
Bug Work
How Does Sql Work Exactly?
Subselect Does Not Work
Date_add Does Not Work
Getting Log File To Work
How Does Synchronizing Work?
Mysql_connect Does Not Work
Work In MySql
Getting PHP & MySQL To Work Together
Sql Statement Doesn't Work
Cannot Get 3 Where Statement To Work
Join Doesn't Seem Work
Min Max Does Not Work For Me For Timestamp
Why Does This Grant All Does Not Work?
LD_PRELOAD Doesn't Work
Auto_Increment Doesn't Work Any More!
Logging Doesn't Work
WHERE EXISTS Does Not Work.
Getting MySQL To Work..
Mysql 5 Help Doesn't Work
Difference Of Work
Sentence Doesn't Work
How Does 'query_buffer_size' Work In 3.23.56
Now() Date Doesn't Seem To Work!!
Date() Just Won't Work
Why Does This Work In PhpMyAdmin But Not In PHP?
What Query Would Work For This?
SET NULL Doesn't Work
Making ODBC Work
Cannot Get MySQL Query To Work In .asp
A Hash At The End Of A Fieldname Does Not Work
ORDER BY With Match() Doesn't Work
MySQL Doesn't Work In XAMPP
Query Wont Work

Function Doesnt Work
i tried write simple function:

CREATE FUNCTION `getLastNumber`(some_guy INT(50), date DATE) RETURNS float
BEGIN
DECLARE a FLOAT;

SET a = NULL;

SELECT number INTO a FROM numbers p WHERE p.number_owner = some_guy AND p.date_created <= date ORDER BY p.date DESC LIMIT 1;

RETURN a;
END

that would return last inserted number into table for some person. When some_guy has number in table, then is everything ok and it returns last number, but when some_guy does not have row in table it returns: Query OK, -1 rows affected. Pls why? and how to fix it?

View Replies !

Domain Name Now Doesnt Work
I have a software script I had installed on my server that works with a mysql database. It was working fine as far as pulling information from the database under my old domain name, however, I changed to a new domain name as primary on my server and now the database does not pull anything into the template.

I know this is probably simple but not sure how to fix this.

View Replies !

Where Clause Doesnt Work
im creating a website with a mysql database. ive jus got started. im havin a few problems
i have a product table and i execute a simple query but it doesnt seem to give me any results: heres the query:

Select * from product where ProductName = 'Age of Empires III';

the above query doesnt work but this 1 does
Select * from product where ProductName LIKE 'Age%';

View Replies !

Mysqldump Doesnt Work
i try to insert the followind data to mysql database
mysqlimport -u root -p anaktisi2 < phpbb_db_backup.sql

But this doesnt insert anything in the db.

View Replies !

Full Text Searsh Doesnt Work
I am trying to do a Full text search, but I have two problems.

1. If I enter a word that is found under multiple entries, I get no return. But it i search a word tha's only under one enetry, then it works.

2. This query works:
PHP

SELECT * FROM `site` WHERE MATCH (site_name,keywords) AGAINST ('mice') AND `approved`=Ƈ'

, but when I add WITH QUERY EXPANSION, it doesnt work.

PHP

SELECT * FROM `site` WHERE MATCH (site_name,keywords) AGAINST ('mice' WITH QUERY EXPANSION) AND `approved`=Ƈ'

I get this error:

MySQL said:

#1064 - You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'WITH QUERY EXPANSION ) AND `approved` = Ƈ'
LIMIT 0, 30' at

View Replies !

Getting Group By To Work With Order By
Trying to get a simple query going using group by and order by

I need to retrieve the latest record ordered by datetimestamp desc but
grouped by my nodeID column...

my data is thus;

7000802818, 2005-10-10 17:13:43
4, 2005-10-10 17:13:44
4, 2005-10-10 17:13:45
5, 2005-10-10 17:13:4

the first column being the nodeID, the second being the datetimestamp...
I cant seem to get the bleeder to work :(

Can anyone offer any assistance ?

View Replies !

WHERE Doesnt Equal
I am trying to select something my DB where a clause does not equal something but for some reason I cant seem to make it work.

Here is what I have tried.

PHP Code:

SELECT * FROM golf_groups WHERE name!="something"

but this doesnt seem to work. i had done something like this before and thought that I had done it this way, but since this does not work,

View Replies !

It Doesnt Use My Index!
`player_1` mediumint(7) unsigned NOT NULL default Ɔ'
`player_2` mediumint(7) unsigned NOT NULL default Ɔ'
reffering to users' id-number in another table.

I have two indexes:

KEY `one` (`player_1`),
KEY `two` (`player_2`)
The cardinality of them is 6000 and 7000. My primary-key has cardinality 150 000.

When i run this query:

EXPLAIN SELECT *
FROM `gamebase`
WHERE player_1 =1
It uses the "one"-key (doh!).

But now, that I run my next query (using OR), it tells me that one and two are possible keys, but it uses neither, but loops through my entire table "Using where".

EXPLAIN SELECT *
FROM `gamebase`
WHERE player_1 =1
OR player_2 =1

View Replies !

Its Returning A Value That Doesnt Exist
Okay here is the deal. I am running a query to find out if there is an ID in my table that has a case number that matches the one being submitted. If there isnt once its should step into an if statement that would submit a number of records. Otherwise it should jump to the else statement.

I the problem comes from: the query keeps returning "Record id#2" but there is no record matching the case number being submited from the form.

if( isset($_POST['Submit'])){ //Begin If statement logic
$resultcheck = mysql_query("SELECT * FROM submissions WHERE wfmcase = " . $_POST['wfmc']) or die(mysql_error()); //Query string to test if wfmcase was already submitted
echo "Sucess, form varibles have been passed.<br />";
echo "Resultcheck query results: $resultcheck <br />";

if ($resultcheck == ""){ //Checks to see if the query was empty.
echo "There are no duplicate case escalations pending. <br />";

I added the echo statement so that i could see how far it was getting through the script. It dies after printing the varible $resultcheck which gives "Record id#2". It never enters the 2nd if statement because for what ever reason the query has the "record #id2" in it... What is causing this? I have verfied that there is no case that matches the one coming from the form and i get the same error.

View Replies !

Full Yearly Weekly Group By Group Report
I have table called tblTransactions:

TblTransactions
Group | Months | Year | Transaction
A | 3 | 2007 | 45
B | 2 | 2007 | 89
A | 7 | 2007 | 50
A | 8 | 2007 | 34
B | 4 | 2007 | 55
A |12 | 2007 | 10
A | 1 | 2008 | 88
B | 3 | 2008 | 34
B | 5 | 2008 | 70
A | 5 | 2008 | 45
A | 9 | 2008 | 88
B | 7 | 2008 | 99
A |10 | 2008 | 77
A |11 | 2008 | 99

How is the T-SQL to make the result like this (the result will display all of the week (FULL), the target is I can compare week by week between years and group.

A | 1 | 2007 | 0
B | 1 | 2007 | 0
A | 2 | 2007 | 0
B | 2 | 2007 | 89
A | 3 | 2007 | 45
B | 3 | 2007 | 0
A | 4 | 2007 | 0
B | 4 | 2007 | 55
A | 5 | 2007 | 0
B | 5 | 2007 | 0
A | 6 | 2007 | 0
B | 6 | 2007 | 0
A | 7 | 2007 | 50
B | 7 | 2007 | 0
A | 8 | 2007 | 34
B | 8 | 2007 | 0
A | 9 | 2007 | 0
B | 9 | 2007 | 0
A | 10| 2007 | 0
B | 10| 2007 | 0
A | 11| 2007 | 0
B | 11| 2007 | 0
A | 12| 2007 | 10
B | 12| 2007 | 0
A | 1 | 2008 | 88
B | 1 | 2008 | 0
A | 2 | 2008 | 0
B | 2 | 2008 | 0
A | 3 | 2008 | 0
B | 3 | 2008 | 34
A | 4 | 2008 | 0
B | 4 | 2008 | 0
A | 5 | 2008 | 45
B | 5 | 2008 | 70
A | 6 | 2008 | 0
B | 6 | 2008 | 0
A | 7 | 2008 | 0
B | 7 | 2008 | 99
A | 8 | 2008 | 0
B | 8 | 2008 | 0
A | 9 | 2008 | 88
B | 9 | 2008 | 0
A |10 | 2008 | 77
B |10 | 2008 | 0
A |11 | 2008 | 99
B |11 | 2008 | 0
A |12 | 2008 | 0
B |12 | 2008 | 0

View Replies !

Group By With Order BY, Or INSERT INTO (SELECT * GROUP BY)?
I seem to have a few duplicates entries in a very large database.

My database has two identical tables. One is written to on a regular basis (every few seconds) and the other is used for queries, and updated from the write table on a daily basis.

I'm trying to get rid of duplicate entries for the queries, and I thought the most effective way to do this would be to run the query as
Expand|Select|Wrap|Line Numbers

View Replies !

SELECT Doesnt Select Anything WHERE Doule
I am running the next script
SELECT * FROM flcc_Tickets WHERE 'Match No'=1;
but dont get any results although there are lots of resords with 'Match No'=1 in a table. 'Match No' is DOUBLE . if run it with 'Match No'=0 i get all the table.

View Replies !

GROUP BY With Order Inside Group
I have a problem when grouping records - I can't manipulate data inside group. For example,

I have table `images` with fields `name` (name of image, not unique) and `dtadded` (date of image adding). Then, I need to get all images names with distinct names where each name must be latest added name.
Sample:
------------------------------
id, name, dtadded
------------------------------
1 name1 2007-10-15 00:00:00
2 name2 2007-10-15 00:00:00
3 name1 2007-10-16 00:00:00
------------------------------

I need to receive 2 results (for each name)
3 - name1 - 2007-10-16 00:00:00
2 - name2 - 2007-10-15 00:00:00

If I use SQL code:

SELECT images.id, images.dtadded, images.name, count(name)
FROM images
GROUP BY `name`
ORDER BY dtadded DESC

I get results
1 - name1 - 2007-10-15 00:00:00
2 - name2 - 2007-10-15 00:00:00

It groups records with first row in database, but I need last row in table for each name.

Question: How can I order results in side group to get needed results as described above?

View Replies !

Top Record From Each Group In GROUP BY
I never could figure out a good way to do this.
I am doing a group by statement, and want the top record from each group to be returned only. My query is to find the current win/loss streak for each team.
Here is the sql:
SELECT
id,
team_name,
result,
MIN(date) AS startdate,
MAX(date) AS enddate,
COUNT(*) AS games
FROM (
SELECT
t.id,
t.team_name,
sched.date,
CASE sched.draw
WHEN 1 THEN 'draw'
ELSE CASE sched.winner_id
WHEN t.id THEN 'win'
ELSE 'loss'
END
END AS result,
(
SELECT COUNT(*)
FROM fantasy_schedule sched2
WHERE ( CASE sched2.draw
WHEN 1 THEN 'draw'
ELSE CASE sched2.winner_id
WHEN t.id THEN 'win'
ELSE 'loss'
END
END ) <> result
AND sched2.date >= &#55614;&#57157;-01-01' AND sched2.date < &#55614;&#57158;-01-01'
AND sched2.date < sched.date
AND sched2.fantasy_league_id = 6
AND (sched2.fantasy_team1_id = t.id OR sched2.fantasy_team2_id = t.id)
) AS rungroup
FROM fantasy_schedule sched
JOIN fantasy_team t ON t.id = sched.fantasy_team1_id OR t.id = sched.fantasy_team2_id
WHERE sched.last_update < NOW()
AND sched.date >= &#55614;&#57157;-01-01' AND sched.date < &#55614;&#57158;-01-01'
AND sched.fantasy_league_id = 6
) bighonker
GROUP BY id, team_name, result, rungroup
ORDER BY enddate DESC
And a subset of what is returned:

+----+----------------------------+--------+------------+------------+-------+
| id | team_name | result | startdate | enddate | games |
+----+----------------------------+--------+------------+------------+-------+
| 1 | The Macho King | loss | 2005-12-17 | 2005-12-17 | 1 |
| 1 | The Macho King | win | 2005-12-11 | 2005-12-11 | 1 |
| 1 | The Macho King | loss | 2005-11-24 | 2005-12-04 | 2 |
| 1 | The Macho King | win | 2005-11-20 | 2005-11-20 | 1 |
| 1 | The Macho King | loss | 2005-11-13 | 2005-11-13 | 1 |
| 1 | The Macho King | win | 2005-10-30 | 2005-11-06 | 2 |
| 1 | The Macho King | loss | 2005-10-16 | 2005-10-23 | 2 |
| 1 | The Macho King | win | 2005-10-09 | 2005-10-09 | 1 |
| 1 | The Macho King | loss | 2005-10-02 | 2005-10-02 | 1 |
| 1 | The Macho King | win | 2005-09-08 | 2005-09-25 | 3 |
| 2 | General Grievous | loss | 2005-12-17 | 2005-12-17 | 1 |
| 2 | General Grievous | win | 2005-12-11 | 2005-12-11 | 1 |
| 2 | General Grievous | loss | 2005-11-24 | 2005-12-04 | 2 |
| 2 | General Grievous | win | 2005-11-13 | 2005-11-20 | 2 |
| 2 | General Grievous | loss | 2005-11-06 | 2005-11-06 | 1 |
| 2 | General Grievous | win | 2005-10-23 | 2005-10-30 | 2 |
| 2 | General Grievous | loss | 2005-10-16 | 2005-10-16 | 1 |
| 2 | General Grievous | win | 2005-10-02 | 2005-10-09 | 2 |
| 2 | General Grievous | loss | 2005-09-08 | 2005-09-25 | 3 |
| 3 | Ultimate Jarin | win | 2005-11-06 | 2005-12-24 | 8 |
| 3 | Ultimate Jarin | loss | 2005-10-23 | 2005-10-30 | 2 |
| 3 | Ultimate Jarin | win | 2005-10-09 | 2005-10-16 | 2 |
| 3 | Ultimate Jarin | loss | 2005-09-25 | 2005-10-02 | 2 |
| 3 | Ultimate Jarin | win | 2005-09-08 | 2005-09-18 | 2 |
| 4 | The Hulkamaniacs | loss | 2005-12-11 | 2005-12-11 | 1 |
| 4 | The Hulkamaniacs | win | 2005-12-04 | 2005-12-04 | 1 |
| 4 | The Hulkamaniacs | loss | 2005-11-24 | 2005-11-24 | 1 |
| 4 | The Hulkamaniacs | win | 2005-11-06 | 2005-11-20 | 3 |
| 4 | The Hulkamaniacs | loss | 2005-10-30 | 2005-10-30 | 1 |
| 4 | The Hulkamaniacs | win | 2005-10-09 | 2005-10-23 | 3 |
| 4 | The Hulkamaniacs | loss | 2005-09-18 | 2005-10-02 | 3 |
| 4 | The Hulkamaniacs | win | 2005-09-08 | 2005-09-08 | 1 |
| 5 | WHO WANTS TO KISS HOMER? | win | 2005-12-04 | 2005-12-04 | 1 |
| 5 | WHO WANTS TO KISS HOMER? | loss | 2005-11-20 | 2005-11-24 | 2 |
| 5 | WHO WANTS TO KISS HOMER? | win | 2005-11-13 | 2005-11-13 | 1 |
The top record in each group is the current streak. I want only that top record for each teamr returned. I could simply 'order by enddate DESC' and in my php script loop until I have one record per team, but is there any way I can only return the top result for each team in the query itself?

Here is the corresponding table schema:

+-------------------+---------------------+------+-----+---------------------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------------------+---------------------+------+-----+---------------------+----------------+
| id | int(10) unsigned | | PRI | NULL | auto_increment |
| fantasy_league_id | int(10) unsigned | | MUL | 0 | |
| week_num | tinyint(3) unsigned | | MUL | 0 | |
| fantasy_team1_id | int(10) unsigned | | MUL | 0 | |
| fantasy_team2_id | int(10) unsigned | | MUL | 0 | |
| winner_id | int(10) unsigned | YES | | 0 | |
| date | date | | | 0000-00-00 | |
| last_update | datetime | | | 0000-00-00 00:00:00 | |
| draw | tinyint(3) unsigned | | | 0 | |
+-------------------+---------------------+------+-----+---------------------+----------------+

fantasy_team
+---------------------+-----------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------------------+-----------------------+------+-----+---------+----------------+
| id | int(10) unsigned | | PRI | NULL | auto_increment |
| fantasy_league_id | int(10) unsigned | | MUL | 0 | |
| user_id | mediumint(8) unsigned | | MUL | 0 | |
| team_name | char(40) | | | | |
| fantasy_division_id | int(10) unsigned | YES | MUL | NULL |
+---------------------+-----------------------+------+-----+---------+----------------+

View Replies !

Bug Work
I've got this bug http://bugs.mysql.com/bug.php?id=27861. I know they've made a fix and it's going to come out in the next version but is there any way I can fix this myself?

View Replies !

How Does Sql Work Exactly?
Well what exactly does sql do when you create a table for like a login (user) What does the tables and stuff do. Do they just create like a library to store that users information so they can log back in to site or w/e....

View Replies !

Subselect Does Not Work
i am trying to remove values from a list menu if the join table d=
oesnt have keys when a key is selected for instance:
locations
locationID

locations_join
locationID shotlistID

SELECT SQL_NO_CACHE l.locationID , l.location FROM locations l LEFT JOIN lo=
cations_join lj ON l.locationID =3D lj.locationID WHERE l.locationID NOT IN=
(select locationID FROM locations_join WHERE shotlistID IN (5069)) ORDER B=
Y l.location ASC.
so when shotlistID is selected all the keys from the locations_join joined =
to the shotlistID would be remove from the locations list please help, i'm =
trying to do this in one query saving from getting all the keys into an arr=
ay then checking if the values arent in the array when generating the list.

View Replies !

Date_add Does Not Work
I'm using mySQL 5.0, with a database of UNIX timestamps. Can someone explain this to me? This query works great:

SELECT from_unixtime( 1132277574 )

But this one breaks:

SELECT date_add(from_unixtime( 1132277574 ), interval 1 week)

Telling me, oh-so-helpfully, that the error is near "'week)'". (It doesn't work when I use "1 weeks" either.)Does the from_unixtime simply not work with the date_add function, or is there some arcanity to this syntax that I'm missing?

View Replies !

Getting Log File To Work
I am putting
log = "C:Program FilesMySQLMySQL Server 5.0sql.txt"
into my .ini files and mysql wont start.
I cant work out the syntax to tell mysql to ouput all the queries to a log file.

View Replies !

How Does Synchronizing Work?
I have a question;

currently we want to run our central database from two different locations as opposed to one currently. I am trying to figure out how to synchronize the two locations.

My question is: if two (or more) locations create new clients locally, each location will thus create a new client-numbers on their own active operational database. This opens the possibility that each locations will create identical client-numbers; of course being different clients altogether.

View Replies !

Mysql_connect Does Not Work
I have searched through the forum and tried the suggestions that have been made for this issue but I am about ready to give up! I have installed Apache 2.0.52, PHP 5.0.3, and MySQL 4.0.23 on Windows XP. I uncommented extension=php_mysql.dll in the php.ini file and copied libmysql.dll into both C:WindowsSystem32 and C:WindowsSystem.
My phpinfo.php test works fine so I know PHP and Apache are working together. I do not see any MySQL references on that entire page. From previous posts I know I should. Needless to say, mysql_connect(...) does not work, it gives an undefined function error. I apologize if this should have been posted in the PHP forum instead. If it is a PHP issue, I will repost this there.

View Replies !

Work In MySql
This query works fine in SQL Server, will it work in MySql?

SELECT a.total, b.browsertotal, b.browser, (b.browsertotal / a.total) * 100 AS browserpercent
FROM
(SELECT Count(id) As total
FROM MyTable) a,
(SELECT count(id) as browsertotal, browser
FROM MyTable
GROUP BY browser) b

I dont have MySQL and I am trying to help someone out.

View Replies !

Getting PHP & MySQL To Work Together
I am trying to get PHP to write into MySQL database. Right now it does not seem that it is doing such, MySQL doesnt appear when I bring up the PHPInfo() page. I have removed semi-colons before the Mysql.dll extension in php.ini, and I have made sure that PHP is in the windows PATH. What else do I need to do to get this stuff running?

View Replies !

Sql Statement Doesn't Work
When i was using a previous version of mysql (3.something) this sql statement worked fine.

strSQL = "SELECT COUNT(*) as COUNTER FROM prayerrequest where dateexpires <= '" & sDate & "'"

sDate equals todays date.

But after installing the new version (4.1) of mysql this statement no longer works.

I went as far as stepping through all dates and comparing them and it wouldn't work. "1/11/2005" from the database did not equal sDate which was "1/11/2005".. The only way I could get it to work was to use the asp/vb function FormatDateTime on dateexpires and then compare it with sDate.

Does mysql not return a different var type? Is there anyway I can convert dateexpires in the above sql statement so I can compare it with sDate?

View Replies !

Cannot Get 3 Where Statement To Work
I am having a problem with MySQL 5. I cannot get 3 where statements to work with my database. Any two of the three work fine and return records as they are supposed to but when I add a third clause (all clauses are AND) I get absolutely no records at all and no error message. I don't understand what could be causing this and can use any help offered.

SELECT this, that, otherthing FROM myTable WHERE something = "this" AND somethingelse = "that" AND yetsomethingelse > 0 ORDER BY RAND() LIMIT 3;

View Replies !

Join Doesn't Seem Work
I have some issues with a simple query, but I just can't figure out why the query doesn't return expected results.

Here is the situation:

I have a table look like this:

TRANSACTION(UNIQUE_ID(PK), CUSTOMER_ID, EMP_ID, TRANS_TYPE, TRANS_DATE, POSTED_DATE, AMOUNT_CHARGED, AMOUNT_PAID, TRANS_LOC, TRANS_ID_CHARGE, TRANS_ID_PAYMENT)

Some rules for the data in the table:

- The TRANS_TYPE indicates if it is a charge or payment(C charge, P for payment). For a payment, there is a unique TRANS_ID_PAYMENT, and for same transaction, the charge record holds same TRANS ID in the TRANS_ID_CHARGE field.

- The TRANS_LOC is for location where the charge happened. But for a payment record, this field is null. which means, payment records and charge records can't be linked together through location.

- TRANS_DATE is the date the transaction happened. For a payment record, this field is also null.

- A charge record and a payment record for same transaction usually have different POSTED_DATE since they are posted most likely on different days.

- AMOUNT_PAID could be less than AMOUNT_CHARGED for same transaction since customers can have an installment payment plan.

Here is my query:

SELECT DISTINCT
A.CUSTOMER_ID,
A.AMOUNT_PAID
FROM TRANSACTION A
WHERE A.TRANS_TYPE = 'C'
AND A.TRANS_DATE BETWEEN '2007-01-01' AND '2007-09-30
AND A.LOC_ID = 1
UNION ALL
SELECT DISTINCT
A.CUSTOMER_ID,
A.AMOUNT_PAID
FROM TRANSACTION A,
TRANSACTION B
WHERE A.TRANS_TYPE = 'P'
AND B.TRANS_TYPE = 'C'
AND B.TRANS_DATE BETWEEN '2007-01-01' AND '2007-09-30
AND B.LOC_ID = 1
AND A.CUSTMER_ID = B.CUSTOMER_ID
AND A.EMP_ID = B.EMP_ID
AND A.TRANS_ID_PAYMENT = B.TRANS_ID_CHARGE

The purpose of this query is to pair all the charge and payment records for the same transaction that happened between a certain period of time. But the results show that there are some dangling payment records that are not linked to any charge records.

View Replies !

Min Max Does Not Work For Me For Timestamp
I have such a table:

id
timestamp
value
person

regardless or entering this
SELECT id, MIN(timestamp),value FROM table GROUP BY person

or this

SELECT id, MAX(timestamp),value FROM table GROUP BY person

The timestamp value may be properly pulled, yet, the value field is always the same. Meaning, not the value associated wuth the MIN or MAX timestamp value is shown, but the very first for that person. How is that possible?

View Replies !

Why Does This Grant All Does Not Work?
I've created a new user like this:

create user myuser identified by 'xxxxx';
commit;

Now I'd like to give him all privileges to a certain database:

grant all on db210216204.* to 'myuser'@localhost;

Now I get an error message:

ERROR 1133 (42000): Can't find any matching row in the user table

What am I doing wrong? The database exists:

mysql> show databases
-> ;
+--------------------+
| Database |
+--------------------+
| information_schema |
| baka |
| db210216204 |
| mysql |
| test |
+--------------------+
5 rows in set (0.13 sec)

View Replies !

LD_PRELOAD Doesn't Work
LD_PRELOAD doesn't seem to work with /usr/sbin/mysqld. It works for other programs, but when it comes to mysqld, I always get the same error saying that the object cannot be preloaded, so ignored. I checked that mysqld is not a setuid binary. Is mysqld doing something funny or did I miss anything?

View Replies !

Auto_Increment Doesn't Work Any More!
After upgrading from 5.0.11.beta to 5.0.15 on a Windows 32 system, I can't use the auto_increment feature any more; for example, phpMyAdmin gives the following error message: "#1366 - Incorrect integer value: '' for column 'ID' at row 1". Other php clients also (WordPress, MediaWiki...) don't work.

View Replies !

Logging Doesn't Work
I have WinXP, and MySQL 5.0.18 running as a service. I followed the instructions for
setting up general query log file (mysqld --log), and the file was created (Nebo.log),
but it does not display any logs.
This is all I have after executing a few queries:

Nebo.log

mysqld, Version: 5.0.18-log. started with:
Tcp port: 3306 Unix socket: (null)
Time Id Command Argument

Can anyone offer any help?

View Replies !

WHERE EXISTS Does Not Work.
SELECT id,name FROM tcesports_groups WHERE EXISTS (SELECT * FROM tcesports_teams WHERE tcesports_teams.gid=tcesports_groups.id);

i get this:

You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'EXISTS (SELECT * FROM tcesports_teams WHERE tcesports_teams.gid

Strucuture of the both tables:
tcesports_teams
-------------------
gid smallint(6)
img varchar(100)
mod mediumint(9)
pos smallint(6)

tcesports_groups
-------------------
id smallint(5)
name varchar(100)

currently the entries for id or gid are just 2digits long (1-99) so im a bit confused what throws this error. im using mysql 4.0 branch

View Replies !

Getting MySQL To Work..
I downloaded version 4.1.7, opened the manual, followed the instructions, and installed it. Then, when I opened up the "MySQL Server Instance Config Wizard", I set it all up. I press the "Configure" button, and it runs up the point where it creates the Service. Then, it gives this message"Could not start the service MySQL. Error: 0".

I've ran a Google search on the error message, but have found nothing.

I'm running Windows XP. If there's any more information you need about my computer setup, go ahead and ask.

View Replies !

Mysql 5 Help Doesn't Work
when i type "help show;" in mysql 5, nothing shows up. Why can i not see it.?

View Replies !

Difference Of Work
My provider has passed on version 4.1.7 and the inquiry of the following kind has ceased to work for me:

SELECT goods.id, goods.name FROM goods LEFT JOIN category ON goods.id = category.goods AND category.razdel=2 WHERE category.goods IS NULL.

Before all worked correctly, on the version 3.x, what could exchange?
Where a problem?

View Replies !

Sentence Doesn't Work
I'm using mysql 4.0.21 and Toad to run this query. The message i'm receiving is that there is a Syntax error, where?

select a.id_opcion, a.nombre, a.campo, a.visible, b.id_usuario,
if(b.ancho is null, 50, b.ancho) as ancho, b.posicion
from det_rmen as a
left join
(select *
from rmen_usu
where id_usuario = 'generico') as b
on (a.id_opcion = b.id_opcion and a.campo = b.campo)
where (b.id_usuario='generico') or (b.id_usuario is NULL)
order by id_opcion, posicion;

View Replies !

How Does 'query_buffer_size' Work In 3.23.56
I see a variable named 'query_buffer_size' that value is 0 in mysql server 3.23.56. I didn't find relational materials in reference. so I wander if any one could tell me what is this variable for?

View Replies !

Now() Date Doesn't Seem To Work!!
I am using Now() to insert the current date into my date field and everytime it inserts the following value .... 0000-00-00

View Replies !

Date() Just Won't Work
I have this line

while ($qry = mysql_fetch_array($results)) {
// if (strtotime($qry['loginDateTime']) <= (time() + 86400*31)) {
$login .= 'Name: '.$qry["FirstName"].' '.$qry["LastName"].' ('.$qry["JoinDate"]?date("d/m/Y", strtotime($qry["JoinDate"])).') '.$qry["Email"].''.$qry["rep_NBR"].' ('.$qry["State"].')
';
//}
}

And when I run it I receive

[quoteParse error: parse error, unexpected ';' in e:emailMembersPending.php on line 43[/quote]

View Replies !

Why Does This Work In PhpMyAdmin But Not In PHP?
This is driving me absolutely crazy. I am using the following query which works fine in phpMyAdmin in the SQL section

SELECT date, amount, description FROM invoices WHERE unique_key = 48781048
when I enter it like this in PHP it comes up with the error below

$query = "SELECT date, amount, description FROM invoices WHERE unique_key = 48781048";
$result = mysql_query($query) or die (mysql_error());
Unknown column 'unique_key' in 'where clause'

I've even tried using the PHP code that phpMyAdmin generates and that doesn't even work.

View Replies !

What Query Would Work For This?
I have two tables: say Yes and No.

Yes(ID, YesEmail, EmailOwner);
No(ID, NoEmail);

Both YesEmail and NoEmail contain email addresses.

What query can retrieve all the email addresses in Yes table that are not listed in No table? Thanks in advance.

View Replies !

SET NULL Doesn't Work
I have this table:
MySQL
CREATE TABLE `users` (
`id` INT(11) NOT NULL AUTO_INCREMENT,
`login` VARCHAR(20) NOT NULL,
`password` VARCHAR(20) NOT NULL,
`name` VARCHAR(30) NOT NULL,
`email` VARCHAR(30) NOT NULL,
`icq` INT(9) UNSIGNED DEFAULT NULL,
`personal_stream_id` INT(11) NOT NULL,
`TIME` DATETIME NOT NULL,
`url_name` VARCHAR(30) DEFAULT NULL,
`avatar` VARCHAR(20) DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `id` (`id`),
UNIQUE KEY `login` (`login`),
UNIQUE KEY `email` (`email`),
UNIQUE KEY `icq` (`icq`),
CONSTRAINT `users_fk` FOREIGN KEY (`id`) REFERENCES `photos` (`user_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=INNODB DEFAULT CHARSET=latin1;

The constraint is linking this table:

MySQL
CREATE TABLE `photos` (
`id` INT(11) NOT NULL AUTO_INCREMENT,
`name` VARCHAR(20) NOT NULL,
`user_id` INT(11) DEFAULT NULL,
`streams_id` INT(11) NOT NULL,
`description` TEXT,
`hardware_id` INT(11) DEFAULT NULL,
`ratings_id` INT(11) DEFAULT NULL,
`thread_id` INT(11) DEFAULT NULL,
`url_name` VARCHAR(20) DEFAULT NULL,
`views` INT(11) DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `id` (`id`),
KEY `ratings_id` (`ratings_id`),
KEY `user_id` (`user_id`),
KEY `streams_id` (`streams_id`)
) ENGINE=INNODB DEFAULT CHARSET=latin1;

It works, but as you can see, the constraint ondelete action is set to CASCADE, which is not good - it is bad to delete all photos when the user is deleted, I want the database to set user_id value to NULL using the SET NULL action - PHP frontend should display something like "User deleted" then. But when I try to alter the constraint to SET NULL, EMS SQL Manager I'm using shows a very strange error like "Can't create table '.dfk#sql-8e4_4.frm' (errno: 150)". Similiar error is shown when I try to link unsigned int with not unsigned int.

View Replies !

Making ODBC Work
When trying to set up programs (dbQwikSite, Adobe Designer) to work with
ODBC databases, I encounter the following during setup of the programs:
"it connects, but if I set the initial catalog to any database it says:
"Test connection failed because of an error initializing provider.
Catastrophic Failure."

But when I connect with the initial catalog field empty it connects
successfully.How do I fix this problem?

View Replies !

Cannot Get MySQL Query To Work In .asp
I have a MySQL database and in the front-end software I am using to Run MySQL, I have created a query on the tables which runs in the front-end, similar to the MS Access query grid.

I now wish to pull this query on-line using .asp, but I seem to have found out that I can only pull data from tables in MySQL? ....

View Replies !

A Hash At The End Of A Fieldname Does Not Work
My Linux MYSQL 3.23.52 database server is working OK generally.

However, in a database I have a field name that ends with a # (hash UK as
opposed to £ which is a pound UK) i.e. [empnumber#]

If I "select * from emptable" it works fine and the empnumber# field is
listed correctly with all the other fields.

If I "select empnumber# from emptable" I get returned "unknown column
'empnumber' in 'field list'" (#1054) from Access 2000.

If I use Mysql on the server I get similar results.

I cannot change the field names as these are constrained by the fact that
this is not a new design of database.

View Replies !

ORDER BY With Match() Doesn't Work
Something like the following does not sort the results:

select * from properties where match(location) against('"main st"' in boolean
mode) order by location;

The results are not sorted by location. If I sort by some other field not
specified in a match()/against() pair, the sort works.

Shows up in MySQL 4.1 compiled for Windows and Linux.

Is this a known bug, and are there any workarounds that I can try without
source diving?

View Replies !

MySQL Doesn't Work In XAMPP
Ok i downloaded xampp, which has php,apache and mysql all in one, but mysql doesnt work... but ne way when i type my localhost i get the xampp screen which must mean that apache is working fine but what do i do now? im really confused between mysql and apache.

View Replies !

Query Wont Work
PHP Code:

if(mysql_query("UPDATE users SET referer_score=referer_score+1 WHERE userID='$frommysql_referer'"))
{
die(mysql_affected_rows());

im trying to figure out why refer_score isnt being iincremented as im expecting it to. i know that the value of $frommysql_referer is the right value that im expecting it to be. just it wont increment referer_score. and mysql_affected_rows() wont print anythnig to the screen

View Replies !