Yuval Hager <yuval@avramzon.net> writes:
Let's say I have two content types - a person and a bank account. A person can have several bank accounts, and a person also has an address.
Now I need to present a listing if all the people who live in New Jersey and have more than $100k in their bank account. In any database application it would be dead easy - just use a SELECT.. JOIN on these two tables and you're done.
However, on Drupal, I am not sure what is the best way to accomplish that.
CCK and Views are great and a lot can be accomplished via the UI, but sometimes you just write your own SELECT statement.
Of course I can write the query myself, but since these are CCK nodes, I can't use a direct SELECT...
Yes, you can. Node type person with a "state" field. Node type bankacct with an "owner" field that is a nodereference to the person that owns it and a "balance" field. -- select each person/acct pair SELECT person.nid, acct.nid FROM {node} person -- connect to the CCK person table INNER JOIN content_type_person ct_person ON ct_person.nid = person.nid -- connect to all CCK bankaccts that are owned by this person INNER JOIN content_field_bankacct acct ON acct.field_owner_nid = person.nid -- impose the conditions WHERE person.type = 'person' AND ct_person.state = 'NJ' AND acct.field_balance > 100000 The WHERE person.type = 'person' clause isn't really necessary b/c of the joint to content_type_person. This gets all person/account pairs over $100k. If you just want the person nids, SELECT DISTINCT person.nid instead. IMPORTANT note: Create an index on acct.field_owner_nid. CCK does not do this for you (I think it should). And run your query through EXPLAIN when you are done to see what other indices you might need. Barry