comparison query

[ScrewLooseSalad]

I want to compare two columns, essentially this:

 

$query = "SELECT * FROM table WHERE column3 < column4;";

 

Is this even possible? Or have I just got the syntax badly wrong?

I've tried writing out a complicated php code breaking my task into three MySQL lookups instead but that's proving more trouble than its worth..

 

Everything I've found on Google hasn't been related to my problem...

Is anyone able to help me in the right direction? Thanks

[Jessica]

Yes, it's possible. Why don't you post your actual code that you're having trouble with?

[Barand]

If you are running the query with mysql_query() in PHP then remove the ; after column4.

[ScrewLooseSalad]

  On 2/22/2013 at 12:09 PM, Jessica said:

Yes, it's possible. Why don't you post your actual code that you're having trouble with?

 

I was hoping I wouldn't need it if I could only make that line of sql work

[Jessica]

  On 2/22/2013 at 3:45 PM, ScrewLooseSalad said:

I was hoping I wouldn't need it if I could only make that line of sql work

 

... So .... why don't you post it HERE so we can HELP you make it WORK. 

[ScrewLooseSalad]

  On 2/22/2013 at 4:21 PM, Jessica said:

... So .... why don't you post it HERE so we can HELP you make it WORK.

 

I did,

$query = "SELECT * FROM table WHERE column3 < column4;";

 

the syntax for it is incorrect, I can't find anything similar on Google or in my book,

and the best solution I can come up with involves looking up column3 and 4 separately,

comparing them, then performing a third query to find all the relevant entries.

[Jessica]

CREATE TABLE IF NOT EXISTS `temp` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `title` varchar(100) NOT NULL,
  `a_number` int(11) NOT NULL,
  `another_number` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=1;

INSERT INTO  `test`.`temp` (
`id` ,
`title` ,
`a_number` ,
`another_number`
)
VALUES (
NULL ,  'Row One',  '1',  '2'
), (
NULL ,  'Row Two',  '2',  '1'
);

SELECT * 
FROM  `temp` 
WHERE a_number < another_number

id    title    a_number    another_number
1    Row One    1    2

 

 

If you want some help you're going to have to explain to us WHAT you are encountering. That query works. You haven't given us any information to help you, and it's annoying.

 

 

(Sidebar: I used phpmyadmin for that dump, excuse the quoted numbers. Blah blah blah)

[Zane]

column3 and column4 MUST be in a numerical format like INT or FLOAT in order for that comparison to work.

[Jessica]

  On 2/22/2013 at 5:07 PM, Zane said:

column3 and column4 MUST be in a numerical format like INT or FLOAT in order for that comparison to work.

Dates work too.

[Barand]

  On 2/22/2013 at 5:07 PM, Zane said:

column3 and column4 MUST be in a numerical format like INT or FLOAT in order for that comparison to work.

 

And so do varchars

 

CREATE TABLE test (
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
creation TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
column3 VARCHAR(10),
column4 VARCHAR(10)
);

INSERT INTO test (column3, column4) VALUES
('aaaaa', 'bbbbb'),
('ccccc', 'bbbbb'),
('aaaaa', 'ccccc'),
('ddddd', 'bbbbb');

SELECT * FROM test WHERE column3 < column4;

RESULTS-->

1, 2013-02-22 17:43:56, aaaaa, bbbbb
3, 2013-02-22 17:43:56, aaaaa, ccccc

[Jessica]

Thnx Barand, I thought so too but didn't have the energy to add a few rows to my table.

*coffee break time*

[fenway]

The comparison operator will always "work" -- but the implicit casting just might not do what you'd expect.

[Zane]

Never figured VARCHAR would do that. Learn something new everyday.

[fenway]

  On 2/24/2013 at 7:50 PM, Zane said:

Never figured VARCHAR would do that. Learn something new everyday.

That's how date strings are sorted, too (i.e. outside of mysql).

[ScrewLooseSalad]

thanks for your help, I've got it working now,

I wasn't confident that my sql was correct