How to use subqueries in MySQL

Question

I have two tables.

Table user:

CREATE TABLE IF NOT EXISTS `user` (
  `user_id` int(20) NOT NULL AUTO_INCREMENT,
  `ud_id` varchar(50) NOT NULL,
  `name` text NOT NULL,
  `password` text NOT NULL,
  `email` varchar(200) NOT NULL,
  `image` varchar(150) NOT NULL,
  PRIMARY KEY (`user_id`)
) ENGINE=InnoDB

and mycatch:

CREATE TABLE IF NOT EXISTS `mycatch` (
  `catch_id` int(11) NOT NULL AUTO_INCREMENT,
  `catch_name` text NOT NULL,
  `catch_details` text NOT NULL,
  `longitude` float(10,6) NOT NULL,
  `latitude` float(10,6) NOT NULL,
  `time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `image` varchar(150) NOT NULL,
  `user_id` int(20) NOT NULL,
  PRIMARY KEY (`catch_id`),
  KEY `user_id` (`user_id`)
) ENGINE=InnoDB;
ALTER TABLE `mycatch`
  ADD CONSTRAINT `mycatch_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `user` (`user_id`) ON DELETE CASCADE ON UPDATE CASCADE;

My goal is: I want to retrieve longitude and latitude from mycatch against given ud_id (from user) and catch_id (from mycatch) where ud_id = given ud_id and catch_id > given catch_id.

I used the query but fail to retrieve

SELECT ud_id=$ud_id FROM user
WHERE user_id =
(
SELECT user_id,longitude,latitude from mycatch
WHERE catch_id>'$catch_id'
)

The error is:

#1241 - Operand should contain 1 column(s)

Your SQL makes no sense. Why do you select longitude and latitude if you just want user_id from your subquery, and why does the outer SELECT set a value? — BoltClock
– BoltClock, Commented Dec 31, 2010 at 12:03

Michael Lorton · Accepted Answer · 2010-12-31 12:07:32Z

2

First, try not to use subqueries at all, they're very slow in MySQL.

Second, a subquery wouldn't even help here. This is a regular join (no, Mr Singh, not an inner join):

SELECT ud_id FROM user, mycatch
WHERE catch_id>'$catch_id'
AND user.user_id = mycatch.user_id

answered Dec 31, 2010 at 12:07

Michael Lorton

44.7k26 gold badges110 silver badges159 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Shakti Singh · Accepted Answer · 2010-12-31 12:10:49Z

1

 Select m.longitude,m.latitude from user u left join mycatch m 
on u.user_id =m.user_id 
where u.ud_id=$ud_id and 
m.catch_id >$catch_id

edited Dec 31, 2010 at 12:10

answered Dec 31, 2010 at 12:04

Shakti Singh

86.7k21 gold badges142 silver badges156 bronze badges

2 Comments

hunter Over a year ago

i need just longitude and latitude from mycatch against given ud_id and catch_id

rsenna Over a year ago

in fact you don't even need the join; just query the mycatch table and you'll be OK...

Collectives™ on Stack Overflow

How to use subqueries in MySQL

2 Answers 2

Comments

2 Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Related