CiviCRM Community Forums (archive)

Right now I'm trying to write my own function to query information required from the civicrm database for an AJAX call since the civicrm API functions for returning data are too inefficient for my needs. However I'm getting certain rows reported more than once when I pull information from more than two tables. Probably because I'm not very good with SQL.

Example query with 2 tables:
SELECT civicrm_contact.first_name AS first, civicrm_contact.last_name AS last, civicrm_participant.id AS id FROM civicrm_participant, civicrm_contact WHERE civicrm_participant.contact_id=civicrm_contact.id

This is returning (with output format being first - last - id)
Mister - Johnson - 2139
Mister - Ross - 2140
Mister - Van Meter - 2141

However with 3 tables (in this case also trying to pull their street address)

SELECT civicrm_contact.first_name AS first, civicrm_contact.last_name AS last, civicrm_participant.id AS id, civicrm_address.street_address AS sreet FROM civicrm_participant, civicrm_contact, civicrm_address WHERE civicrm_participant.contact_id=civicrm_contact.id AND civicrm_address.contact_id=civicrm_participant.contact_id

This returns
Mister - Johnson - 2139
Mister - Johnson - 2139
Mister - Ross - 2140
Mister - Ross - 2140
Mister - Van Meter - 2141
Mister - Van Meter - 2141


Does anyone have any idea why this may be?

This time I'm outputting what I'm getting with the three table example except with the format first, last, id, street if not null. It doesn't seem to be consistent.

Mister Johnson 2139
Mister Johnson 2139
Mister Ross 2140 722 S. Apple Trail
Mister Ross 2140
Mister Van Meter 2141 911 Example St. Apt C
Mister Van Meter 2141

Looking further down the line, no one in this database has multiple addresses. Just one address listed or null. The address always only shows up in the first listing.

This means that when my query is parsed, its not joining my results together right? I'm not sure what to do since I thought that by confirming that id's from all tables matched it would ensure I'd only get one table with unique rows.