[lordmerlin]

Ok, I got three tables, company, comments and old_company.

In company are all the current companies, in old_company are companies who
have been suspended, and in comment, clients can make comments about the
companies, regardless if they are current or suspended companies.

I currently display all the comments on a page, but now I want to put an
icon next to the companies who are still with us, and another tick with the
suspended companies, making a clean indication as to which is which.

I tried the following statement, and it worked, but I can't tell which of
these are current, and whether some are left out, since there are 17739
comments at this moment, and new comments are made daily.

Code:

SELECT	 company.id AS companyid, comments.id, comments.supplier,
company.name
FROM		 comments RIGHT OUTER JOIN
					  company ON comments.supplier = company.name

How would I be able to tell which of these companies are current, and which
are old

The similarities are as follows:

comments.table companies.table old_companies.table
id id old_id
supplier name old_name

[jake]

Try doing a UNION on the two company tables, then joing the COmments table to that. e.g:-


SELECT Companies.CompanyId,
Companies.CompanyStatus,
Comments.Id,
Comments.Supplier
FROM Comments
LEFT JOIN
( SELECT id AS CompanyId, "Current" AS CompanyStatus, name as CompanyName FROM Company
UNION
SELECT old_id AS CompanyId, "Suspended" AS CompanyStatus, old_name AS CompanyName FROM Old_Company
) Companies
ON Comments.id = Companies.CompanyId

Originally Posted by lordmerlin

Ok, I got three tables, company, comments and old_company.

In company are all the current companies, in old_company are companies who
have been suspended, and in comment, clients can make comments about the
companies, regardless if they are current or suspended companies.

I currently display all the comments on a page, but now I want to put an
icon next to the companies who are still with us, and another tick with the
suspended companies, making a clean indication as to which is which.

I tried the following statement, and it worked, but I can't tell which of
these are current, and whether some are left out, since there are 17739
comments at this moment, and new comments are made daily.

Code:

SELECT	 company.id AS companyid, comments.id, comments.supplier,
company.name
FROM		 comments RIGHT OUTER JOIN
					 company ON comments.supplier = company.name

How would I be able to tell which of these companies are current, and which
are old

The similarities are as follows:

comments.table companies.table old_companies.table
id id old_id
supplier name old_name