CODEWITHSUNDEEP
phpmysqlsqlmysqli
Walter
Walter

Reputation: 71

Distinguish two queries within a union

How would I distinguish where a record is coming from in the following example union query:

SELECT id, name, datetime FROM events
UNION
SELECT id, name, datetime FROM festival
ORDER BY datetime DESC

The way I'm using it in PHP is:

$stmt = $cxn->prepare('SELECT id, name, datetime FROM events WHERE id = ? UNION SELECT id, name, datetime FROM festival WHERE id = ? ORDER BY datetime DESC');
$stmt->bind_param('i', $event_id);
$stmt->execute();
$stmt->store_result();
$stmt->bind_result($id, $name, $datetime);

while ($stmt->fetch()) {
    echo "ID: " . $id . "\n";
    echo "Name: " . $name . "\n";
    echo "Date: " . $datetime;
}

$stmt->close();

Upvotes: 0

Views: 112

Answers (2)

SajithNair
SajithNair

Reputation: 3867

SELECT id, name, datetime, 'events' FROM events
UNION
SELECT id, name, datetime, 'festival' FROM festival
ORDER BY datetime DESC

PHP

$stmt = $cxn->prepare('SELECT id, name, datetime, "events" as tablename FROM events WHERE id = ? UNION SELECT id, name, datetime, "festival" as tablename FROM festival WHERE id = ? ORDER BY datetime DESC');
$stmt->bind_param('i', $event_id);
$stmt->execute();
$stmt->store_result();
$stmt->bind_result($id, $name, $datetime, $table);

while ($stmt->fetch()) {
    echo "ID: " . $id . "\n";
    echo "Name: " . $name . "\n";
    echo "Date: " . $datetime;
    echo "Which Table: " . $table;
}

$stmt->close();

Upvotes: 1

DSS
DSS

Reputation: 7259

you could do this by adding one more column and differentiate it like:

SELECT id, name, datetime, 'E' as fromwhere FROM events
UNION
SELECT id, name, datetime,'F' as fromwhere FROM festival
ORDER BY datetime DESC

the fromwhere column will tell you if its from events or festival.

Upvotes: 0

Related Questions