Union - different values

[NancyJ]

I have this query and the value for the field id2 is different the results are combined

(
SELECT DATE_FORMAT( mia_booking_payment.date_received, '%d-%c-%Y' ) AS incoming_date, DATE_FORMAT( mia_booking_payment.date_received, '%Y-%m-%d' ) AS incoming_date2, CONCAT( '(booking ID: ', bid, ')' ) AS incoming_name, mia_booking_payment.amount_foreign AS incoming_value, CONCAT( 'Incoming Payment from ', booking.name ) AS description, booking.bid AS id, mia_booking.closed AS closed, mia_booking.id AS booking_id, 1 AS num_bookings, mia_booking_payment.id AS id2, running_balance.balance, running_balance.entry_id
FROM mia_booking_payment
JOIN booking ON mia_booking_payment.booking_id = booking.bid
JOIN mia_booking ON mia_booking_payment.booking_id = mia_booking.id
LEFT JOIN running_balance ON CONCAT( running_balance.payment_id, ' - ', running_balance.account ) = CONCAT( mia_booking_payment.id, ' - AOL UK' )
WHERE DATE_FORMAT( date_received, '%Y/%c/%d' ) >= '2007/11/01' AND DATE_FORMAT( date_received, '%Y/%c/%d' ) <= '2007/11/31' AND mia_booking_payment.account = 'AOL UK (Sterling)'
)
UNION (

SELECT DATE_FORMAT( ajt_cheques.written, '%d-%c-%Y' ) AS incoming_date, DATE_FORMAT( ajt_cheques.written, '%Y-%m-%d' ) AS incoming_date2, 'View Details' AS incoming_name, SUM( ajt_payments_az.value_gbp ) AS incoming_value, CONCAT( 'Batch Payment from ', accountfrom ) AS description, ajt_cheques.cheque_id AS id, ajt_cheques.cheque_id AS id2, 4 AS closed, ajt_payments_az.booking_id AS booking_id, COUNT( ajt_payments_az.booking_id ) AS num_bookings, running_balance.balance, running_balance.entry_id
FROM ajt_cheques
LEFT JOIN ajt_payments_az ON ajt_cheques.cheque_id = ajt_payments_az.cheque_id
LEFT JOIN running_balance ON CONCAT( running_balance.cheque_id, ' - ', running_balance.account ) = CONCAT( ajt_cheques.cheque_id, ' - AOL UK' )
WHERE accountto
LIKE '%AOL UK%' AND DATE_FORMAT( written, '%Y/%c/%d' ) >= '2007/11/01' AND DATE_FORMAT( written, '%Y/%c/%d' ) <= '2007/11/31'
GROUP BY ajt_cheques.cheque_id
)
UNION (

SELECT DATE_FORMAT( ajt_cheques.written, '%d-%c-%Y' ) AS incoming_date, DATE_FORMAT( ajt_cheques.written, '%Y-%m-%d' ) AS incoming_date2, 'View Details' AS incoming_name, ( 0 - SUM( ajt_payments_az.value_gbp ) ) AS incoming_value, CONCAT( 'Payment to ', accountto ) AS description, ajt_cheques.cheque_id AS id, ajt_cheques.cheque_id AS id2, 2 AS closed, ajt_payments_az.booking_id AS booking_id, COUNT( ajt_payments_az.booking_id ) AS num_bookings, running_balance.balance, running_balance.entry_id
FROM ajt_cheques
LEFT JOIN ajt_payments_az ON ajt_cheques.cheque_id = ajt_payments_az.cheque_id
LEFT JOIN running_balance ON CONCAT( running_balance.cheque_id, ' - ', running_balance.account ) = CONCAT( ajt_cheques.cheque_id, ' - AOL UK' )
WHERE accountfrom = 'AOL UK' AND DATE_FORMAT( written, '%Y/%c/%d' ) >= '2007/11/01' AND DATE_FORMAT( written, '%Y/%c/%d' ) <= '2007/11/31'
GROUP BY ajt_cheques.cheque_id
)
UNION (

SELECT DATE_FORMAT( date, '%d-%c-%Y' ) AS incoming_date, DATE_FORMAT( date, '%Y-%m-%d' ) AS incoming_date2, 'Manual Adjustment' AS incoming_name, amount AS incoming_value, description, id, 0 AS closed, NULL AS booking_id, id AS id2, NULL AS balance, NULL AS entry_id, 0 AS num_bookings
FROM balance_adjustment
WHERE date
LIKE '2007-11-%' AND account = 'AZ'
)
ORDER BY incoming_date2 ASC , id2 ASC LIMIT 0 , 30


