SQL Server - Join across several tables

Question

Below are the relevant tables info:

tgenie

Guid | genietypeguid | ...

and :

tgenieType

guid | genietype | ...

I made this query to select all possible entries:

select 
    tsearch.description,
    tcompany.CompanyName,
    tsearch.CompanyGUID,
    tgenie.GenieNotes,
    tGenieType.GenieType
from 
    tcompany, tsearch, tGenieType
left outer join 
    tGenie on tgenie.GenieTypeGUID = tGenieType.GUID

As the result I have this output:

description | CompanyName | CompanyGUID | GenieNotes | GenieType

VP Buiness AP Dev | Test |  5920B842-8216-45AA-A2AF-EA69740959FC |  Test2 | Add'l Candidate Info
VP Buiness AP Dev | Test |  5920B842-8216-45AA-A2AF-EA69740959FC |  Test3 | Add'l Candidate Info
VP Buiness AP Dev | Test |  5920B842-8216-45AA-A2AF-EA69740959FC |  Test5 | Add'l Candidate Info

. . .

However, it does not work as supposed.

GenieType is set as "Add'l Candidate Info" for all the entries. I had ran the query for a long time and it seem to be the same for all the rows ... which does not seem right.

Any body has an idea why my join does not work?

Thanks in advance!

Update 1

I ended up with this query

It took 9 mins and has 1478028 rows...

select tsearch.Description,

tcompany.CompanyName,
tcompany.GUID as CompanyGuid,
tcompanylocation.LocationName,
tsearchtype.SearchType,
tsearchresult.searchresult,
tpeople.GUID as PersonPlacedGuid,
tpeople.LastName As PersonPlacedLName,
tpeople.Firstname As PersonPlacedFName,
tpeople.address1,
 tpeople.address2,
 tpeople.city,
 tpeople.zipcode,
 tpeople.city2,
 tpeople.zipcode2,
 tpeople.emailaddress,
 tpeople.website,
 tpeople.homephone,
 tpeople.OtherPhoneDescription1,
 tpeople.otherphone1,
 tpeople.otherphonedescription3,
 tpeople.otherphone3,
 tpeople.resumefile,
 tpeople.resumeocr,
 tpeople.timeentered,
 tPeopleEducation.education,
tPeopleEducation.GradYear,
tsearch.SearchNotes,
( select tpeople.Firstname  from tpeople where tpeople.guid=tSearch.RepresentativeGUID) as repfirstname, 
( select tpeople.Lastname from tpeople where tpeople.guid=tSearch.RepresentativeGUID) as replastname,
tsearch.RepresentativeGUID as RepGuid,
tposition.Position as backgroundposition,
tdepartment.Department as backgrounddepartment,
( select tpeople.Lastname from tpeople where tpeople.guid=tSearch.ReferredByGUID) as referredbylastname,
( select tpeople.FirstName from tpeople where tpeople.guid=tSearch.ReferredByGUID) as referredbylastname,
tsearch.ReferredByGUID as PersonwhorefferedGuid,
( select tcompany.CompanyName from tCompany where tCompany.guid=tSearch.PlacedFromGUID) as placedfrom,
tinstantstatustype.InstantStatus,
tWorkbench.WorkbenchName,
( select tpeople.Lastname from tpeople where tpeople.guid=tInstantStatus.PeopleGUID) as Candlastname,
( select tpeople.FirstName from tpeople where tpeople.guid=tInstantStatus.PeopleGUID) as candFirstname,
tInstantStatus.ForClientNotes,
tinstantstatus.InstantStatusNotes as InstanttatusNotesSummary,
tGenie.GenieNotes,
tGenieType.GenieType,
tgenielabel.GenieLabel

from tcompany


