I have three different tables within a database and need to return data based on a query from each table, is this possible?
The tables are named as follows:
table1 = housingasbcase
table2 = cccontact
table3 = CCADDRESS
table4 = gazuprncoords
The data I need to retreieve is as follows
housingasbcase & cccontact
Both of these 2 tables have a column named ContactNumber. I need to retrieve all data from cccontact column "UPRN"
where ContactNumber is present in both tables.
CCADDRESS
Using UPRN results returned from above query I need to search CCADDRESS and return all data from a column called "REALUPRN", where column "UPRN" matches (UPRN column is present in CCADDRESS and CCContact)
gazuprncoords
Finally with the results from above continaing "REALUPRN" I need to search a table gazuprncoords and return fields "XCOORD" & "YCOORD" where there is a match on column REALUPRN
SELECT C.UPRN, A.REALUPRN, G.XCOORD, G.YCOORD
FROM housingasbcase AS H
JOIN cccontact AS C
ON C.ContactNumber = H.ContactNumber
JOIN CCADDRESS AS A
ON A.UPRN = C.UPRN
JOIN gazuprncoords AS G
ON G.REALUPRN = C.UPRN -- I've guessed what you need to join TO here
This is great progress! Thank you for you help. I have amended the last line slightly as I realised I got the name of the column in GAZUPRNCOORDS wrong, it is actually jsut called UPRN:
SELECT C.UPRN, CAST(A.RealUPRN AS VARCHAR), G.X_COORDINATE, G.Y_COORDINATE
FROM housingasbcase AS H
JOIN cccontact AS C
ON C.ContactNo = H.SourceContactNo
JOIN CCADDRESS AS A
ON A.RealUPRN = C.UPRN
JOIN gazuprncoords AS G
ON G.UPRN = C.UPRN
I am currently getting an error "The conversion of the varchar value '100005XXXXXX' overflowed an int column. I recoginse this to be a realuprn from the CCADDRESS table. To resolve this do I need to Cast it as a varchar in the query somehow?
EG CAST (RealUPRN AS VarChar)
EDIT: the XXXX in the value is just where I have blanked out numbers not actually part of ref!
needs to have the same datatype on both sides of the JOIN - sounds like RealUPRN is numeric and UPRN is a VARCHAR?
SQL will be using an imlicit conversion and probably trying to convert the VARCHAR column into INT for the comaparison - and then finding that one of the VARCHARs is not valid for an INT
Best to use an EXPLICIT cast/conversion in ALL cases when you need to do that - if noting else it indicates to other people, looking at your code later, that you deliberately & knowingly JOINed a VARCHAR to an INT !!
Second point, never use VARCHAR without a size parameter. There are defaults, sooner or later they will be too small, and you'll spend hours trying to work out why ... something deep within your code may, silently, truncate a converted value because the default size of VACHAR is too small ...
Assuming I have got it right, and the right way round, then the explicit CAST needs to be in the JOIN (not the select, you can have one in the SELECT as well if you want to convert Numeric to Varchar for the output - although that would probably be unusual.
JOIN CCADDRESS AS A
ON CAST(A.RealUPRN AS VARCHAR(20)) = C.UPRN
By the by, it would be much better to change both these columns to be the same datatype. JOINing on different datatypes is usually very inefficient - indexes etc. may not be used, which makes the query run a lot slower.
Thanks for all the help and info Kristen, all sorted 
really appreciate it.