table joins - 3 tables

  • Thread starter Thread starter Rudi Ahlers
  • Start date Start date
R

Rudi Ahlers

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.


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
--

Kind Regards
Rudi Ahlers
+27 (82) 926 1689

For as he thinks in his heart, so he is. ... (Proverbs 23:7)
 
try something like this:
SELECT
CASE
WHEN d.Id IS NOT NULL THEN d.CompanyId
WHEN e.Old_Id IS NOT NULL THEN e.Old_Id
END As CompanyId,
c.Id,
c.Supplier
CONVERT(bit,
(CASE
WHEN d.Id IS NOT NULL THEN 1
ELSE 0) As IsCurrent
FROM
comments c
LEFT OUTER JOIN company d ON c.Id= d.Id
LEFT OUTER JOIN old_companies e ON c.Id = e.Old_Id
WHERE
/* to hide comments which have no valid company */
d.Id IS NOT NULL OR e.Old_Id IS NOT NULL
 
Sorry, this is in MSSQL server 2000. When I tried to run it, it told me that
The Query Designer does not support the CASE SQL construct.

--

Kind Regards
Rudi Ahlers
+27 (82) 926 1689

For as he thinks in his heart, so he is. ... (Proverbs 23:7)
 
Sorry, but that is bull****

Do you have the SQL BOL installed ??? If so paste the following in IE for
the CASE stuff:
(watch for wrapping)
mk:@MSITStore:C:\Program%20Files\Microsoft%20SQL%20Server\80\Tools\Books\tsq
lref.chm::/ts_ca-co_5t9v.htm

Eric
 
I saw what that link produced, and the stuff looks very similar to this
query that I was given to try out. but this is what happened when I ran this
query in the MSSQL SQL Server Enterprise Manager

I'm a bit new to ASP and MSSQL, but this is what it reported



--

Kind Regards
Rudi Ahlers
+27 (82) 926 1689

For as he thinks in his heart, so he is. ... (Proverbs 23:7)
 
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.
Click to expand...

This may or may not help. Are the "old_company" and "company" tables the
same except for the fact that "old" companies go in the old_company table?
Meaning, do they have the same CREATE TABLE statement?

If so, you are much better off just using one "company" table and adding a
true/false flag to it

CREATE TABLE company (
....create definitions...,
is_with_us int(1) DEFAULT 1,
PRIMARY KEY .....
)

You can then just mark the "is_with_us" column zero or one if it is an
"old" company or not.

Creating a completely separate table for that is a waste of time,
overhead, typing, etc. not to mention *much* more difficult to maintain!

later...
 
Back
Top