[RESOLVED] Query Structure

[rgermain]

I am having trouble writing a query for the following situation.

I have 4 tables and each table has a status field and applicant_id field and then each contain various other fields.

I want to get a single id that can be found in any of the tables and count it as one where a specific field is equal to submitted.
So take the following example.

table 1 - ids = 1 - stauts = "submitted" | ids = 2 - status = "process"
table 2 - ids = 2 - stauts = "process" | ids = 3 = "submitted"
table 3 - ids = 3 - stauts = "submitted" | ids = 1 = "sumbited"

I would like the query to return that there 2 results for that query.

I tired:
$q = "SELECT * FROM d_tv, c_tv, phone, internet WHERE status = 'submitted' GROUP BY applicant_id";

Error I am getting is: Error: Column 'status' in where clause is ambiguous

I am tried many various ways and things to make this work, but to no luck. Do I need to write out something that will do this?

[bpat1434]

Well, you need to reference each table and column name separately since they contain the same name(s). So you could structure your query like this:

SELECT *, COUNT(a.id) as count 
FROM d_tv AS a, c_tv AS b, phone AS c, internet AS d 
WHERE b.status='submitted' 
GROUP BY a.applicant_id

Something like that. That query won't work, but should point you in the right direction....

~Brett

[rgermain]

I got it working... Thank you for helping!!

[bpat1434]

glad to hear it.

~Brett