SQL Multiple Select on same table

Question

I'm currrently stuck with my sql query.

I want to do multiple select query but all in one. Each select is for the same table, same column but have different conditions.

I'm using this for the moment :

    /* Ordre d'affichage 1 */
    $sql = "SELECT O.INTITULE, O.ENTREPRISENOM, O.TYPECONTRAT, O.DATEDEBUT, O.DATEINSERTION, O.REGION, O.FONCTION, O.ID_DIFFUSEUR, D.Id, D.MiseEnAvantOffres FROM OffreEmploi O, Diffuseur D WHERE O.INTITULE = ? AND O.REGION = $regions AND O.FONCTION = $fonctions AND O.TYPECONTRAT = $type_contrat AND O.ID_DIFFUSEUR = D.Id AND D.MiseEnAvantOffres = 1 ";
    /* Ordre d'affichage 2 */
    $sql .= "EXCEPT ";
    $sql .="SELECT O.INTITULE, O.ENTREPRISENOM, O.TYPECONTRAT, O.DATEDEBUT, O.DATEINSERTION, O.REGION, O.FONCTION, O.ID_DIFFUSEUR, D.Id, D.MiseEnAvantOffres FROM OffreEmploi O, Diffuseur D WHERE O.REGION = $regions AND O.FONCTION = $fonctions AND O.TYPECONTRAT = $type_contrat AND O.ID_DIFFUSEUR = D.Id AND D.MiseEnAvantOffres = 1 ";
    /* Ordre d'affichage 3 */
    $sql .= "EXCEPT ";
    $sql .="SELECT O.INTITULE, O.ENTREPRISENOM, O.TYPECONTRAT, O.DATEDEBUT, O.DATEINSERTION, O.REGION, O.FONCTION, O.ID_DIFFUSEUR, D.Id, D.MiseEnAvantOffres FROM OffreEmploi O, Diffuseur D WHERE O.FONCTION = $fonctions AND O.TYPECONTRAT = $type_contrat AND O.ID_DIFFUSEUR = D.Id AND D.MiseEnAvantOffres = 1 ";
    /* Ordre d'affichage 4 */   
    $sql .= "EXCEPT ";
    $sql .="SELECT O.INTITULE, O.ENTREPRISENOM, O.TYPECONTRAT, O.DATEDEBUT, O.DATEINSERTION, O.REGION, O.FONCTION, O.ID_DIFFUSEUR, D.Id, D.MiseEnAvantOffres FROM OffreEmploi O, Diffuseur D WHERE O.FONCTION = $fonctions AND O.ID_DIFFUSEUR = D.Id AND D.MiseEnAvantOffres = 1 ";
    /* Ordre d'affichage 5 */
    $sql = "SELECT O.INTITULE, O.ENTREPRISENOM, O.TYPECONTRAT, O.DATEDEBUT, O.DATEINSERTION, O.REGION, O.FONCTION, O.ID_DIFFUSEUR, D.Id, D.MiseEnAvantOffres FROM OffreEmploi O, Diffuseur D WHERE O.INTITULE = ? AND O.REGION = $regions AND O.FONCTION = $fonctions AND O.TYPECONTRAT = $type_contrat ";
    $sql .= "EXCEPT ";
    /* Ordre d'affichage 6 */
    $sql = "SELECT O.INTITULE, O.ENTREPRISENOM, O.TYPECONTRAT, O.DATEDEBUT, O.DATEINSERTION, O.REGION, O.FONCTION, O.ID_DIFFUSEUR, D.Id, D.MiseEnAvantOffres FROM OffreEmploi O, Diffuseur D WHERE O.REGION = $regions AND O.FONCTION = $fonctions AND O.TYPECONTRAT = $type_contrat ";

$query = $this->db->query($sql, array($_POST['intitule'], $_POST['intitule']));

Is there a way to make this easier and possible? (because currently the except does not work)

Still struggling? Consider providing proper DDLs (and/or an sqlfiddle) TOGETHER WITH THE DESIRED RESULT SET. — Strawberry
– Strawberry, Commented May 2, 2014 at 10:23

Gordon Linoff · Accepted Answer · 2014-05-02 19:14:12Z

Why can't you do this as one long where clause:

SELECT O.INTITULE, O.ENTREPRISENOM, O.TYPECONTRAT, O.DATEDEBUT, O.DATEINSERTION, O.REGION,
       O.FONCTION, O.ID_DIFFUSEUR, D.Id, D.MiseEnAvantOffres
FROM OffreEmploi O join
     Diffuseur D
     on O.ID_DIFFUSEUR = D.Id 
WHERE (O.INTITULE = ? AND O.REGION = $regions AND O.FONCTION = $fonctions AND O.TYPECONTRAT = $type_contrat AND D.MiseEnAvantOffres = 1) and
      not (O.REGION = $regions AND O.FONCTION = $fonctions AND O.TYPECONTRAT = $type_contrat AND D.MiseEnAvantOffres = 1) and
      not (O.FONCTION = $fonctions AND O.TYPECONTRAT = $type_contrat AND D.MiseEnAvantOffres = 1) and
      not (O.FONCTION = $fonctions AND D.MiseEnAvantOffres = 1) and
      not (O.INTITULE = ? AND O.REGION = $regions AND O.FONCTION = $fonctions AND O.TYPECONTRAT = $type_contrat) and
      not (O.REGION = $regions AND O.FONCTION = $fonctions AND O.TYPECONTRAT = $type_contrat);

The last two are missing the (implicit) join conditions in the where clauses. If you were having problems, that could have been the cause. You should learn to always use explicit joins.

Collectives™ on Stack Overflow

SQL Multiple Select on same table

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related