Omzetten van IN mysql in JOINS

Overzicht Reageren

Sponsored by: Vacatures door Monsterboard

Production Engineer

Supermicro® (NASDAQ: SMCI), the leading innovator in high-performance, high-efficiency server technology, is a premier provider of advanced server Building Block Solutions® for Data Center, Cloud Computing, Enterprise IT, Hadoop/Big Data, HPC and Embedded Systems worldwide. Supermicro is committed to protecting the environment through its “We Keep IT Green®” initiative and provides customers with the most energy-efficient, environmentally-friendly solutions available on the market. Supermicro Computer B.V. is seeking a: Production Engineer Who is responsible for the assembly and building of Supermicro product that meet products quality requirements and shipment deadlines. This position will be located in the HMEA headquarters in 's-Hertogenbosch,

Bekijk vacature »

Daniel van Seggelen

Daniel van Seggelen

13/02/2018 15:43:27
Quote Anchor link
@IVO

In een eerdere tpoic had je een wijze laten zijn om IN query'som te zetten naar JOINS


Code (php)
PHP script in nieuw venster Selecteer het PHP script
1
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



Naar JOINS

Code (php)
PHP script in nieuw venster Selecteer het PHP script
1
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


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.



Overzicht Reageren

 
 

Om de gebruiksvriendelijkheid van onze website en diensten te optimaliseren maken wij gebruik van cookies. Deze cookies gebruiken wij voor functionaliteiten, analytische gegevens en marketing doeleinden. U vindt meer informatie in onze privacy statement.