Selecting all from Super-Types and Sub-Types

[Quish]

Hi There

I am wanting to create a query that will select all entries from all tables within a super and sub types that are linked to a house using house_id in table tenant house_id.

my tables are

Tenant - super-type - PK tenant_id
Professional - sub-type - PK-FK tenant_id
Student - sub-type - PK-FK tenant_id

I keep getting multiple rows displaying the same values even though they should be different.

Hope you can help

Many Thanks

[ck9663]

Could you post what you have so far?

-- CK

[Quish]

Hi There

This returns 6 identical records apart for tenant_id which is repeated 3 times for each ID

select distinct * 
from tenant t, student s, professional p, house h
where t.tenant_ID = s.tenant_ID
and t.house_id = 1
or t.tenant_ID = p.tenant_ID
and t.house_id = 1

This is my second attempt but returns 2 identical records apart for tenant_id which changes

 

select distinct * 
from tenant t, student s, professional p
where t.tenant_ID = s.tenant_ID
and t.house_id = 1
or t.tenant_ID = p.tenant_ID
and t.house_id = 1

If I replace the OR with an AND I get no rows returned.

Regards

Quish

[ck9663]

Hi There

This returns 6 identical records apart for tenant_id which is repeated 3 times for each ID

select distinct * 
from tenant t, student s, professional p, house h
where t.tenant_ID = s.tenant_ID
and t.house_id = 1
or t.tenant_ID = p.tenant_ID
and t.house_id = 1

This is my second attempt but returns 2 identical records apart for tenant_id which changes

 

select distinct * 
from tenant t, student s, professional p
where t.tenant_ID = s.tenant_ID
and t.house_id = 1
or t.tenant_ID = p.tenant_ID
and t.house_id = 1

If I replace the OR with an AND I get no rows returned.

Regards

Quish

This is one of the problem in using WHERE to enforce table relationship. The reason could be your OR. if t.tenant_ID = p.tenant_ID is true, the ANDs fails. The OR will be followed. Depending on the relationship of these tables, you might want to use parenthesis to group your conditions. Or use JOIN to enforce the relationship.

-- CK

[Quish]

Hey there

I know its been a while but I have been up to different things.

I have been trying what you said about using the inner joins and have come up with this but no rows are being returned

 

SELECT     TENANT.*, STUDENT.*, PROFESSIONAL.*
FROM         TENANT INNER JOIN
                      STUDENT ON TENANT.TENANT_ID = STUDENT.TENANT_ID INNER JOIN
                      PROFESSIONAL ON TENANT.TENANT_ID = PROFESSIONAL.TENANT_ID
WHERE     (TENANT.HOUSE_ID = 1)

and i am not fully clear on what you mean by using "parenthesi s to group your conditions"

many thanks

Quish

[ck9663]

Hey there

I know its been a while but I have been up to different things.

I have been trying what you said about using the inner joins and have come up with this but no rows are being returned

 

SELECT     TENANT.*, STUDENT.*, PROFESSIONAL.*
FROM         TENANT INNER JOIN
                      STUDENT ON TENANT.TENANT_ID = STUDENT.TENANT_ID INNER JOIN
                      PROFESSIONAL ON TENANT.TENANT_ID = PROFESSIONAL.TENANT_ID
WHERE     (TENANT.HOUSE_ID = 1)

and i am not fully clear on what you mean by using "parenthesi s to group your conditions"

many thanks

Quish

A grouping would typically look like this:

      select distinct *
      from tenant t, student s, professional p, house h
      where (t.tenant_ID = s.tenant_ID and t.house_id = 1)
      or (t.tenant_ID = p.tenant_ID and t.house_id = 1)

Could you also try and separate your query just for testing? Do this first:

SELECT     TENANT.*, STUDENT.*
FROM         TENANT 
INNER JOIN STUDENT ON TENANT.TENANT_ID = STUDENT.TENANT_ID WHERE     (TENANT.HOUSE_ID = 1)

then:

SELECT     TENANT.*,  PROFESSIONAL.*
FROM        TENANT 
INNER JOIN PROFESSIONAL ON TENANT.TENANT_ID = PROFESSIONAL.TENANT_ID
WHERE     (TENANT.HOUSE_ID = 1)

Also, what's the data type of these TENANT_ID's?

-- CK

[Quish]

tenant_id is a INT datatype

[ck9663]

Have you tried running the query separately for testing purposes?


-- CK