Follow

Keep Up to Date with the Most Important News

By pressing the Subscribe button, you confirm that you have read and are agreeing to our Privacy Policy and Terms of Use
Contact

MSSQL: Get all columns from three tables only if field exists in all three tables

I have three tables, and I want to get all columns from the three tables matching with the one column value[Name, Hostname, and system name]. But it seems to be going in a loop. In the sense, its bringing back all the values and repeats a particular value several times. Any advice on what I am doing wrong? I searched for similar issues but couldnt find any

Some of the matching values can be repeated. Currently I am using the following code

SELECT a.*, b.*, c.*
FROM dbo.ISD_machines a
LEFT JOIN dbo.ISD_systems b
ON a.HOSTNAME = b.SYSTEM_NAME
LEFT JOIN dbo.ISD_laptops c
ON a.HOSTNAME = c.[NAME]

Table 1:

id Hostname    date       location
1  LAP2000  13-06-2022    Mumbai
2  LAP2001  13-06-2022    Chennai
3  LAP2003  13-06-2022    Delhi
4  LAP2001  14-06-2022    HYDERABAD
5  LAP2004  15-06-2022    London
6  LAP2005  11-06-2022    Mumbai
7  LAP2001  12-06-2022    Delhi

Table 2:

id name       date       location areacode   user
1  LAP2002  13-06-2022   London    X5FE BST   JOHN
2  LAP2003  13-06-2022   Dublin    R32 AEW5   David
3  LAP2006  12-06-2022   Dubai     1016AE     Peter
4  LAP2007  11-06-2022   Doha      QA876      Sarah
5  LAP2004  15-06-2022   Delhi     632006     Louis

Table 3: 

id systemname county       telephone    mobile
1  LAP2000    Bungrada     987-456-123  0447891231
2  LAP2002    Courtyard    369-852-741  0445896321
3  LAP2003    Dublin       654-987-123  0449516321
4  LAP2004    Chelsea      258-963-741  0445863265
5  LAP2008    Anna Nagar   698-785-321  0446845214
6  LAP2006    Junaith      159-623-487  0446259384

MEDevel.com: Open-source for Healthcare and Education

Collecting and validating open-source software for healthcare, education, enterprise, development, medical imaging, medical records, and digital pathology.

Visit Medevel

>Solution :

If you Want all matched values then please use inner join

it will return only matched rows

SELECT *
FROM dbo.ISD_machines a
Inner JOIN dbo.ISD_systems b
ON a.HOSTNAME = b.SYSTEM_NAME
Inner JOIN dbo.ISD_laptops c
ON a.HOSTNAME = c.[NAME]
Add a comment

Leave a Reply

Keep Up to Date with the Most Important News

By pressing the Subscribe button, you confirm that you have read and are agreeing to our Privacy Policy and Terms of Use

Discover more from Dev solutions

Subscribe now to keep reading and get access to the full archive.

Continue reading