When I run this query on its own the id2 numbers are correct. When I run the union query the id2 values for this particular query always come out as 1... which doesn't exist (the numbers start at 1000)

SELECT DATE_FORMAT( ajt_cheques.written, '%d-%c-%Y' ) AS incoming_date, DATE_FORMAT( ajt_cheques.written, '%Y-%m-%d' ) AS incoming_date2, 'View Details' AS incoming_name, ( 0 - SUM( ajt_payments_az.value_gbp ) ) AS incoming_value, CONCAT( 'Payment to ', accountto ) AS description, ajt_cheques.cheque_id AS id, ajt_cheques.cheque_id AS id2, 2 AS closed, ajt_payments_az.booking_id AS booking_id, COUNT( ajt_payments_az.booking_id ) AS num_bookings, running_balance.balance, running_balance.entry_id
FROM ajt_cheques
LEFT JOIN ajt_payments_az ON ajt_cheques.cheque_id = ajt_payments_az.cheque_id
LEFT JOIN running_balance ON CONCAT( running_balance.cheque_id, ' - ', running_balance.account ) = CONCAT( ajt_cheques.cheque_id, ' - AOL UK' )
WHERE accountfrom = 'AOL UK' AND DATE_FORMAT( written, '%Y/%c/%d' ) >= '2007/11/01' AND DATE_FORMAT( written, '%Y/%c/%d' ) <= '2007/11/31'
GROUP BY ajt_cheques.cheque_id
)

[TheShaner]

On a UNION query, you must line up the SELECT fields exactly. Your first SELECT statement does not match on all fields with your other two SELECT statements. So on the below query, I put the id2 field after the id field in the first SELECT statement as it is with the other two.

