Hi SQL heroes! Well, at least you have a bit more knowledge about it that I have.
I have a simple model: A Product is associated with a Representation (in this case, an exception report). I am trying to construct an SQL query that can report to me which Products have one (or more) Representations associated to them.
The needed information is in 2 tables: Elements (Product and Representation) and Relationships (AssociationRelationship)
The Relationship items carry the IDs of both linked Elements. I am not able to SQL select the Product names and Representation names that are linked through the Association.
Does anyone here have an idea or an example I can experiment with?
Thanks!!!
Hi,
This should be a good start:
SELECT product.name AS 'Product', representation.name AS 'Representation' FROM Elements AS product,Relationships AS rel,Elements AS representation WHERE representation.id = rel.sourceid AND product.id = rel.targetid AND product.type = 'Product' AND representation.type = 'Representation' AND rel.type = 'AssociationRelationship'
Regards,
JB
Oh, that appears to do the trick! I will run it across some bigger models and see if it holds up, but that is GOLDEN.
Thank you very much!
I was able to use this and expand it a bit so I can view the properties of the linked item as well.
Here is the structure I came up with, and it's easily expanded.
SELECT
product.name AS 'PDC Dienst',
linked.name AS 'PDC Exceptie',
prop.propvalue AS 'Vervallen op',
status.propvalue AS 'Status'
FROM
Elements AS product,
Relationships AS rel,
Elements AS linked,
Properties AS prop,
Properties AS status
WHERE
linked.id = rel.sourceid AND product.id = rel.targetid
AND rel.type = 'AssociationRelationship'
AND product.specialization = 'PDC Dienst'
AND linked.specialization = 'PDC Exceptie'
AND linked.id = prop.conceptid
AND prop.propkey = 'Datum Verval'
AND linked.id = status.conceptid
AND status.propkey = 'Status'
Hi,
Quote from: wiebow on February 05, 2023, 17:48:52 PMthat is GOLDEN.
Happy to help ;-)
Note that this syntax can have some performance issues sometime. If you face such issues, then switch to the INNER JOIN syntax: https://github.com/AlaSQL/alasql/wiki/Join
Regards,
JB
It sure has some performance issues :)
Thanks, I'll inspect some more.