CODEWITHSUNDEEP
mysqljoininner-join
Nomis
Nomis

Reputation: 457

Querying, joining and returning ID's from mySQL

I am trying to make a query with JOIN that should work as following:

  1. Imaging a user enter either a mobile number or name in a searchbox.
  2. The result should be returning data that match the search from the two tables below.

I want to JOIN and return data where ID from CUSTOMER table (id) and the TASK table (customer_id) are the same.

My below $sql query returns the correct task(s) data ([customer_id] => 5) from the TASK table, but it always returns ID 1 from the CUSTOMER table when it should return ID 5 from the CUSTOMER table).

I can not figure out what I am doing wrong?

My sql query ex.:

$sql = ("SELECT * FROM customer, task WHERE customer.id = task.customer_id LIKE 5 ");

My CUSTOMER table info:

CREATE TABLE `customer` (
  `id` int(40) NOT NULL AUTO_INCREMENT,
  `name` varchar(40) NOT NULL DEFAULT '',
  `mobil` varchar(20) NOT NULL,
  `email` varchar(50) NOT NULL DEFAULT '',
  `Messages` varchar(200) NOT NULL DEFAULT '',
  `adr` varchar(50) NOT NULL DEFAULT '',
  `zip` int(4) NOT NULL,
  `date_created` date NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=latin1;

My TASK table info:

CREATE TABLE `task` (
  `task_id` int(40) NOT NULL AUTO_INCREMENT,
  `user_id` int(40) NOT NULL,
  `customer_id` int(40) NOT NULL,
  `date` datetime NOT NULL,
  PRIMARY KEY (`task_id`),
  KEY `customer_id` (`customer_id`),
  KEY `user_id` (`user_id`),
  CONSTRAINT `task_ibfk_1` FOREIGN KEY (`customer_id`) REFERENCES `customer` (`id`),
  CONSTRAINT `task_ibfk_2` FOREIGN KEY (`user_id`) REFERENCES `user` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=latin1;

Upvotes: 0

Views: 42

Answers (2)

Joel Coehoorn
Joel Coehoorn

Reputation: 415820

I'm amazed this runs at all. Let's look at this expression:

customer.id = task.customer_id LIKE 5

Keeping in mind that LIKE and = are both binary operators, this can only make sense one of two ways:

  1. The equality is handled first, and the boolean (0 or 1) result of that comparison is then LIKE-compared to 5.
  2. The LIKE is handled first, the boolean result of task.customer_id LIKE 5 is then compared to your customer.id field.

According to the docs, those operators have the same precedence. However, given your result (a single record with id 1), it looks like the second option is actually taking place. Since there is a task.customer_id with a value of 5, this condition passes; the boolean result of the LIKE expression is a 1, and therefore you get the records where customer.id is 1.

We can fix this by separating the LIKE comparison with an AND. While we're at it, let's ditch the obsolete A,B join syntax:

SELECT * 
FROM customer
INNER JOIN task ON customer.id = task.customer_id 
WHERE customer.id LIKE 5

Upvotes: 1

Jens
Jens

Reputation: 69440

Try:

SELECT * FROM customer join task on customer.id = task.customer_id where  customer.id  = 5

Upvotes: 0

Related Questions