2

What is wrong with this query:

WITH volcan AS (SELECT DISTINCT v.numturista
                FROM viaje v, sitio s
                WHERE v.numsitio = s.numsitio
                AND s.tipo = 'Volcan'),
    desierto AS (SELECT DISTINCT v.numturista
                 FROM viaje v, sitio s
                 WHERE v.numsitio = s.numsitio
                 AND s.tipo = 'Desierto')
SELECT DISTINCT pais
FROM turista
WHERE numturista IN (volcan INTERSECT desierto);

Isn't it supposed to be equivalent to the following (since WITH creates named SELECT queries):

SELECT DISTINCT pais
FROM turista
WHERE numturista IN (
(SELECT DISTINCT v.numturista
                FROM viaje v, sitio s
                WHERE v.numsitio = s.numsitio
                AND s.tipo = 'Volcan')
INTERSECT
(SELECT DISTINCT v.numturista
                 FROM viaje v, sitio s
                 WHERE v.numsitio = s.numsitio
                 AND s.tipo = 'Desierto')
);

It says:

syntax error near INTERSECT.

Postgres version: psql (PostgreSQL) 9.1.9

UPDATE: Test tables

CREATE TABLE turista (
    numturista INTEGER,
    nomturista VARCHAR(100),
    pais VARCHAR(100),
    PRIMARY KEY(numturista)
);

CREATE TABLE sitio (
    numsitio INTEGER,
    nomsitio VARCHAR(100),
    tipo VARCHAR(100),
    continente VARCHAR(100),
    PRIMARY KEY(numsitio)
);

CREATE TABLE viaje (
    numviaje VARCHAR(7),
    numturista INTEGER,
    numsitio INTEGER, 
    fechasalida DATE,
    fechallegada DATE,
    ciudadsalida VARCHAR(100),
    PRIMARY KEY(numviaje, numturista, numsitio),
    FOREIGN KEY(numsitio) REFERENCES sitio,
    FOREIGN KEY(numturista) REFERENCES turista
);

INSERT INTO turista VALUES
 (300, 'Carlos', 'Costa Rica')
,(301, 'Pierre', 'Francia')
,(302, 'John', 'Jamaica')
,(303, 'Mario', 'Panama')
,(304, 'Ali', 'Tunez')
,(305, 'Ana', 'Guatemala');

INSERT INTO sitio VALUES 
 (125, 'Isla Moorea', 'Mar Litoral', 'Oceania')
,(126, 'Bahia Matsushima', 'Mar Litoral', 'Asia')
,(127, 'Irazu', 'Volcan', 'America')
,(128, 'Ngorongoro', 'Volcan', 'Africa')
,(129, 'Valle de la Muerte', 'Desierto', 'America')
,(130, 'Kilimandjar', 'Volcan', 'Africa');

INSERT INTO viaje VALUES
 ('03-2012', 301, 125, '2013-03-03', '2013-10-03', 'Paris')
,('04-2012', 303, 129, '2013-04-07', '2014-02-07', 'Las Vegas')
,('05-2012', 301, 128, '2013-05-07', '2013-12-07', 'Dar-es-Salam')
,('06-2012', 304, 127, '2013-06-07', '2014-02-07', 'San Jose')
,('07-2012', 302, 128, '2015-04-11', '2014-01-08', 'Mombasa')
,('04-2012', 305, 129, '2013-04-07', '2014-02-07', 'Las Vegas')
,('06-2012', 305, 127, '2013-06-07', '2014-02-07', 'San Jose');
Community
  • 1
  • 1
blaze
  • 2,588
  • 3
  • 32
  • 47
  • You probably need `(SELECT * FROM volcan INTERSECT SELECT * FROM desierto)` instead of `volcan INTERSECT desierto` but that assumes the table structures are compatible; we're going to need more info to give real answers. – Daniel Lyons Jun 14 '13 at 15:48
  • Hi, updated postgres version and error message. What other information do you need me to provide? – blaze Jun 14 '13 at 15:49
  • What I don't understand is why it doesn't work, since volcan is a named SELECT query? – blaze Jun 14 '13 at 15:52
  • I already replaced volcan and desierto with their respective SELECT statements and it works. Yeah, there are duplicate rows in the viaje table. – blaze Jun 14 '13 at 15:54
  • This seems to be a case of [**relational division**](http://stackoverflow.com/questions/7364969/how-to-filter-sql-results-in-a-has-many-through-relation) and can be solved more elegantly and faster. If you'd provide a test case and a description of what you are after ... – Erwin Brandstetter Jun 14 '13 at 15:56
  • Added the tables for testing. – blaze Jun 14 '13 at 15:58
  • The test case and information you supplied on request makes this a useful & interesting question now. +1 – Erwin Brandstetter Jun 14 '13 at 16:10

1 Answers1

5

This would be equivalent:

WITH volcan AS (
    SELECT DISTINCT v.numturista
    FROM   viaje v
    JOIN   sitio s USING (numsitio)
    WHERE  s.tipo = 'Volcan'
   )
, desierto AS (
    SELECT DISTINCT v.numturista
    FROM   viaje v
    JOIN   sitio s USING (numsitio)
    WHERE  s.tipo = 'Desierto'
   )
SELECT DISTINCT pais
FROM   turista
WHERE  numturista IN ((TABLE volcan) INTERSECT (TABLE desierto));

But it's probably pretty inefficient ...

TABLE tbl is just a notational shortcut for SELECT * FROM tbl.

Alternative query

Try this:

SELECT pais
FROM   turista t
WHERE  EXISTS (SELECT 1 FROM viaje v JOIN sitio s USING (numsitio)
               WHERE  v.numturista = t.numturista AND s.tipo = 'Volcan')
AND    EXISTS (SELECT 1 FROM viaje v JOIN sitio s USING (numsitio)
               WHERE  v.numturista = t.numturista AND s.tipo = 'Desierto')

Does the same, just simpler and faster. With EXISTS, you don't need any DISTINCT clauses, except if you really have duplicate countries (pais), which I seriously doubt.

Community
  • 1
  • 1
Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228