relations question

chucklarge

Hello,

I have table called Projects, each project has a primary address and can have any number of alternate addresses. So, I have two address tables, Primary and Alternate . I then have a relation table that keeps track of projects and addresses. This relation has the id of the project, the id of the address and a enum type that says 'Primary' or 'Alternate'

I would like to be able to use one query to search the project-address relation table for a project id and return all the addresses. Is there a way to do this or should i setup my tables differently?

Thanks,
Chuck

here is an example of the tables
-Primary_Address-
id-name-street ...
1-comp 1 - 332 street
2-comp 2 - 55 street
3-comp 3 - 344 street

-Alternate_ Address-
id-name-street ...
1-comp 54 - 7765 street
2-comp 23 - 555 street
3-comp 66 - 884 street

-Project_Address_Relation-
id-Proj_id-Add_id-type
1- 34 - 2 - Primary
2- 34 - 3 - Alternate
3- 34 - 1 - Alternate
4- 44 - 3 - Primary
5- 44 - 2 - Alternate

Roger_Ramjet

OK, the Primary Address is an attribute of the project and should be stored in the Project record: every project has a primary address.
Secondary addresses are a different attribute from the primary address: there can be none, one, or many secondary addresses. So you use a sub-table for secondary addresses.

Query for projects and their secondary addresses uses a left join so that projects without any secondary address are not excluded. You do not need another table to relate secondary addresses to projects, you store the project id with the secondary address itself.

Understand, just because things have similar names, or even look the same, like primary and secondary address, does not mean that they are the same thing. In this case, niether is an entity, and both are different attributes of the project entity.

jansky

you can use left join for this one

example :

select primary_address.* from primary_address left join Project_Address_Relation on (primary_address.id=Project_Address_Relation.proj_id)