0

I am working on a database for a client of mine and on one of the pages (written in PHP and HTML) i need to display all of the information on their clients, but when i run the following query (Supposed to select all of the rows in the table 'families') it returns a total of 0 rows. When it should return all of the rows in the table

SELECT
families.id AS fam_id,
families.last_name AS fam_surname,
families.address_1 AS fam_address_1,
families.address_2 AS fam_address_2,
families.city_id AS fam_city,
families.phone AS fam_phone,
families.mobile AS fam_mobile,
families.email AS fam_email,
families.f_d_worker_1 AS fam_fdw_1,
families.f_d_worker_2 AS fam_fdw_2,
families.status_id AS fam_status_id,
families.trans_date AS fam_trans_date,
families.entry_date AS fam_entry_date,
families.exit_date AS fam_exit_date,
families.eligible_date AS fam_eligible_date,
families.active_date AS fam_active_date,
families.lga_loc_id AS fam_lga_id,
families.facs_loc_id AS fam_facs_id,
families.ind_status_id AS fam_indig_id,
families.referral_id AS fam_ref_id,
families.active_status AS fam_act_status,
families.comm_org_id AS fam_com_org,
city.id AS city_id,
city.name AS city_name,
city.state_id AS city_state,
city.post_code AS post_code,
states.id AS state_id,
states.long_name AS state_name,
states.abbrev AS state_abbrev,
client_status.id AS client_stat_id,
client_status.name AS client_stat_name,
community_org.id AS com_org_id,
community_org.name AS com_org_name,
facs_location.id AS facs_id,
facs_location.name AS facs_name,
lga_location.id AS lga_id,
lga_location.name as lga_name,
indig_status.id AS indig_id,
indig_status.name AS indig_name,
referrals.id AS ref_id,
referrals.name AS ref_name,
f_d_workers.id AS fdw_id,
f_d_workers.first_name AS fdw_first_name,
f_d_workers.last_name AS fdw_last_name,
client_status.id AS client_id,
client_status.name AS client_name
FROM
`families`,
`city`,
`client_status`,
`community_org`,
`facs_location`,
`f_d_workers`,
`indig_status`,
`lga_location`,
`referrals`,
`states`
WHERE
families.city_id = city.id AND
families.f_d_worker_1 = f_d_workers.id AND
families.f_d_worker_2 = f_d_workers.id AND
families.status_id = client_status.id AND
families.lga_loc_id = lga_location.id AND
families.facs_loc_id = facs_location.id AND
families.ind_status_id = indig_status.id AND
families.referral_id = referrals.id AND
families.comm_org_id = community_org.id
Improve this question
2
  • You really should be using an INNER JOIN for this... Commented Feb 6, 2014 at 1:58
  • @hichris123 I have no experience with the INNER JOIN function, and with a little research i don't see it as appropriate for my situation. Could you please elaborate on how it could be used? Commented Feb 6, 2014 at 2:44

1 Answer 1

Reset to default
1

Without seeing your schema or your data, I am going to guess that one or more of the joins in your query is not working the way you think it is going to work. For example, the query implies that every families record will have non-null values for city_id, f_d_worker_1, f_d_worker_2, status_id, lga_loc_id, facs_loc_id, ind_status_id, referral_id, and comm_org_id. If the families record has values for each of these fields, then it would appear that there is no matching id field in one or more of the corresponding table (city, f_d_workers, client_status, lga_locations, facs_locations, indig_status, referrals, community_org).

The first step I typically use when a complex query does not work the way I think it should be working is to convert inner joins to outer joins, and then simply look for the missing records.

If, on the other hand, one or more of your join elements is optional (ie, there may not be a f_d_worker_2 id), then you should be using outer joins in the query itself. I hope this helps.

UPDATED

SELECT
families.id AS fam_id,
families.last_name AS fam_surname,
families.address_1 AS fam_address_1,
families.address_2 AS fam_address_2,
families.city_id AS fam_city,
families.phone AS fam_phone,
families.mobile AS fam_mobile,
families.email AS fam_email,
families.f_d_worker_1 AS fam_fdw_1,
families.f_d_worker_2 AS fam_fdw_2,
families.status_id AS fam_status_id,
families.trans_date AS fam_trans_date,
families.entry_date AS fam_entry_date,
families.exit_date AS fam_exit_date,
families.eligible_date AS fam_eligible_date,
families.active_date AS fam_active_date,
families.lga_loc_id AS fam_lga_id,
families.facs_loc_id AS fam_facs_id,
families.ind_status_id AS fam_indig_id,
families.referral_id AS fam_ref_id,
families.active_status AS fam_act_status,
families.comm_org_id AS fam_com_org,
city.id AS city_id,
city.name AS city_name,
city.state_id AS city_state,
city.post_code AS post_code,
states.id AS state_id,
states.long_name AS state_name,
states.abbrev AS state_abbrev,
client_status.id AS client_stat_id,
client_status.name AS client_stat_name,
community_org.id AS com_org_id,
community_org.name AS com_org_name,
facs_location.id AS facs_id,
facs_location.name AS facs_name,
lga_location.id AS lga_id,
lga_location.name as lga_name,
indig_status.id AS indig_id,
indig_status.name AS indig_name,
referrals.id AS ref_id,
referrals.name AS ref_name,
workers1.id AS fdw1_id,
workers1.first_name AS fdw1_first_name,
workers1.last_name AS fdw1_last_name,
workers2.id AS fdw2_id,
workers2.first_name AS fdw2_first_name,
workers2.last_name AS fdw2_last_name,
client_status.id AS client_id,
client_status.name AS client_name
FROM
`families` 
LEFT OUTER JOIN `city` ON families.city_id = city.id
LEFT OUTER JOIN `client_status` ON families.status_id = client_status.id
LEFT OUTER JOIN `community_org` ON families.comm_org_id = community_org.id
LEFT OUTER JOIN `facs_location` ON families.facs_loc_id = facs_location.id
LEFT OUTER JOIN `f_d_workers` AS workers1 ON families.f_d_worker_1 = workers1.id
LEFT OUTER JOIN `f_d_workers` AS workers2 ON families.f_d_worker_2 = workers2.id
LEFT OUTER JOIN `indig_status` ON families.ind_status_id = indig_status.id
LEFT OUTER JOIN `lga_location` ON families.lga_loc_id = lga_location.id
LEFT OUTER JOIN `referrals` ON families.referral_id = referrals.id 
LEFT OUTER JOIN `states` ON city.state_id = states.id

I think this is what you were trying to get at.

Improve this answer
Sign up to request clarification or add additional context in comments.

6 Comments

Here is a SQL Fiddle of the Schema and Query. I hope it helps. I have a fair bit of experience with SQL but these bigger querys seem to be doing my head in a little. sqlfiddle.com/#!2/b48d1
It appears that you are populating zeroes into the families record for city, f_d_worker_1, f_d_worker_2, etc. There are no corresponding entries in cities, f_d_workers, etc. with an id = 0. That is why the query is not returning anything. If those values should be optional, then make the joins outer joins rather than inner joins. If they are not optional, populate the families record with valid data (ie id values that exist in the lookup tables).
This did cross my mind at one stage but i do not have experience with the difference between inner and outer joins, so it was only a brief thought. I will do a little research and hopefully come up with the correct query :)
I'm not sure whether i need to open another question for this but, i have hit another issue. I had it working the intended way with a simplified OUTER JOIN query but now that i am using the full query it once again is not working. But it is giving an error this time. sqlfiddle.com/#!2/b48d1/21
You are moving in the right direction. You are going to need a few more joins though (one for each table that families references.
|

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.