SQL Combining two different tables

(P.S. I am still learning SQL and you can consider me a newbie)

I have 2 sample tables as follows:

Table 1

|Profile_ID|  |Img_Path|

Table 2

|Profile_ID|  |UName|   |Default_Title|

My scenario is, from the 2nd table, i need to fetch all the records that contain a certain word, for which i have the following query :

Select Profile_Id,UName from 
Table2 Where 
Contains(Default_Title, 'Test')

ORDER BY Profile_Id
OFFSET 5 ROWS
FETCH NEXT 20 ROWS ONLY

(Note that i am setting the OFFSET due to requirements.)

Now, the scenario is, as soon as i retrieve 1 record from the 2nd table, i need to fetch the record from the 1st table based on the Profile_Id.

So, i need to return the following 2 results in one single statement :

|Profile_Id|   |Img_Path|

|Profile_Id|   |UName|

And i need to return the results in side-by-side columns, like :

|Profile_Id|   |Img_Path|   |UName|

(Note i had to merge 2 Profile_Id columns into one as they both contain same data)

I am still learning SQL and i am learning about Union, Join etc. but i am a bit confused as to which way to go.

Answer

You can use join:

select t1.*, t2.UName
from table1 t1 join
     (select Profile_Id, UName
      from Table2
      where Contains(Default_Title, 'Test')
      order by Profile_Id
      offset 5 rows fetch next 20 rows only
     ) t2
     on t2.profile_id = t1.profile_id

Advertisement

Answer