Multi table select

Databases
1

I will admit I may not have the best design, but here it is:

Table 1: rfc
Field: rfc_id
More…

Table 2: service
Field: serv_id
More…

Table 3: bu
Field: bu_id
Field: email
More…

Table 4: rfc2serv
Field: serv_id
Field: rfc_id

Table 5: bu2serv
Field: serv_id
Field: bu_id

The intent is to have many records in the service table each of these point back to a record in the bu table. A many to one relationship (multiple services can point to one bu record).
I use the bu2serv to make this relationship.
As my customers create a rfc record, they select services via checkboxes on the form.
I use the rfc2serv to make this relationship.
So far, I am able to manage all the tables. But I am having difficulty getting a query to work that maps the email address in the bu table back to the rfc record.
The query I am using is:
“SELECT bu.email FROM bu AS busunit
LEFT JOIN (rfc2serv, bu2serv, bu)
ON (rfc2serv.serv_id = bu2serv.serv_id AND bu2serv.bu_id = busunit.bu_id)
WHERE rfc2serv.rfc_id = ‘$rfc_id’”

I get all email records from all bu records.
If I have three bu records (ex: food, drinks, snacks) and I select food in the rfc record, I get the email for drinks and snacks as well.)
I am not concerned if I have two services that are part of the same bu. (ex. If I have hotdogs and hamburgers in the service table and they both belong to the food bu, I would and should get the same email twice. That is easy to remedy with the array_unique function as I iterate through the results.
The query as it is written does not throw an error, it just gives me too much info.

Can someone give me guidance?

2

r937, thank you for your help. I agree the query was poorly written!

3

could you do a mysqldump for these tables, please, and include enough INSERT statements that we can do some testing…

right now, it’s pretty difficult to understand what your query is trying to do

the query itself is badly written, and needs to be corrected, but i’m not sure which tables and columns to use…

4

First of all, if there’s a one to many relationship, you don’t need an extra table. Having a FK column in the ‘many’ table would be enough.

For example, if 1 bu can have multiple services, but each service can belong to only 1 bu (a one to many relationship), you can add a bu_id column to the services table.

About your query: it’s not exacty clear to me what you want, but I guess it’s something like this:


SELECT DISTINCT
    bu.email 
FROM bu
INNER JOIN bu2serv
ON bu.bu_id = bu2serv.bu_id
INNER JOIN rfc2serv
ON bu2serv.serv_id = rfc2serv.serv_id
WHERE rfc2serv.rfc_id = '$rfc_id'
5

Thank you Guido. That query does exactly what I need. Thank you.