Try this:
(
SELECT DATE_FORMAT( mia_booking_payment.date_received, '%d-%c-%Y' ) AS incoming_date, DATE_FORMAT( mia_booking_payment.date_received, '%Y-%m-%d' ) AS incoming_date2, CONCAT( '(booking ID: ', bid, ')' ) AS incoming_name, mia_booking_payment.amount_foreign AS incoming_value, CONCAT( 'Incoming Payment from ', booking.name ) AS description, booking.bid AS id, mia_booking_payment.id AS id2, mia_booking.closed AS closed, mia_booking.id AS booking_id, 1 AS num_bookings, running_balance.balance, running_balance.entry_id
FROM mia_booking_payment
JOIN booking ON mia_booking_payment.booking_id = booking.bid
JOIN mia_booking ON mia_booking_payment.booking_id = mia_booking.id
LEFT JOIN running_balance ON CONCAT( running_balance.payment_id, ' - ', running_balance.account ) = CONCAT( mia_booking_payment.id, ' - AOL UK' )
WHERE DATE_FORMAT( date_received, '%Y/%c/%d' ) >= '2007/11/01' AND DATE_FORMAT( date_received, '%Y/%c/%d' ) <= '2007/11/31' AND mia_booking_payment.account = 'AOL UK (Sterling)'
)
UNION (

SELECT DATE_FORMAT( ajt_cheques.written, '%d-%c-%Y' ) AS incoming_date, DATE_FORMAT( ajt_cheques.written, '%Y-%m-%d' ) AS incoming_date2, 'View Details' AS incoming_name, SUM( ajt_payments_az.value_gbp ) AS incoming_value, CONCAT( 'Batch Payment from ', accountfrom ) AS description, ajt_cheques.cheque_id AS id, ajt_cheques.cheque_id AS id2, 4 AS closed, ajt_payments_az.booking_id AS booking_id, COUNT( ajt_payments_az.booking_id ) AS num_bookings, running_balance.balance, running_balance.entry_id
FROM ajt_cheques
LEFT JOIN ajt_payments_az ON ajt_cheques.cheque_id = ajt_payments_az.cheque_id
LEFT JOIN running_balance ON CONCAT( running_balance.cheque_id, ' - ', running_balance.account ) = CONCAT( ajt_cheques.cheque_id, ' - AOL UK' )
WHERE accountto
LIKE '%AOL UK%' AND DATE_FORMAT( written, '%Y/%c/%d' ) >= '2007/11/01' AND DATE_FORMAT( written, '%Y/%c/%d' ) <= '2007/11/31'
GROUP BY ajt_cheques.cheque_id
)
UNION (

SELECT DATE_FORMAT( ajt_cheques.written, '%d-%c-%Y' ) AS incoming_date, DATE_FORMAT( ajt_cheques.written, '%Y-%m-%d' ) AS incoming_date2, 'View Details' AS incoming_name, ( 0 - SUM( ajt_payments_az.value_gbp ) ) AS incoming_value, CONCAT( 'Payment to ', accountto ) AS description, ajt_cheques.cheque_id AS id, ajt_cheques.cheque_id AS id2, 2 AS closed, ajt_payments_az.booking_id AS booking_id, COUNT( ajt_payments_az.booking_id ) AS num_bookings, running_balance.balance, running_balance.entry_id
FROM ajt_cheques
LEFT JOIN ajt_payments_az ON ajt_cheques.cheque_id = ajt_payments_az.cheque_id
LEFT JOIN running_balance ON CONCAT( running_balance.cheque_id, ' - ', running_balance.account ) = CONCAT( ajt_cheques.cheque_id, ' - AOL UK' )
WHERE accountfrom = 'AOL UK' AND DATE_FORMAT( written, '%Y/%c/%d' ) >= '2007/11/01' AND DATE_FORMAT( written, '%Y/%c/%d' ) <= '2007/11/31'
GROUP BY ajt_cheques.cheque_id
)
UNION (

SELECT DATE_FORMAT( date, '%d-%c-%Y' ) AS incoming_date, DATE_FORMAT( date, '%Y-%m-%d' ) AS incoming_date2, 'Manual Adjustment' AS incoming_name, amount AS incoming_value, description, id, 0 AS closed, NULL AS booking_id, id AS id2, NULL AS balance, NULL AS entry_id, 0 AS num_bookings
FROM balance_adjustment
WHERE date
LIKE '2007-11-%' AND account = 'AZ'
)
ORDER BY incoming_date2 ASC , id2 ASC LIMIT 0 , 30
-Shane

[NancyJ]

hmm, I never noticed that - it would explain a lot - its also fixed another issue I wasn't even aware of. I didn't write these queries, I'm just trying to get info out of them - I'm always amazed with this client how long things have been broken and no-one has noticed.

[TheShaner]

