By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
455,379 Members | 1,357 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 455,379 IT Pros & Developers. It's quick & easy.

Selecting all from Super-Types and Sub-Types

P: 22
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
Mar 20 '08 #1
Share this Question
Share on Google+
7 Replies


ck9663
Expert 2.5K+
P: 2,878
Could you post what you have so far?

-- CK
Mar 20 '08 #2

P: 22
Hi There

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

Expand|Select|Wrap|Line Numbers
  1.  
  2. select distinct * 
  3. from tenant t, student s, professional p, house h
  4. where t.tenant_ID = s.tenant_ID
  5. and t.house_id = 1
  6. or t.tenant_ID = p.tenant_ID
  7. and t.house_id = 1
  8.  
  9.  
This is my second attempt but returns 2 identical records apart for tenant_id which changes

Expand|Select|Wrap|Line Numbers
  1.  
  2.  
  3. select distinct * 
  4. from tenant t, student s, professional p
  5. where t.tenant_ID = s.tenant_ID
  6. and t.house_id = 1
  7. or t.tenant_ID = p.tenant_ID
  8. and t.house_id = 1
  9.  
  10.  
If I replace the OR with an AND I get no rows returned.

Regards

Quish
Mar 20 '08 #3

ck9663
Expert 2.5K+
P: 2,878
Hi There

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

Expand|Select|Wrap|Line Numbers
  1.  
  2. select distinct * 
  3. from tenant t, student s, professional p, house h
  4. where t.tenant_ID = s.tenant_ID
  5. and t.house_id = 1
  6. or t.tenant_ID = p.tenant_ID
  7. and t.house_id = 1
  8.  
  9.  
This is my second attempt but returns 2 identical records apart for tenant_id which changes

Expand|Select|Wrap|Line Numbers
  1.  
  2.  
  3. select distinct * 
  4. from tenant t, student s, professional p
  5. where t.tenant_ID = s.tenant_ID
  6. and t.house_id = 1
  7. or t.tenant_ID = p.tenant_ID
  8. and t.house_id = 1
  9.  
  10.  
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
Mar 20 '08 #4

P: 22
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

Expand|Select|Wrap|Line Numbers
  1.  
  2.  
  3. SELECT     TENANT.*, STUDENT.*, PROFESSIONAL.*
  4. FROM         TENANT INNER JOIN
  5.                       STUDENT ON TENANT.TENANT_ID = STUDENT.TENANT_ID INNER JOIN
  6.                       PROFESSIONAL ON TENANT.TENANT_ID = PROFESSIONAL.TENANT_ID
  7. WHERE     (TENANT.HOUSE_ID = 1)
  8.  
and i am not fully clear on what you mean by using "parenthesis to group your conditions"

many thanks

Quish
Mar 27 '08 #5

ck9663
Expert 2.5K+
P: 2,878
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

Expand|Select|Wrap|Line Numbers
  1.  
  2.  
  3. SELECT     TENANT.*, STUDENT.*, PROFESSIONAL.*
  4. FROM         TENANT INNER JOIN
  5.                       STUDENT ON TENANT.TENANT_ID = STUDENT.TENANT_ID INNER JOIN
  6.                       PROFESSIONAL ON TENANT.TENANT_ID = PROFESSIONAL.TENANT_ID
  7. WHERE     (TENANT.HOUSE_ID = 1)
  8.  
and i am not fully clear on what you mean by using "parenthesis to group your conditions"

many thanks

Quish
A grouping would typically look like this:

Expand|Select|Wrap|Line Numbers
  1.       select distinct *
  2.       from tenant t, student s, professional p, house h
  3.       where (t.tenant_ID = s.tenant_ID and t.house_id = 1)
  4.       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:
Expand|Select|Wrap|Line Numbers
  1. SELECT     TENANT.*, STUDENT.*
  2. FROM         TENANT 
  3. INNER JOIN STUDENT ON TENANT.TENANT_ID = STUDENT.TENANT_ID WHERE     (TENANT.HOUSE_ID = 1)
  4.  
then:

Expand|Select|Wrap|Line Numbers
  1. SELECT     TENANT.*,  PROFESSIONAL.*
  2. FROM        TENANT 
  3. INNER JOIN PROFESSIONAL ON TENANT.TENANT_ID = PROFESSIONAL.TENANT_ID
  4. WHERE     (TENANT.HOUSE_ID = 1)

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

-- CK
Mar 27 '08 #6

P: 22
tenant_id is a INT datatype
Mar 30 '08 #7

ck9663
Expert 2.5K+
P: 2,878
Have you tried running the query separately for testing purposes?


-- CK
Mar 30 '08 #8

Post your reply

Sign in to post your reply or Sign up for a free account.