inner join tsearch on tCompany.GUID = tSearch.CompanyGUID
left join tcompanylocation on tcompanylocation.guid= tcompany.LocationGUID
left join tSearchType on tsearchtype.GUID = tSearch.SearchTypeGUID
left join tSearchResult on tSearchResult.GUID = tsearch.SearchResultGUID
left join tPeople on tPeople.GUID = tsearch.PlacedGUID
left join tPosition on tPosition.GUID = tsearch.PositionGUID
left join tDepartment on tdepartment.GUID = tsearch.DepartmentGUID
left join tInstantStatus on tInstantStatus.SearchGUID = tSearch.guid
left join tInstantStatusType on tInstantStatusType.GUID = tInstantStatus.InstantStatusGUID 
left join tWorkbench on tWorkbench.SearchGUID=tsearch.GUID
left join tSearchCluendex on tSearchCluendex.CPSGUID=tsearch.GUID 
left join tpeopleEducation on tpeople.GUID = tpeopleEducation.PeopleGUID
left join tBusDev on tsearch.GUID = tbusdev.SearchGUID
left join tGenie on tbusdev.GUID = tgenie.BusDevGUID
left join tGenieLink on tGenie.GUID = tgenielink.GenieGUID
left join tGenieLabel on tgenielink.GenieLabelGUID = tGenieLabel.GUID
left join tGenieType on tgenie.GenieTypeGUID=tGenieType.GUID

Weird is that in the system 57 GenieType are defined and only 4 were found. Also with GenieLabel, in the system 16 are defined and only 6 were found.

I am beginner in SQL/TSQL and this query seems quite big .... does it look OK? Or it is hard to tell without more info about DB structure ...

HMMM.... there are different answers suggested, but this above seem to work.

Why should I use:

inner join
inner join
left outer

vs

inner join
left join
left join

vs

join 
join
left join

????

Update 2:

Thanks @Daniel for your help. I attached the diagram as you suggested.

What I want to get can be seen in my query from Update 1

Hope some good soul can help.

Answer 1

I suggest trying maybe this.

SELECT  tsearch.description
        ,tcompany.CompanyName
        ,tsearch.CompanyGUID
        ,tgenie.GenieNotes
        ,tGenieType.GenieType
FROM tcompany
    JOIN tsearch 
        ON tcompany.SomeID = tsearch.SomeID
    JOIN tGenieType 
        ON tcompany.SomexID = tGenieType.SomexID
    LEFT JOIN tGenie 
        ON tgenie.GenieTypeGUID = tGenieType.GUID

You accidentaly stumbled upon cross join. Also, connecting your tables will have to go over keys, so make sure you identify them correctly. A good visual reference on what's going on with joins is on codinghorror blog.

Answer 2

Your query is performing a CROSS JOIN between tcompany, tsearch and tGenieType, and then tries to join on a corresponding record in the tGenie table. This means your query will return a record for every combination of tcompany, tsearch and tGenieType possible, regardless of any relations between them, and will then try to link the results to tGenie records using your condition, potentially multiplying the results once more.

When you say that you want "all possible entries", what exactly do you want to achieve?

Typically, as well as from your queried columns, you'd want to perform a INNER JOIN or a LEFT JOIN rather than a CROSS JOIN. For example :

SELECT
  tSearch.Description,
  tCompany.CompanyName,
  tSearch.CompanyGUID,
  tGenie.GenieNotes,
  tGenieType.GenieType
FROM
  tCompany
  INNER JOIN tSearch ON tCompany.GUID = tSearch.CompanyGUID
  LEFT JOIN tGenie ON tSearch.GUID = tGenie.SearchGUID
  LEFT JOIN tGenieType ON tGenieType.GUID = tGenie.GenieTypeGUID

This would pull every tSearch from every tCompany, matching them according to their foreign keys, and then try to match with the corresponding tGenie records based on an hypothetical GUID column before doing the same with the tGenieType.

Answer 3

You are not joining the other tables in FROM (and this syntax is not longer supported in new versions of Sql Server)

Should be like this:

select tsearch.description
,tcompany.CompanyName
,tsearch.CompanyGUID
,tgenie.GenieNotes
,tGenieType.GenieType
from tcompany
inner join tsearch on tcompany.XXX = tsearch.XXX
inner join tGenieType on tcompany.YYY = tsearch.YYY
left outer join tGenie on tgenie.GenieTypeGUID=tGenieType.GUID