I work with a database of several hundred schemas with 250 tables and thousands of records. PGAdmin III 1.22 as my only tool. A graphical interface of the database is not written in a robust way and it is sometimes possible to index several entries. I try to write a query that displays a list of indexed items and displays their LABEL to users rather than their ID. This allows the user to use the graphics program to solve the problem without the need for postgresql knowledge.
Here is the query at this point:
SET search_path = "SCHEMA_NAME"; TO SELECT e1.label, c1.label, h1.contentsid, h1.groupe OF hostdata like h1 INNER JOIN hostdata as h2 on h1.group = h2.group and h1.contentsid = h2.contentsid and h1.id! = h2.id INNER JOIN class as c1 on t1.classid = c1.id INNER JOIN element like e1 on t1.elementid = e1.id ORDERED BY t1.group ASC, t1.contentsid ASC, c1.label ASC, e1.label ASC;
Cross-indexing occurs when the GROUP and CONTENTSID fields are duplicated in two or more records in the HOSTDATA table. It is ok to duplicate one as long as the two fields are not identical. If that counts, the GROUP field is text.
What I'm trying to do is to compare the HOSTDATA records, list those that match another record in the GROUP and CONTENTSID fields, and then list them with the LABELs in the CLASS and ELEMENT tables.
The query above works perfectly as long as there are only two matches, but as soon as the number of matches exceeds the result, the results multiply – 8 matches in 7 statements per record – one for each correspondence – 8 times 7 instead of a list This is obviously due to the fact that the first INNER JOIN returns several true conditions.
Performance is not a problem. I have not yet found a way to rearrange sorting and querying to return the desired truncated output.