Hi

Ok, so I've got a database managing Companies, and Groups. A Company can have many Groups.

Some fields are duplicated between the Companies and Groups tables; Payment Method, for instance. If the Payment Method for a Group isn't set (ie: is NULL), then the system should display the Payment Method for the parent Company, and display a notification that it's using this "parent" value.

Additionally, the Payment Method stored against a Company or Group is, of course, just an ID. So a join to the Payment Methods table is required to get the "label" value for the Payment Method.

Below is my attempt to fetch this data for a couple of the fields in question. I've ended up doing a subquery for each field. Bearing in mind that there are about ten such fields, and could be more, it looks horribly inefficient.

Is there a more elegant solution?

Code:
SELECT
	gr.group_name,
	co.id AS company_id,
	co.company_name,
	
	pm.payment_method_id,
	pm.using_group AS pm_using_group,
	pm.using_company AS pm_using_company,
	payment_method.method AS payment_method_label,

	inv.invoice_delivery_method_id,
	inv.using_group AS inv_using_group,
	inv.using_company AS inv_using_company,
	invoice_delivery_method.method AS invoice_delivery_method_label

FROM client_group gr
INNER JOIN client_company co ON gr.client_company = co.id

INNER JOIN (
	SELECT
		COALESCE(client_group.payment_method, client_company.payment_method) AS payment_method_id,
		NOT ISNULL(client_group.payment_method) AS using_group,
		NOT ISNULL(client_company.payment_method) AS using_company
	FROM client_group
	INNER JOIN client_company ON client_group.client_company = client_company.id
) AS pm
INNER JOIN payment_method ON pm.payment_method_id = payment_method.id

INNER JOIN (
	SELECT
		COALESCE(client_group.invoice_delivery_method, client_company.invoice_delivery_method) AS invoice_delivery_method_id,
		NOT ISNULL(client_group.invoice_delivery_method) AS using_group,
		NOT ISNULL(client_company.invoice_delivery_method) AS using_company
	FROM client_group
	INNER JOIN client_company ON client_group.client_company = client_company.id
) AS inv
INNER JOIN invoice_delivery_method ON inv.invoice_delivery_method_id = invoice_delivery_method.id
NB: It may also be worth mentioning that there's a third level to this. As well as Companies and Groups, there are of course People. A Group can have many People. In fact, People can be attached directly to Companies and the Company may have no Groups at all. Once I've got this working for Companies and Groups, I'll need to expand it to get the relevant fields for People.