Ads were blocked - no problem. But keep in mind that developing HeidiSQL, user support and hosting takes time and money. You may want to send a donation instead.

Combining results query in Heidi

closedminded's profile image closedminded posted 2 months ago in Feature discussion Permalink

I am trying to assist a nonprofit that has access to Heidi for the backend but nothing else. I need to run the following queries against about 1000 instances of the database and create a master list of the results. The two queries below will produce the desired results but it populates to two tabs in Heidi and won't combine into 1 query. They will need to run this report once a week so producing 1000 tabs and trying to manually export and combine those files is not an option. From each databse instance, I am attempting to pull the last time the ticket was used for each customer. This isn't my database and I don't have the ability to edit just access the results through Heidi on a restricted machine. UNION doesn't seem to work and I don't have rights to output the information to a file on the server. Is there any way I can have these results displayed in one query tab in Heidi so I can export the results list from Heidi in one file? Every db instance is identical and I need the same fields from each query.

USE db1-customer1; SELECT ticket.location_id, ticket.updated_on, location.legal_name FROM ticket, location ORDER BY ticket.updated_on DESC LIMIT 1;

USE db2-customer1; SELECT ticket.location_id, ticket.updated_on, location.legal_name FROM ticket, location ORDER BY ticket.updated_on DESC LIMIT 1;

ansgar's profile image ansgar posted 2 months ago Permalink

UNION is the solution to that. Try such a query:

(SELECT '1' as customer, t.location_id, t.updated_on, l.legal_name
  FROM `db1-customer1`.ticket AS t, `db1-customer1`.location AS l)
UNION
(SELECT '2' as customer, t.location_id, t.updated_on, l.legal_name
  FROM `db1-customer2`.ticket AS t, `db1-customer2`.location AS l)
ORDER BY updated_on DESC;

Note that the ORDER BY applies to the merged result, so you cannot use the local table aliases there.

closedminded's profile image closedminded posted 2 months ago Permalink

Thank you. Is there any way to limit the return for each ticket holder to the most recent result? I only need 1 return per query and that is the most recent item in the t.updated field. This returned the first 20 of each.

Thanks again for your help!

ansgar's profile image ansgar posted 2 months ago Permalink

One way would be to add a WHERE clause which just matches the most current row. LIMIT seems not supported in the single queries of a UNION.

Please login to leave a reply, or register at first.




Ads were blocked - no problem. But keep in mind that developing HeidiSQL, user support and hosting takes time and money. You may want to send a donation instead.