MYSQL :: Simple Question About UNIX

Unix_timestamp()
I have columns called repeat_date and repeat_month and I would like to
use the info within them in my WHERE clause as part of a
UNIX_TIMESTAMP().

Essentially, I would like to say something like:

SELECT * FROM table WHERE
UNIX_TIMESTAMP(repeat_date-repeat_month-$curYear 0:00:00) > $date_start
AND UNIX_TIMESTAMP(repeat_date-repeat_month-$curYear 0:00:00) <
$date_end

The variables $curYear, $date_start and $date_end will have been set
previously with PHP.

I'd like to use the information in each row within the
UNIX_TIMESTAMP().
Is this possible? If so, how would I do it?

Unix_timestamp()
I have several tables where most have a timestamp column. Normally I set up a varchar field, then add a timestamp to the field with mktime() via PHP. I've been reading about mysql's UNIX_TIMESTAMP(), and it says that (just like PHP) it is the seconds since 01/01/1970. I tested an example from the manual where it shows:

875996580 = 1997-10-04 22:23:00

but when I test it in php, It comes up 6 hours off:

875996580 = 1997-10-04 16:23:00

Whats going on here? It seems that I cannot trust mysqls' interperetion of the unix timestamp, and will have to rely on PHP to handle date searches.

Newb: Can You Do This With UNIX_TIMESTAMP?
Is this valid:

UNIX_TIMESTAMP(DATE_ADD(now(), INTERVAL -15 MINUTE)) ?

Using Unix_timestamp Function
Can anyone see anything wrong with the way I am using the MySQL unix_timestamp function in this snippet from a query?

$importdata="INSERT INTO import
(date, gluc, humilog, regular, lantus, carbs)
VALUES (unix_timestamp('2004-08-01 05:04:00'),152,02.0,00.0,00.0,00.0)

The table column where I am inserting this was created as:

"date timestamp not null"

I am getting an incorrect timestamp value (20001228122240)that converts to "Mon, 18 Jan 2038 20:14:07 -0700" in PHP. As far as I can see, my usage of unix_timestamp is the same as that in the MySQL manual.

Getting Records When Using UNIX_TIMESTAMP
I'm currently using this query:

SELECT poker_room,game_type,limit_type,UNIX_TIMESTAMP(date_time),date_time,buyin_desc,description,url FROM poker_tourneys ORDER BY date_time ASC LIMIT 0, $nrpp

My question is... can I pull all records where the converted "date_time" is > a certain time?

I tried the below but it didn't seem to work:

$time = time();

SELECT poker_room,game_type,limit_type,UNIX_TIMESTAMP(date_time),date_time,buyin_desc,description,url FROM poker_tourneys WHERE UNIX_TIMESTAMP(date_time) > '$time' ORDER BY date_time ASC LIMIT 0, $nrpp

Btw... I'm using PHP as my server-side language.

MySQL Unix_timestamp
I have a MySQL query used to compute the total time a device is within a expected location. The loglocations table contains a history of every device's location history within the DB. the starttime/endtime are datetime fields which represent how long a device is in a partcular location for that time duration. The macaddress is the ID of the device and is comes from the loghistories table. Both tables are linked by the historyId and uuid respectively:

