Get all columns from three tables only if field exists in all three tables-CodePudding

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?

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

CodePudding user response：

If you want all matched values then use an 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]