XOOPS 
Forum
Forum Index General Forums Community Support Modules Support Themes Support Development International Support Module Hack Reviews Theme Designer Talk
  1. Board index / 
  2. XOOPS Modules Support forums / 
  3. Module usage questions 
  4.  / IN and NOT IN on MySQL < 4.1
Register
1
Antoine
IN and NOT IN on MySQL < 4.1

  • 2005/9/22 7:26

  • Antoine

  • Friend of XOOPS

  • Posts: 112

  • Since: 2004/11/14


Hi,

Currently I am about to implement a module I have been working on. This modules has been developed using MySQL 4.1. Now, however, I have to impelement it on a system that is running an earlier version of MySQL and doesn't support subqueries.

Please, if anyone knows how to rewrite this query so it doesn't use subqueries I'd be most gratefull. I could solve the problem in code but that would at least quadruple the execution time.

SELECT NGS_ID, NGS_Schaal FROM xoops_survey_normgroepschaal, xoops_survey_normgroep
WHERE NRG_ID = 7
AND xoops_survey_normgroepschaal.VRL_ID = xoops_survey_normgroep.VRL_ID
AND NGS_ID NOT IN 
(SELECT NGS_ID FROM xoops_survey_normgroepschaalitem WHERE NRG_ID = 7) ORDER BY NGS_Schaal
2
McNaz
Re: IN and NOT IN on MySQL < 4.1

  • 2005/9/22 10:28

  • McNaz

  • Just can't stay away

  • Posts: 574

  • Since: 2003/4/21


Quote:
doesn't support subqueries.

That got me too.

The trick with a Not In subquery is to use a left join.

So your query will become:

SELECT NGS_ID, NGS_Schaal FROM xoops_survey_normgroepschaal ott left join  xoops_survey_normgroepschaalitem inn on
  ott.NRG_ID = inn.NRG_ID
where inn.NRG_ID is null

HTH

Login

Register now!  |  Lost Password?

Search

Advanced Search

Recent Posts

Donat-O-Meter

Stats
Goal: $15.00
Due Date: Oct 31
Gross Amount: $0.00
Net Balance: $0.00
Left to go: $15.00
Make donations with PayPal!