SELECT (SUM(UNIX_TIMESTAMP(ll.endtime) - UNIX_TIMESTAMP(ll.starttime)) AS DEVICE_TIME FROM loghistories lh INNER JOIN loglocations ll ON (lh.uuid = ll.historyId) WHERE lh.macaddress = ? and ll.locationName = ? and ll.starttime >= ? and ll.endtime <= ?

all the ? marks represent user-inserted information. This query works fine except for in one instance: Some devices have a 'NULL' endtime because the endtime of the device has yet to be determined (still in same location). This causes the sum section of the query to equate to NULL which is of no use to me...is there any way I could somehow append the current datetime value (using NOW() function for instance) for all devices which contain a 'NULL' endtime??

What Is Wrong Here With FROM_UNIXTIME + UNIX_TIMESTAMP
I am using gentoo-AMD64 and the MYSQL server version provided by gentoo is currently 4.0.24

When I am inserting a DATETIME which is in that magic gap during the change from summer time to normal time, MySQL adds a magic hour. My bug, or a MySQL bug?

This is what I am doing:
######################################
mysql> create table a (f1 DATETIME);
Query OK, 0 rows affected (0.07 sec)

mysql> insert into a VALUES (FROM_UNIXTIME(1130631910));
Query OK, 1 row affected (0.00 sec)

mysql> select * from a;
+---------------------+
| f1 |
+---------------------+
| 2005-10-30 02:25:10 |
+---------------------+
1 row in set (0.00 sec)

mysql> select UNIX_TIMESTAMP(f1) from a;
+--------------------+
| UNIX_TIMESTAMP(f1) |
+--------------------+
| 1130635510 |
+--------------------+
1 row in set (0.00 sec)
######################################

I inserted 1130631910 and later retrieve 1130635510?

Convert From UNIX_TIMESTAMP To Formatted Date
I'm holding a UNIX_TIMESTAMP (say, 1110715984) and I would like a way of formatting this in MySql into a date and time.

Convert Unix_Timestamp To DateTime In A Whole Column
I have an old table with a column:

datum varchar(30)
with Unix Timestamps in it.

Now I want to convert it into a new column with DateTime (0000-00-00 00:00:00).

Only have found the syntax to make it the other direction
UPDATE tabelle SET tmpDate = UNIX_TIMESTAMP(DATE_FORMAT(myDateTime,'%Y-%m-%d %T'))
WHERE myDateTime IS NOT NULL

Please can you give me the syntax to convert the whole column from Unix Timestamp to DateTime without loosing my dates.

Can You Use "UNIX_TIMESTAMP" Outside The Mysql Query?
Hey guys. I was using this code:

mysql_connect($dbhost, $dbuser, $dbpassword) or die("Couldn't connect to server.");
@mysql_select_db($dbname) or die("Couldn't connect to database.");

$q="SELECT poker_room,game_type,limit_type,UNIX_TIMESTAMP(date_time),date_time,buyin_desc,description,url FROM poker_tourneys ORDER BY date_time ASC";
$sql=mysql_query($q);
$num=mysql_num_rows($sql);

mysql_close();

Then using this code to get the results:

while (list($poker_room,$game_type,$limit_type,$dt,$dt2,$buyin_desc,$description,$url)=mysql_fetch_array($ sql)) {
// Blah
}

But I need to only display certain results so depending on the page they are on.. so I was wanting to get each results values with the "mysql_result()" funtion; but I can't figure out how to get the UNIX_TIMESTAMP to work this way.....

Simple (?) AND
SELECT * FROM table WHERE (date_col >= '2005-09-26' AND time_col >= '18:00:00') AND (date_col <= '2005-09-27' AND time_col <= '18:00:00')

I get two results from this query, one for 18:00:00 from each day, when what I want to get is from 18:00:00 and onwards for the first day and 18:00:00 and before for the second day.Pretty much my query is behaving like this...

SELECT * FROM table WHERE (date_col >= '2005-09-26' AND date_col <= '2005-09-27') AND time_col = '18:00:00')

Another option is to combine date_col and time_col into a query like...

SELECT * FROM table WHERE date_col + time_col >= '2005-09-26 18:00:00' AND date_col + time_col <= '2005-09-27' 18:00:00'

Less messy but I'm not sure how to combine the columns for the query as above.

Probably Simple Question
I'm trying to insert a list of around 700 values into a simple table, which is just emid (auto-incrementing index) and emotion (varchar). The code I'm using (inefficient I know but it only needs to be run once) is as follows...

$i=0;FOREACH ($emotions AS $emotion) { IF (strlen($emotion)>2) { $list[$i]=ucfirst(rtrim($emotion)); $i++; }}$list=array_unique($list);FOREACH ($list AS $key=>$value) {$query="INSERT INTO emotions(emid,emotion) VALUES("","" . $value . "")";$result=mysql_query($query);}

but it won't work. If I echo out the queries then they look like this...

INSERT INTO emotions(emid,emotion) VALUES("","Abandoned")

If I then paste them straight into phpMyAdmin then they work fine, but running the script doesn't work. Could it be an upper limit placed by my host on query number/page? Or am I missing something simple?

Simple Subquery
I know the basics of sql but i'm really bad at the joins and everything, so i hope someone can help me out with this.

items:

+-------+-------+
| it_id | name |
+-------+-------+
| 1 | item1 |
| 2 | item2 |
| 3 | item3 |
+-------+-------+
subitems:

+----+------+-------+
| id | name | it_id |
+----+------+-------+
| 1 | a | 1 |
| 2 | b | 1 |
| 3 | c | 1 |
| 4 | d | 1 |
+----+------+-------+
now i tried this in my php

PHP

