0

I have 2 queries which I want to use together to produce one output result to the user. The output describes to the user the journey to take to from A to B. I keep getting a syntax error when I try to combine both queries with UNION:

Query failed: ERROR: syntax error at or near "UNION" LINE 6: UNION ^

I have had a look at a similar question but the solutions & discussions provided do not seem to work.

$name = pg_escape_string($_POST['name']); // Start destination from user input
$name2 = pg_escape_string($_POST['name2']); // End destination from user input

//If no results are given, the following query below will execute instead.

if (pg_num_rows($result) == 0 ) {

$query = "SELECT dt1.name as name1,dt1.time as time1,dt2.name as name2,dt2.time as time2
From departure_times as dt1
inner join departure_times as dt2 on dt2.tram_id = dt1.tram_id
where dt1.name = '$name' and dt2.name = 'CitySquare' LIMIT 5 

UNION 

SELECT dt1.name as name3,dt1.time as time3,dt2.name as name4,dt2.time as time4
From departure_times as dt1
inner join departure_times as dt2 on dt2.tram_id = dt1.tram_id
where dt1.name = 'CitySquare' and dt2.name = '$name2' LIMIT 5";
$result = pg_query($query) or die('Query failed: ' . pg_last_error());
} ...

The desired output:

------------------------------------------------------------------------
    name1 | time1  | name2  | time2 | name3 |  time3    | name4  | time4
------------------------------------------------------------------------
  • 1
    Change your aliases in the second UNION to dt3 & 4. – Vérace Mar 3 '16 at 22:30
  • @Vérace: There is no need, SELECT statements of a UNION query have separate scopes. – Erwin Brandstetter Mar 4 '16 at 4:02
5

The primary solution is to add parentheses:

(  -- parentheses required!
SELECT dt1.name AS name1, dt1.time AS time1
     , dt2.name AS name2, dt2.time AS time2
FROM   departure_times dt1
JOIN   departure_times dt2 USING (tram_id)
WHERE  dt1.name = '$name'
AND    dt2.name = 'CitySquare'
-- no ORDER BY?
LIMIT  5 
)
UNION ALL  -- see below
(
SELECT dt1.name, dt1.time, dt2.name, dt2.time  -- see below
FROM   departure_times dt1
JOIN   departure_times dt2 USING (tram_id)
WHERE  dt1.name = 'CitySquare'
AND    dt2.name = '$name2'
-- no ORDER BY?
LIMIT  5
);

Without extra parentheses LIMIT is only allowed once at the end of the query and applies to the result of the UNION. To attach a LIMIT to each SELECT parentheses are required.

The manual:

The UNION clause has this general form:

select_statement UNION [ ALL | DISTINCT ] select_statement

select_statement is any SELECT statement without an ORDER BY, LIMIT, FOR NO KEY UPDATE, FOR UPDATE, FOR SHARE, or FOR KEY SHARE clause. (ORDER BY and LIMIT can be attached to a subexpression if it is enclosed in parentheses. Without parentheses, these clauses will be taken to apply to the result of the UNION, not to its right-hand input expression.)

Other notes:

  • I switched to UNION ALL - assuming you don't actually want to try and fold duplicate rows.

  • Names of result columns are determined by first SELECT of a UNION query. Names (or aliases) in all following SELECT statements are ignored.

  • You may want to add ORDER BY to each SELECT. LIMIT without prior ORDER BY returns an arbitrary selection that can change with every execution - in simple queries determined by the physical order of rows in the table (or in the index), which can change any time.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.