[RESOLVED] Code question?? Best way?

Nomaad

OK, I have 4 tables in MySQL, one main table that stores the contact information and a primary ID that then gets stored in the other 3 tables as folks complete different parts of the site. I am now doing a reporting site that needs to look at all 4 tables to determine who completed which parts of the site.

Right now I have the queries of the 3 other tables in the loop that builds the main table and they loop through based on that primary ID to determine if they have an entry in the other 3 tables....

Is this a heavy handed way of doing this or should I be using JOINs to complete the process? And if yes, suggestions for doing a 4 way join???

Thanks.

Desdinova

could you paste database structure? it would make it easier to write a query if we know the fields.

I think using a 'group by' and using a good join would be doable.

Nomaad

db structures:

MAIN:
mss_id - primary
business_Name
address
city
state
zip
....and a bunch O other stuff....

SUB-Tables:
SCAN:
scan_id - primary
mss_id - from MAIN
question responses that will report in another page

WHITEPAPER:
data_id - primary
mss_id - from MAIN
registration form information

CONTACT:
data_id - primary
mss_id - from MAIN
registration information

As the end user completes a step the data gets stored to cross-reference against their main contact entry in the MAIN table. For the main report page I just need to show with a yes or no that a step has been completed, then it will link to a more concise page that shows response to each step of completion. The problem I am running into was with Record Set Paging, it would only pull the 1st row ID in each page unless I put the queries into the loop statement to get all IDs being displayed.

Desdinova

try this:

SELECT
	MAIN.*,
	SCAN.scan_id,
	CONTACT.data_id
FROM
	MAIN
LEFT JOIN
	SCAN
ON
	MAIN.mss_id = MAIN.mss_id
LEFT JOIN
	WHITEPAPER
ON
	MAIN.mss_id = WHITEPAPER.mss_id
LEFT JOIN
	CONTACT
ON
	MAIN.mss_id = CONTACT.mss_id
GROUP BY
	MAIN.mss_id

it should return the full main table, and only the id's of the other tables. It should return NULL if there's no record, else it would return the matchin ID.

You might be able to remove the 'group by' clause. You'll have to try.