$result = mysql_query("SELECT name FROM subitems WHERE it_id in (SELECT it_id from items WHERE name='$name'");
echo '<select name="subitems" class="list">'
while ($line = mysql_fetch_array($result , MYSQL_ASSOC)) {
   foreach ($line as $subitem) {
      echo '<option value="'.$subitem.'">'.$subitem.'</option>'
   }
}
echo '</select>'

Simple SQL Query
I was wondering if anyone could help me with this SQL query?

I'm making a football prediction website and I want to be able to display the weekly fixtures in a GridView so the admin can edit, insert, etc.

This is a simple version of the two tables that I have: Fixtures and Clubs...

CREATE TABLE Fixtures
(
MatchID Int PRIMARY KEY,
HomeClubID Int,
AwayClubID Int
)

CREATE TABLE Clubs
(
ClubID Int PRIMARY KEY,
Name Nvarchar(50)
)

.. where HomeClubID and AwayClubID are actually foreign keys to ClubID.

My Problem is that I'd like to create a query that will show each fixture with both the names of the home and away clubs on the same row, eg:

| MatchID | Name | Name |
|--------------------------------|
| 1 | Arsenal | Man Utd |
|--------------------------------|
| 2 | Liverpool | Chelsea |

... etc.

Simple Mirroring. Possible?
Two servers in same datacenter. Connected with a private line.
ServerA and ServerB
Both Linux, both have mysql 5.0 installed.

Both have an account mywebsite.com

mywebsite.com in serverA have a database named dbA.

Depends on the location, the visitor can be connected to mywebsite.com in serverA or in serverB.

Goal:
Regardless which server the user connects, I want to populate/update the dbA on serverA.

Challenge:
I cannot change the "host" in connection parameter in mywebsite.com in serverB. So it must stay as "localhost" but data must be written into/read from serverA

I need this running for 2 or 3 days max. Is this possible? at all?

Simple IF THEN Statement
Ok all i'm trying to do it left join a table on another and order by the data in the joined table. Meaning that since it's left joined there is the possability that there will be no data in that table. If not then i'd like to make the order by field contain a number in order to preserve ordering.

The entries with no information from table B would end up at the bottom of the ordered list.

Table A
id | someInfo | moreInfo |
1 blah blah
2 Foo Bar

Table B
id | tableA_id | someInfo | order_column |
1 2 Weee 1

Now with the above if i call and join the tables with the following,

select tableA.someInfo, tableA.moreInfo, tableB.someInfo
from tableA
left outer join tableB on tableA.id=tableB.tableA_id
order by tableB.order_column ASC;

With this i get the following order,

blah
Foo

Where blah has no entry in tableB. Since there is no entry in tableB i need to it end up at the bottom of the list not the top. But i understand that since there is no entry it is showing the correct order since nothing in ascending order is higher then 1 (similar to 0 being the first number).

But i need to be able to throw an if statement in there, something like the following,

IF tableB.order_column < 1 THEN tableB.order_column = 9999999

The problem is that i'm not sure how to add this to the above query. I mean i've never added an if statement to a direct query before and am unable to create or work with views/stored procs on this server.

Help With Simple Query
I know this is easy to accomplish, but my brain is fried and I can't figure it out.

I have a few markets (areaID) that vendors belong to. Within these markets, vendors are assigned categories (catID).

I am trying to do a SELECT on the vendors table to find out the total number of vendors to a particular category, within a particular market. So essentially, I want to know the number of vendors within each category within each market. Does that make sense?

Can Anyone Help Me With This Simple Query?
In my database. I want to pull out one record:

Where the date I wish to publish a story where the published_web_date(date) is less or equal to now
and
The published_web_time(time) is less or equal to now

However, using the below query data is nomatter what the published_web_date is if the published_web_time has not elapsed then nothing is shown.

select * from cms_stories
WHERE section = 'news'
AND published_web_date <= NOW()
AND published_web_time <= NOW()
ORDER BY story_id
DESC LIMIT 1";

A Simple Query?
In table "vb_user" I want to pull the data in field "username" and copy that username in another table called "vb_userfield" in a field called "field5". Both tables share the field "userid" which is how the relation is made. I know this is probably very simple but man... I just can't seem to pull it off.

I've got about 500 members and I really want to populate that field "field5" with thier username without having every one of them doing it manually.

A Simple Update
What is wrong with my code?

UPDATE TABLE Ahs_cat SET Ahs_cat.Master_ID to Ahs_master.ID WHERE Ahs_cat.ID = Ahs_master.cat

I get an error every time, but to me it looks like how the manual is telling me to do it.

