Posted by Hilarion on 11/16/05 13:24

> Pls help me. I have very important problem.
>
> I have 3 tables:
> tab1 - objects, index field [oid]
> tab2 - propertyes, index field [thid]
> tab3 - linc table in format: [oid] - [thid], relation -
> [great many] to [great many]
>
> How i get table per oid in format
> thid | thid.name | status
> where:
> thid - field [thid] tab2
> thid.name - field [name] tab2
> status - { =1 if the tab3 contain record [oid] - [thid]
> =0, if the tab3 NOT contain record
> [oid] - [thid]
> }


It'd be better if you post CREATE statements for your
tables with some sample data in the future because your
description is a bit confusing. You should also use
a subject that suggests what kind of problem you have
(most people posting here have questions and need help).

The solution for your problem is in using join and
some aggregation. You did not specify what DBMS
you use (eg. MySQL 4, Oracle 8i), so the solution
I'll give you may require some modifications before
it'll work for you:


SELECT
tab2.thid,
tab2.name,
SIGNUM( COUNT( tab3.oid ) ) AS status
FROM
tab2 LEFT OUTER JOIN
tab3 ON tab2.thid = tab3.thid AND tab3.oid = $some_specified_oid
GROUP BY
tab2.thid,
tab2.name
ORDER BY
tab2.name ASC,
tab2.thid ASC


Hilarion

[Back to original message]