WebCOBRA.com Community Message Boards

mhowell
Posts: 7
Since: 09/24/2007

I was looking at the list of fields to pull from, and noticed that the BenefitPlan table had 5 fields, one being CarrierSponsor_ID. The description for that field is - ID of parent carrier-sponsor. But when I look at the fields for the table CarrierSponsor, the CarrerSponsor_ID field is not listed - only the clientEmployer_ID.

If I were to pull down all data in both tables, how would I know which benefit plan goes with which carrier?

Similar problem for the ClientEmployer table.

And for some reason, I always have error posting, like the forum engine doesn't like special characters. What are those characters?

jwolgamott
Posts: 257
Since: 09/26/2005

Each table has an "ID" column, which represents that entity's ID.

so if a BenefitPlan had a CarrierSponsor_ID of 25, you'd look for CarrierSponsor that had a CarrierSponsor.ID = 25

Here's a sample query:

---
select * from BenefitPlans
inner join CarrierSponsors on CarrierSponsors.ID = BenefitPlans.Carrier Sponsor_ID
---

You could easily add where clauses and groupings.

And finally... I think the error on postings are with the size of the Subject field.. if it's too long, we get an error.

mhowell
Posts: 7
Since: 09/24/2007

That makes sense about the title being too long. I'll be less verbose :-)

About the ID field, cool, I'll try that next then, but your SQL prompted another question: Are we able to do a SELECT * ... query, or do we have to list each field separately? Can we do inner joins? If so, what kind of results object should we cast that to so we can cycle through the rows?

Matthew

jwolgamott
Posts: 257
Since: 09/26/2005

Matthew --

Sorry, I assumed you were doing the SQL queries in the Access Data Loader. Based on your reply (and you're other posts), you're using the raw API, right?

If so, you're right, you can't do either SELECT *'s or Inner joins, so that's a bad example.

To get a Benefit Plan and Carrier array of objects, you'll want to get an array of carriers from a query. Then, do a foreach loop through them, and execute a separate query. Something along the lines of:

Carriers[] = get_carrier_list()
foreach (carrier in Carriers)
{
Plans_for_Carrier = API.Query("select id, name from BenefitPlans where CarrierSponsor_ID = " + carrier.id)
// do something interesting with your plan list
}

Hope that pseudo code makes sense.