Simple Grouping
i need to select the latest 8 records (newest timestamp) from each of the two categories and 4 subcategories. so that the final result looks like this:

category subcategory price time
--------- ------------ ----- ---------------------
1 dogde 1.34 2005-09-08 08:13:07
1 ford 3.50 2005-09-08 10:20:12
1 toyota 2.43 2005-09-08 11:40:06
1 honda 3.00 2005-09-07 14:01:11
2 dogde 1.00 2005-09-01 10:37:00
2 ford 9.20 2005-09-08 08:23:43
2 toyota 2.55 2005-09-05 18:51:37
2 honda 6.25 2005-09-07 11:54:25

please note: there are only 2 possible categories and only 4 possible subcategories. the price should reflect that particular sale (indicated by timestamp).

Simple Counter
I figured id try and make a simple little counter for my site using php/sql, but i cant seem to get it to work, its buggin the crap out of me, im fairly new to Sql and am just trying things out here and there. This is what I have for script so far. Code:

Simple Trigger
im trying to create a simple delete trigger. when a row is deleted from one table, the trigger will use the id from that row and delete a row with the same id value in another table. ive tried a few things, and i keep getting errors with my syntax.

Simple Join?
I need to find all the groupID's that employee ID=123 doesn't belong to
given the following table structure:

EmployeeGroup
------------------------
groupID
groupName

EmployeeGroupMap
----------------------------
groupID
employeeID

Ideas?

Simple Select
MySQL v4.1.12 (Win32)

I want to perform a select using the values of a column as a comma seperated list for use in a IN clause see example below

SELECT * FROM mytable where 100 IN (mytable.lookup)

where mytable.lookup (which is a VARCHAR) contains values such as:-

a) 100,10,0,1000,7272
b) NULL
c) 2,199,20,100
d) 199,100,2

it only returns rows when the first entry in the column is the value being searched for i.e. a) gets returned but c) and d) are not...

Is this technique not possible? Changing the column type to a SET datatype results in no columns being returned.

Simple Trigger
I have an auto_incremented column and would like to have a trigger that takes this value, adds an 'A' (or some other character, thats the next mission ;) to it and puts it into another column in the same table.

I thought that one was easy, but no matter what i tries I get errors, like "Truncated incorrect double value" and "Missing Lock" etc etc

Simple Trigger
I have 'user' table with 'id' attribute (amone other attibutes) and 'test' table with same 'id' attribute. I am trying to create a trigger that will copy 'id' attribute value of every inserted row in 'user' database to 'id' row of my second 'test' table. I tried with no luck with following code:

CREATE TRIGGER test AFTER INSERT on users
FOR EACH ROW BEGIN
INSERT INTO test SET test.id=user.id;
END;

Simple Login
this is so easy but i can't seem to ascertain what is wrong. i'm making a 'random quote' database. this is my php (ver 4.3.11);

<?php
$link = mysql_connect('localhost', 'root', 'calvin');
if (!$link) {
die('Could not connect to server : ' . mysql_error());
}

$db_selected = mysql_select_db('quotes', $link);
if (!$db_selected) {
die ('Could not connect to database : ' . mysql_error());
}
?>

why do i get the following er msg?

Warning</b>: mysql_connect(): Client does not support authentication protocol requested by server; consider upgrading MySQL client in <b>C:ArtizanrookhavenBHconnection.php</b> on line <b>2</b><br />

Could not connect to server : Client does not support authentication protocol requested by server; consider upgrading MySQL client

Simple Update
"#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 ''LR1' SET LEFT='1' WHERE ID='1'' at line 1"

I try the very simple (with all integers):

UPDATE LR1 SET LEFT=1 WHERE ID=1 or
UPDATE LR1 SET LEFT=1 WHERE ID='1' or
UPDATE LR1 SET LEFT='1' WHERE ID='1' or
UPDATE 'LR10 SET LEFT='1' WHERE ID='1'

Simple Install
i want to install mysql-max version from the tar file i downloaded. i wish to use it sql node in cluster. so i followed the instructions in chap 15 mysql ref manual.
but could not find specific instructions to install sql node. and how to configure the my.cnf .

Simple Syntax
I have a huge database table design that is all integrated into four areas...very complex. I paid a coder to do the sql set up for me, but now a year later, I need to upgrade from phpBB to vBulletin (of course right)...

So I need to quickly find the table columns named user_id to make sure I have them all accounted for....

So I would like to do this

SELECT * FROM * WHERE *.user_id > 0

But it doesn't seem to work. The asterisk I guess only works for tables full names, as opposed to ALL tables that have a user_id column...

