Omzetten van IN mysql in JOINS
In een eerdere tpoic had je een wijze laten zijn om IN query'som te zetten naar JOINS
Code (php)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
2
3
4
5
6
7
8
9
10
11
12
13
14
15
SELECT * ,
b.profile_id as from_id ,
b.profile_name as from_name ,
b.profile_image as from_image,
b.gender as from_gender ,
c.profile_id as to_id ,
c.profile_name as to_name ,
DATE_FORMAT(time_sending,'%b %d %Y %h:%i %p') as time_sending
FROM `messages` a
LEFT JOIN user_profiles b ON a.from = b.profile_id
LEFT JOIN user_profiles c ON a.`to` = c.profile_id
WHERE `to` in (select profile_id from `user_profiles` where is_fake=1 and user_id=1)
and `_initiator` ='unanswered' and hide = 0
order by `time_sending` desc
LIMIT 5
b.profile_id as from_id ,
b.profile_name as from_name ,
b.profile_image as from_image,
b.gender as from_gender ,
c.profile_id as to_id ,
c.profile_name as to_name ,
DATE_FORMAT(time_sending,'%b %d %Y %h:%i %p') as time_sending
FROM `messages` a
LEFT JOIN user_profiles b ON a.from = b.profile_id
LEFT JOIN user_profiles c ON a.`to` = c.profile_id
WHERE `to` in (select profile_id from `user_profiles` where is_fake=1 and user_id=1)
and `_initiator` ='unanswered' and hide = 0
order by `time_sending` desc
LIMIT 5
Naar JOINS
Code (php)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
2
3
4
5
6
7
8
9
10
11
12
13
14
15
SELECT a.* ,
b.profile_id as from_id ,
b.profile_name as from_name ,
b.profile_image as from_image,
b.gender as from_gender ,
c.profile_id as to_id ,
c.profile_name as to_name ,
DATE_FORMAT(time_sending,'%b %d %Y %h:%i %p') as time_sending
FROM `messages` a
LEFT JOIN user_profiles b ON a.from = b.profile_id
LEFT JOIN user_profiles c ON a.`to` = c.profile_id
LEFT JOIN user_profiles fake ON fake.profile_id = a.to and fake.is_fake=1 and fake.user_id = 1
WHERE `_initiator` ='unanswered' and hide = 0
order by `time_sending` desc
LIMIT 5
b.profile_id as from_id ,
b.profile_name as from_name ,
b.profile_image as from_image,
b.gender as from_gender ,
c.profile_id as to_id ,
c.profile_name as to_name ,
DATE_FORMAT(time_sending,'%b %d %Y %h:%i %p') as time_sending
FROM `messages` a
LEFT JOIN user_profiles b ON a.from = b.profile_id
LEFT JOIN user_profiles c ON a.`to` = c.profile_id
LEFT JOIN user_profiles fake ON fake.profile_id = a.to and fake.is_fake=1 and fake.user_id = 1
WHERE `_initiator` ='unanswered' and hide = 0
order by `time_sending` desc
LIMIT 5
Het punt is dat dit andere resultaten geeft. De initiële geeft 0 resultaten en de JOINS versie geeft wel 5 resultaten.
Wat is het verschil en wat is de juiste manier om deze exact om te zetten?
Er zijn nog geen reacties op dit bericht.