Express question, need help

Hi colleagues
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]
}

Tnx

Re: Express question, need help

> 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

solution

select id, name, oid is NOT null as status from tab_things p
left join object_things o on o.oid = 1 and o.thid = p.id

Re: solution

> select id, name, oid is NOT null as status from tab_things p
> left join object_things o on o.oid = 1 and o.thid = p.id


Where does "id" come from? If it's "thid", then you should
use "p.thid" ("thid" is defined in both used tables, so
you have to decide which one to use).
Also your solution (without aggregation) will return more
than one row for one "thid" when there's more than one
row for it in the "object_things" table.


Hilarion