Is there a simpler way to do this than just looking at all 216 tables for user_id.

Simple Join
I have a simple join question. I need to optimize for speed. If I have two tables with a one-to-many relationship, say, [Invoice] and [Items]. And say they are joined by [Invoice]'s primary key of type AUTO_INCREMENT. Here's the question:

When I add a record in both [Invoice] and [Items], how does mysql "know" the value of the [Invoice] primary key so that it can properly insert that value in the appropriate field of [Items]?

Do I have to do a separate SELECT after I insert item in [Invoice] so I can "know" what join value to put in [Items]?

Simple Sql Problem
My query:

'INSERT INTO '.$db_prefix."config (conf_name, conf_value) VALUES('$conf_name', $conf_value)"

is returning:

You have an error in your SQL syntax near ')' at line 1 (Errno: 1064)

Simple Error
I keep getting a syntax error, when trying to run this query (perl script):

SELECT * FROM temp_hits WHERE url NOT IN SELECT * FROM hits

Ive tried with brackets around the last SELECT statement

(Essentially, the tables hits, new_hits and temp_hits hold the same columns,
what I want to do is extract any rows from new_hits, where the url field is
not found in the hits table...).

Simple Join
I need to find all the groupID's that employee ID=123 doesn't belong to
given the following table structure:

EmployeeGroup
------------------------
groupID
groupName

EmployeeGroupMap
----------------------------
groupID
employeeID.

Simple Yield From DB
When new members sign up for my website they are placed as the last line in my table in my database - I suppose you know how that works - they are put as the last entry so that the newest member is at the bottom.

I need some kind of code that will get the username, which is one col in the table.. it should be a rather simple code...I don't know how to explain it better though.. hehe... tell me what you need to solve my problem.
//deaf_digit

Simple Join
I'm trying to join the vendor table whenever the equipment.vendor is equal to vendors.id. The query just shows the equipment table values and nothing from the vendors table. Did I execute the query correct?

SELECT equipment.itemid, equipment.description, equipment.vendor, equipment.reorderlevel, equipment.tonhand
FROM equipment
LEFT JOIN vendors ON vendors.id =equipment.vendor
WHERE buyer = 3

Simple Incrementing
I have a field within my mysql database, this field, ContractNo, is the primary key. I have auto-increment set up to run on this field. However, it only starts from 0. The field is of type int. The value I wish to start the increment from is 24001000. Is it possible to assign this value as a starter value for my field to increment +1 everytime. I'm using navicat and everytime I place 24000000 as the default value, it always reverts it back to being blank.

Simple SELECT
This is probably a simple select for most users here but I can't write it. I
need to select rows from one table which fulfills two WHERE options. The
second WHERE needs to have two values.
Something like this:
SELECT * FROM x_table WHERE (id = '$team') AND (type = 'Game')

but I need to select also second type. For example 'Practice'. So the 'id'
needs to be just one certain id number but I want to select both types
'Game' and 'Practice' for the team in question.

Simple Select
having a problem with a simple select statement - in PHP and from MYSQL command line -

Here is statement

Database changed
mysql> select account_number FROM master WHERE client_name LIKE '%Baber's Rent To Own'";

It gives this and brings up no results
It will not run through a PHP query command

