SQL Query Help

[lovecode]

I have following two tables:

Table1:
-------
ID
Name


Table2:
-------
OwnerID
Address1
Address2
Address3


Table1.ID = "100" and the corresponding address record in Table2 is Table2.OwnerID = "C100". So if Table1.ID = "200" then Table2.OwnerID = "C200" and so on.

So the OwnerID field in Table2 doesn't have the identical value as the ID field in Table1 but it contains a prefix, "C" in this case.

Question: I would like to get the address record from Table2 for one or more IDs in Table1. How can I do that? Basically I want a result table that has Table1.ID, Table1.Name, Table2.Address1, Table2.Address2, and Table2.Address3.

Please let me know if you need more information to answer this question.

Will really appreciate some help.

 

[JamesLean]

SELECT *
FROM table1 t1 JOIN table2 t2 ON 'C' + t1.id = t2.id

If t1.id is an int then:

SELECT *
FROM table1 t1 JOIN table2 t2 ON 'C' + CAST(t1.id AS varchar) = t2.id

--James

 

[lovecode]

Thanks for your suggestion.

When trying to execute it via ADO it is throwing an error "syntax error in FROM clause". Tried it in MS Access query editor and and the same error.

 

[PHV]

SELECT *
FROM table1 t1 INNER JOIN table2 t2 ON 'C' & t1.id = t2.id

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[lovecode]

Thanks PHV, that worked. Appreciate it.

Question 2:

Now I want to modify that query so it returns all the records from Table1 and returns the associated records from Table2 if there is no associated record in Table2, it will still return the record from Table1.

With the current query it only returns records from Table1 that have an associated record in Table2.

 

[PHV]

Replace INNER JOIN by LEFT JOIN.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[lovecode]

Thanks PHV again. That did it.