1 Topic by mathmathou (edited by mathmathou 2017-04-27 00:38:50)

  • Registered: 2015-02-24
  • Posts: 446

Topic: [Solved] Button SQL Query not working ?

Hello Dmitry,


The normal SQLQuery button works fine. A query like

SELECT
artist.id,
artist.artist_name
FROM
tmp_table
INNER JOIN artist ON artist.id = tmp_table.id1
INNER JOIN vendor ON vendor.id = tmp_table.id3
WHERE tmp_table.flag = 'art'
ORDER BY upper(artist_name) ASC

works fine, and even with 'delete_col, Artist Name' as colums titles, the ID of the record is found without problem


Bu then I needed to fill 1 Tablegrid with the results of 2 queries, so I wrote a query like :

SELECT
artist.id,
artist.artist_name
FROM
artist
INNER JOIN tmp_table ON artist.id = tmp_table.id1

UNION

SELECT
artist.id,
artist.artist_name
FROM
artist
INNER JOIN artist_asset ON artist_asset.id_artist = artist.id
WHERE artist_asset.id_asset = {edAID}

The results of those two queries in one are displayed as expected in the Tablegrid, this works fine, but the ID of the record (dbItemID) seems to be lots in the process... Clicking in the tablegrid to get the ID gives a result of -1;


Is this due to the use of the UNION in my query ?


Cheers


Mathias

I'm a very good housekeeper !
Each time I get a divorce, I keep the house
Zaza Gabor

2 Reply by tcoton (edited by tcoton 2017-04-24 15:20:43)

  • Registered: 2014-06-25
  • Posts: 863

Re: [Solved] Button SQL Query not working ?

It might be because of the select artist.id which is is the same for each query, I had a similar issue some times ago.

you should try to use aliases to distinguish the artist.id from each query.

SELECT
A.artist.id,
A.artist.artist_name
FROM
artist as A
INNER JOIN tmp_table ON A.artist.id = tmp_table.id1

UNION

SELECT
B.artist.id,
B.artist.artist_name
FROM
artist as B
INNER JOIN artist_asset ON artist_asset.id_artist = B.artist.id
WHERE artist_asset.id_asset = {edAID}
  • Registered: 2015-02-24
  • Posts: 446

Re: [Solved] Button SQL Query not working ?

Thanks for your answer tcoton.


I tried it first thing this morning, but the result is the same : rows are displayed correctly in the grid, bu the id is lost in the process.


Thanks for trying, I'll find another solution smile


Cheers


Mathias

I'm a very good housekeeper !
Each time I get a divorce, I keep the house
Zaza Gabor
  • Registered: 2014-02-14
  • Posts: 10,294

Re: [Solved] Button SQL Query not working ?

Check it out

SELECT
artist.id as "id",
artist.artist_name
FROM
artist
INNER JOIN tmp_table ON artist.id = tmp_table.id1

UNION

SELECT
artist.id as "id",
artist.artist_name
FROM
artist
INNER JOIN artist_asset ON artist_asset.id_artist = artist.id
WHERE artist_asset.id_asset = {edAID}
Dmitry.
  • Registered: 2015-02-24
  • Posts: 446

Re: [Solved] Button SQL Query not working ?

God... how did I not think about this in the first place... smile


Thanks a thousand times Dmitry, it works !!


Cheers


Mathias

I'm a very good housekeeper !
Each time I get a divorce, I keep the house
Zaza Gabor