php - SQL error when i want comparate values of two query SQL

View Original

The idea is that I would like to gather several table values ??�??�in a column named "entrcod", and then have them compared to another query I am trying in my join query.

AND if a value exists in the first request and in the second it sends me the duplicate

CODE WITHOUT ERROR BUT DONT HAVE ALL VALUES I WANT

         SELECT entrcod FROM marchelot WHERE marcod = 494128
         AND entrcod IN
         
         (SELECT entrcod FROM prospectuserclient
          WHERE procod = 20347 AND usercod = '001'
          
          UNION
          
          SELECT betcod
          FROM prospectuserclient
          WHERE procod = 20347 AND usercod = '001'
          
          UNION
          
          SELECT buccod
          FROM prospectuserclient
          WHERE procod = 20347 AND usercod = '001'
          
          UNION
          
           SELECT ouvrcod
          FROM prospectuserclient
          WHERE procod = 20347 AND usercod = '001'
          
          UNION
          
           SELECT oeuvcod
          FROM prospectuserclient
          WHERE procod = 20347 AND usercod = '001')

CODE WITH INFORMATION I WANT , BUT WITH ERROR

         SELECT entrcod FROM marchelot WHERE marcod = 494128
           UNION
             select buccod as entrcod from marchebuc where marcod = 494128 
            UNION
             select ouvrcod as entrcod from marcheouvrage where marcod = 494128 
           UNION
           select oeuvcod as entrcod  from marcheoeuvre where marcod = 494128          
          
         AND entrcod IN
         
         (SELECT entrcod FROM prospectuserclient
          WHERE procod = 20347 AND usercod = '001'
          
          UNION
          
          SELECT betcod
          FROM prospectuserclient
          WHERE procod = 20347 AND usercod = '001'
          
          UNION
          
          SELECT buccod
          FROM prospectuserclient
          WHERE procod = 20347 AND usercod = '001'
          
          UNION
          
           SELECT ouvrcod
          FROM prospectuserclient
          WHERE procod = 20347 AND usercod = '001'
          
          UNION
          
           SELECT oeuvcod
          FROM prospectuserclient
          WHERE procod = 20347 AND usercod = '001')

THE ERROR

MySQL a r?�pondu : Documentation

#1054 - Champ 'entrcod' inconnu dans IN/ALL/ANY subquery

Answer

Solution:

Your error is misleading

Your query must look like this to work

your condition for entrcod IN doesn't exist in your query, adding an alias to the original query fixes that

SELECT entrcod FROM
           
    (SELECT entrcod FROM marchelot WHERE marcod = 494128
           UNION
             select buccod as entrcod from marchebuc where marcod = 494128 
            UNION
             select ouvrcod as entrcod from marcheouvrage where marcod = 494128 
           UNION
           select oeuvcod as entrcod  from marcheoeuvre where marcod = 494128          
          ) t1
         WHERE entrcod IN
         
         (SELECT entrcod FROM prospectuserclient
          WHERE procod = 20347 AND usercod = '001'
          
          UNION
          
          SELECT betcod
          FROM prospectuserclient
          WHERE procod = 20347 AND usercod = '001'
          
          UNION
          
          SELECT buccod
          FROM prospectuserclient
          WHERE procod = 20347 AND usercod = '001'
          
          UNION
          
           SELECT ouvrcod
          FROM prospectuserclient
          WHERE procod = 20347 AND usercod = '001'
          
          UNION
          
           SELECT oeuvcod
          FROM prospectuserclient
          WHERE procod = 20347 AND usercod = '001'
)

entrcod

Source