Simple Query
I was curious as to what is the best way to determine if a SELECT query was successful in retireving any rows? I've been using..
IF(!$getline){echo "no rows found"
but if there were rows found I would have to run the same query again so I can print them. Is there another way?

$get="select * from orders'";
$getresult=mysql_query($get, $link) or die(ErrorDB($realname));
$getline=mysql_fetch_array($getresult, MYSQL_ASSOC) or die(ErrorDB($realname));

Simple SQL Query
I am looking for some help on a simple (yet beyond my reach) SQL query that I can run via phpMyAdmin whenever I need to.
In a nutshell, I have a Database called A with table B, in which fields C and D sit. For each record, field C has an integer and field D has a human-readable date/time entry in the format: 2005-08-23 17:35:33.
It is important to note that the above date part is YYYY-MM-DD as the convention might be different in your locality.
Anyway, I am looking for an SQL query that will go through the table and whenever the date/time shown in field D is no more than 5 days ago, to add X (some whole number chosen and manually specified in the query each time it's run) to the value in field C

Simple Query
At least I think it should be simple, but I can't get it.

SELECT * FROM 'tableA'
WHERE 'tableA.id' = 'tableB.target_id'

Which returns an empty set, though

SELECT * FROM tableB
WHERE target_id = "b327f34a-54ea-071a-3b9d-451810085fb9"

and

SELECT * FROM tableA
WHERE id = "b327f34a-54ea-071a-3b9d-451810085fb9"

both work. That is to say that there is a record in tableA WHERE, tableA.id = tableB.target_id for AT LEAST one record in tableB

Simple Timestamp
I am working on a new DB I have built, and one of the fields I made is the MySQL timestamp.I "thought" this field would autopopulate when a new record is created, and not change after that unless I forced it.But I notice that every time I edit the record that timestamp field gets reset to the current time.
Is that the correct way that field is supposed to function? And if so how do I use the timestamp in a field that will not change when the record gets edited?

Simple MySQL Questions
I have these two simple questions about MySQL:

1- If a column (id) in a MySQL table holds data of type mediumint (or other int types), when I do an SQL query do I have to put the id number between single quotes like id=õˆ' or just id=875 ?

And the more stupid question :

2- When I do an UPDATE query, does the updated column have to be the same one that identifies the row (after WHERE in the query) to be updated? i.e. is the following query valid?

UPDATE table_name SET col1=1 WHERE col2=10

Simple Join Question
ON EDIT: I figured out the SQL syntax problem. But here's the rest of my question:

I'm trying to join two tables with information about birds, each of which share a field named "Name," which contains birds' scientific names. Most of the information I need comes from table gz_birds, but I also want to display common names, from gz_birds_names.NameCommonD.

If I write a query that targets table gz_birds only, then it displays every member of a particular bird family. For example, genus Falconidae might display a dozen genera...
PHP

$Children = mysql_query ("SELECT GZB.N, GZB.Name, GZB.Parent, GZB.Parent2, GZB.Rank
FROM gz_birds AS GZB
WHERE $Parent = '$URL1' Order By N, Rank");
But when I join table gz_birds_names, it displays just two children (genera)...
PHP
$Children = mysql_query ("SELECT GZB.N, GZB.Name, GZB.Parent, GZB.Parent2, GZB.Rank,
GZBN.Name, GZBN.NameCommonD
FROM gz_birds AS GZB
LEFT JOIN gz_birds_names GZBN ON GZBN.Name = GZB.Name
WHERE $Parent = '$URL1' Order By N, Rank");

Simple Query That I Can't Figure Out
First off, here's my table:
CREATE TABLE order_info (
ID int(10) NOT NULL auto_increment,
ordernum int(10) NOT NULL default Ɔ',
item_code text NOT NULL,
item_qty int(5) NOT NULL default Ɔ',
item_price text NOT NULL,
have int(5) default NULL,
standby int(5) default NULL,
backorder int(5) default NULL,
refund int(5) default NULL,
`status` int(2) NOT NULL default Ƈ',
PRIMARY KEY (ID),
KEY ordernum (ordernum),
KEY `status` (`status`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 PACK_KEYS=0 AUTO_INCREMENT=477150 ;
I would like to make a simple query that returns ordernum's that match this critera:
All of the items have a 'status' of 3
Have at least 1 'refund'
and I would like the ordernum's returned to be distinct.

Here's the query I had before the requirement of the 'refund' column:

SELECT
DISTINCT order_info.ordernum
FROM
order_info
WHERE
order_info.ordernum NOT IN (
SELECT
order_info.ordernum
FROM
order_info
WHERE
order_info.status != 3
)
AND
order_info.status = 3
I tried adding:

SELECT
order_info.ordernum
FROM
order_info
WHERE
order_info.ordernum NOT IN (
SELECT
order_info.ordernum
FROM
order_info
WHERE
order_info.status != 3
)
AND
order_info.status = 3
AND
SUM(order_info.refund) > 0
GROUP BY
order_info.ordernum
But it returns incorrect information (orders with 0 'refunds').

What is the correct way to make this query?

Simple Question About UNIX_TIMESTAMP

UNIX_TIMESTAMP (fieldname) as fieldname
to work, does fieldname have to be a time or timestamp defined field?

Or, is it okay to have a text field as long as the date inside is formatted correctly?

Simple Question About UNIX_TIMESTAMP

UNIX_TIMESTAMP (fieldname) as fieldname to work, does fieldname have to be a time or timestamp defined field? Or, is it okay to have a text field as long as the date inside is formatted correctly?

UNIX_TIMESTAMP (fieldname) as fieldname
to work, does fieldname have to be a time or timestamp defined field?

Or, is it okay to have a text field as long as the date inside is formatted correctly?