I am fairly new to SQL, so please bear that in mind with any responce.
I have a database, that someone else had created, but I want to extract some data from it in a set way.
there is a name table with a urn
there is an address table with a urn
there is a document table with a urn
and there is a link table with a urn
I supose it is in a way to keep the database tidy, but if you link a name to an address it creates a link, if you link the same name with a document it creates another link, and if you link a name with another name, it again creates a link.
The only common eliment to the above in the URN for the name.
Here is what I want to do.
I want and can create a select statement which will give me all the URN's for the names, which are mentioned in the link table, which are linked to a certain document (by its URN), and can join the name details onto this.
However I want now to be able to add the address details for these, but that where I struggle.
EG.
Names table
N1. dave smith
N2. john jones
Address table
A1. 1 here
A2. 3 there
A3. 67 elsewhere
document table
D1. this
D2. that
D3. the other
joins
1 joins D1 to N1
2 joins D1 to N2
3 joins D2 to N1
4 joins N1 to A1
5 joins N2 to A1
etc
I want to do is run a select statment that gives me all the names linked to D1 from the link table join the name table and retreive the names details.
then what I want to do is also link the addresses details to the names, again the link is from the link table ???
I tried with:-
select link.urn
link.link_to
link.link_from
names.urn
names.surname
names.forename
from link, names
on link.link_to=names.urn
where link.link_from=D1
but how can I base the same sort of select statement for the address part on the results from this select???
because what I need to do is something like:-
select link.urn
link.link_to
link.link_from
address.urn
address.housenumber
address.postcode
from link, address
on link.link_to=address.urn
where link.link_from= (the name.urn from previous select)
Any help please!!
I have a database, that someone else had created, but I want to extract some data from it in a set way.
there is a name table with a urn
there is an address table with a urn
there is a document table with a urn
and there is a link table with a urn
I supose it is in a way to keep the database tidy, but if you link a name to an address it creates a link, if you link the same name with a document it creates another link, and if you link a name with another name, it again creates a link.
The only common eliment to the above in the URN for the name.
Here is what I want to do.
I want and can create a select statement which will give me all the URN's for the names, which are mentioned in the link table, which are linked to a certain document (by its URN), and can join the name details onto this.
However I want now to be able to add the address details for these, but that where I struggle.
EG.
Names table
N1. dave smith
N2. john jones
Address table
A1. 1 here
A2. 3 there
A3. 67 elsewhere
document table
D1. this
D2. that
D3. the other
joins
1 joins D1 to N1
2 joins D1 to N2
3 joins D2 to N1
4 joins N1 to A1
5 joins N2 to A1
etc
I want to do is run a select statment that gives me all the names linked to D1 from the link table join the name table and retreive the names details.
then what I want to do is also link the addresses details to the names, again the link is from the link table ???
I tried with:-
select link.urn
link.link_to
link.link_from
names.urn
names.surname
names.forename
from link, names
on link.link_to=names.urn
where link.link_from=D1
but how can I base the same sort of select statement for the address part on the results from this select???
because what I need to do is something like:-
select link.urn
link.link_to
link.link_from
address.urn
address.housenumber
address.postcode
from link, address
on link.link_to=address.urn
where link.link_from= (the name.urn from previous select)
Any help please!!