I'm sorry, I missed that last (fourth) SELECT statement, which also needs to be corrected:
(
SELECT DATE_FORMAT( mia_booking_payment.date_received, '%d-%c-%Y' ) AS incoming_date, DATE_FORMAT( mia_booking_payment.date_received, '%Y-%m-%d' ) AS incoming_date2, CONCAT( '(booking ID: ', bid, ')' ) AS incoming_name, mia_booking_payment.amount_foreign AS incoming_value, CONCAT( 'Incoming Payment from ', booking.name ) AS description, booking.bid AS id, mia_booking_payment.id AS id2, mia_booking.closed AS closed, mia_booking.id AS booking_id, 1 AS num_bookings, running_balance.balance, running_balance.entry_id
FROM mia_booking_payment
JOIN booking ON mia_booking_payment.booking_id = booking.bid
JOIN mia_booking ON mia_booking_payment.booking_id = mia_booking.id
LEFT JOIN running_balance ON CONCAT( running_balance.payment_id, ' - ', running_balance.account ) = CONCAT( mia_booking_payment.id, ' - AOL UK' )
WHERE DATE_FORMAT( date_received, '%Y/%c/%d' ) >= '2007/11/01' AND DATE_FORMAT( date_received, '%Y/%c/%d' ) <= '2007/11/31' AND mia_booking_payment.account = 'AOL UK (Sterling)'
)
UNION (

SELECT DATE_FORMAT( ajt_cheques.written, '%d-%c-%Y' ) AS incoming_date, DATE_FORMAT( ajt_cheques.written, '%Y-%m-%d' ) AS incoming_date2, 'View Details' AS incoming_name, SUM( ajt_payments_az.value_gbp ) AS incoming_value, CONCAT( 'Batch Payment from ', accountfrom ) AS description, ajt_cheques.cheque_id AS id, ajt_cheques.cheque_id AS id2, 4 AS closed, ajt_payments_az.booking_id AS booking_id, COUNT( ajt_payments_az.booking_id ) AS num_bookings, running_balance.balance, running_balance.entry_id
FROM ajt_cheques
LEFT JOIN ajt_payments_az ON ajt_cheques.cheque_id = ajt_payments_az.cheque_id
LEFT JOIN running_balance ON CONCAT( running_balance.cheque_id, ' - ', running_balance.account ) = CONCAT( ajt_cheques.cheque_id, ' - AOL UK' )
WHERE accountto
LIKE '%AOL UK%' AND DATE_FORMAT( written, '%Y/%c/%d' ) >= '2007/11/01' AND DATE_FORMAT( written, '%Y/%c/%d' ) <= '2007/11/31'
GROUP BY ajt_cheques.cheque_id
)
UNION (

SELECT DATE_FORMAT( ajt_cheques.written, '%d-%c-%Y' ) AS incoming_date, DATE_FORMAT( ajt_cheques.written, '%Y-%m-%d' ) AS incoming_date2, 'View Details' AS incoming_name, ( 0 - SUM( ajt_payments_az.value_gbp ) ) AS incoming_value, CONCAT( 'Payment to ', accountto ) AS description, ajt_cheques.cheque_id AS id, ajt_cheques.cheque_id AS id2, 2 AS closed, ajt_payments_az.booking_id AS booking_id, COUNT( ajt_payments_az.booking_id ) AS num_bookings, running_balance.balance, running_balance.entry_id
FROM ajt_cheques
LEFT JOIN ajt_payments_az ON ajt_cheques.cheque_id = ajt_payments_az.cheque_id
LEFT JOIN running_balance ON CONCAT( running_balance.cheque_id, ' - ', running_balance.account ) = CONCAT( ajt_cheques.cheque_id, ' - AOL UK' )
WHERE accountfrom = 'AOL UK' AND DATE_FORMAT( written, '%Y/%c/%d' ) >= '2007/11/01' AND DATE_FORMAT( written, '%Y/%c/%d' ) <= '2007/11/31'
GROUP BY ajt_cheques.cheque_id
)
UNION (

SELECT DATE_FORMAT( date, '%d-%c-%Y' ) AS incoming_date, DATE_FORMAT( date, '%Y-%m-%d' ) AS incoming_date2, 'Manual Adjustment' AS incoming_name, amount AS incoming_value, description, id, id AS id2, 0 AS closed, NULL AS booking_id, 0 AS num_bookings, NULL AS balance, NULL AS entry_id
FROM balance_adjustment
WHERE date
LIKE '2007-11-%' AND account = 'AZ'
)
ORDER BY incoming_date2 ASC , id2 ASC LIMIT 0 , 30
You gotta love when people have websites spitting out incorrect data to the world, haha.

-Shane