MySQL query with JOIN or different solution

Question

I have a problem with this query:

select start, end, surname, name, id from employee, absences where surname LIKE '$surname%' and name LIKE '$name%' and start LIKE '$start%' and name LIKE '$end%' order by start ASC

I am aware that this is not a good way to search. I tried to use a JOIN for this, but I failed. I have two tables: absences and employee, simply connected by a FK, which gives me too many results(multiplied).

How do I use a join for this? Or is there another solution?

Tables_

CREATE TABLE IF NOT EXISTS `absences` (
`absences_ID` int(11) NOT NULL,
  `employee_FK` int(11) NOT NULL,
  `start` date NOT NULL,
  `end` date NOT NULL,
  `approved` tinyint(1) NOT NULL DEFAULT '0',
  `approved_date` date DEFAULT NULL,
  `comment` varchar(100) NOT NULL,
  `type_FK` int(11) NOT NULL
) ENGINE=InnoDB AUTO_INCREMENT=1371 DEFAULT CHARSET=latin1;

CREATE TABLE IF NOT EXISTS `employee` (
`employee_ID` int(11) NOT NULL,
  `id` int(11) NOT NULL,
  `name` varchar(30) DEFAULT NULL,
  `surname` varchar(30) DEFAULT NULL,
  `password` varchar(60) NOT NULL,
  `email` varchar(255) NOT NULL,
  `on_off_FK` int(11) NOT NULL,
  `inactive` tinyint(1) NOT NULL DEFAULT '1',
  `admin` tinyint(1) NOT NULL DEFAULT '0'
) ENGINE=InnoDB AUTO_INCREMENT=28 DEFAULT CHARSET=latin1;

Can you show the structure of tables?

Jim Wright
– Jim Wright

2015-06-17 13:08:11 +00:00
Commented Jun 17, 2015 at 13:08 — Jim Wright
– Jim Wright, Commented Jun 17, 2015 at 13:08
It's in the start post now

Gintoki
– Gintoki

2015-06-17 13:14:56 +00:00
Commented Jun 17, 2015 at 13:14 — Gintoki
– Gintoki, Commented Jun 17, 2015 at 13:14

habib ul haq · Accepted Answer · 2015-06-17 13:24:22Z

1

use join with the condition

    select a.start, a.end, e.surname, e.name, e.id 
from employee e
JOIN absences a ON e.employee_id=a.employeefk
where e.surname LIKE '$surname%' and e.name LIKE '$name%' and a.start LIKE'$start%' and a.end LIKE '$end%'
order by a.start ASC

try this I have updated it according to your table

Now try this

edited Jun 17, 2015 at 13:24

answered Jun 17, 2015 at 13:10

habib ul haq

8246 silver badges13 bronze badges

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

8 Comments

Gintoki Over a year ago

Did you use id as primary key? id is just a unique number each employee has, for the primary key I use employee_ID. I think the JOIN needs to go on employee_FK, right?

habib ul haq Over a year ago

yes I was confuse about your primary key.. now i have updated my answer

PaulF Over a year ago

@habibulhaq : well you just beat me to the same answer, so no problems with editing them same

Gintoki Over a year ago

I will try it and I think I start to understand how to use it.

habib ul haq Over a year ago

hahahahahhaah @PaulF I was in so hurry that Mistakenly i have edited your post :p

|

sanjay yadav · Accepted Answer · 2015-06-17 13:15:25Z

0

Try this... I have added some place holder where u need to put column required to fetch and also on join condition which to use

select {column name comma separated} from employee e
INNER JOIN absences ON e.id=absences.id {here u need to specify which column to use for joining two column}
where surname LIKE '$surname%' and name LIKE '$name%' and start LIKE '$start%' and name LIKE '$end%'
order by start ASC

answered Jun 17, 2015 at 13:15

sanjay yadav

12 bronze badges

Comments

habib ul haq · Accepted Answer · 2015-06-17 13:22:27Z

0

select a.start, a.end, e.surname, e.name, e.id 
from employee e
JOIN absences a ON e.id=a.Id
where e.surname LIKE '$surname%' and e.name LIKE '$name%' and a.start LIKE'$start%' and a.end LIKE '$end%'
order by a.start ASC

try this I have updated it according to your table

edited Jun 17, 2015 at 13:22

habib ul haq

8246 silver badges13 bronze badges

answered Jun 17, 2015 at 13:10

PaulF

6,7932 gold badges20 silver badges30 bronze badges

Collectives™ on Stack Overflow

MySQL query with JOIN or different solution

3 Answers 3

8 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

8 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related