Flexi query for one to many relationship?

[Former Member]

I have two types A and B. They are in a one to many relationship as A-----one-----to------many-----B. Qualifier for A is a and B is b. How do I run a flexiquery to get all the rows of A join all the rows of B?

 <relation generate="true" localized="false" code="A2B" autocreate="true">
     <sourceElement type="A" qualifier="a" cardinality="one">                                                                                                                                             </sourceElement>                                        
     <targetElement type="B" qualifier="b" cardinality="many" collectiontype="list">
         <modifiers read="true" write="true" optional="true"    partof="true" />
     </targetElement>
 </relation>

[agrabovskis · Accepted solution]

 SELECT *
 FROM {A
 JOIN B ON {B:a} = {A:pk}
 }

A bit better example (with more meaningful entities):

 SELECT *
 FROM {Product AS p
 JOIN CatalogVersion AS cv ON {cv:pk} = {p:catalogVersion}
 }

[Former Member · Accepted solution]

usually B has the owner attribute:

 JOIN B ON {B:owner} = {A:pk}

[Former Member · Accepted solution]

Can you please post example for querying many to many relation as well?

[Former Member]

flexi query can be fired for many to many relations (deployment details are provided for many to many relations)

for one to many relations, item listed in targetElement contains the pk of the item listed in the sourceElement.

FYI source and target are the columns in the relation table that stores the pk of source and target items.

[Former Member]

A query would be most helpful.

[ishmeet_singh]

SELECT {A.PK} FROM {A}, {B}, {ABRELATION AS ABR} WHERE {ABR.SOURCE}={A.PK} AND {ABR.TARGET}={B.PK}

The above query will return all the values for a many to many relation. A is source item type, B is target and ABRelation is the relation defined in items.xml.

You can further add conditions in WHERE clause to retrieve data.