Joining Tables in SQL

Joining Tables in SQL

  



Hi Guys,


I've the below query that is working fine but now I wanted to also select the street name which is in Street entity that I could link to Property entity via StreetId field (which is defined as Street Identifier), so I added the  :

{Street}.[Name] as St,  in the selection bit and JOIN {Property} ON {Property}.[StreetId]={Street}.[Id] to join the tables but its giving me and error "Error executing query".

 so what am I doing wrong ?


SELECT {InspectionCategory}.[Label] AS Category, 
    IIF(InnerInspection.InnerInspectionId IS NULL, {Form}.[Name], InnerInspection.InnerInspectionForm) AS Form, 
    {HealthPremise}.[Code], {Property}.[Address], 
    {HealthPremise}.[Name] AS LocationName,
    (
        SELECT {Contact}.[Name] + ' ' + {Contact}.[OtherNames] + ', '
        FROM {Contact} 
            JOIN {HealthPremiseContact} ON {HealthPremiseContact}.[ContactId] = {Contact}.[Id]
        WHERE {HealthPremiseContact}.[HealthPremiseId] = {HealthPremise}.[Id]
            AND {HealthPremiseContact}.[ContactTypeId] = @ProprietorContactTypeId 
        FOR XML PATH ('')
    ) AS ContactName, {PremiseClass}.[Code] + ' - ' + {PremiseClass}.[Description] AS PremiseClass, 
    {Audit}.[ConductedOn] AS LastInspectionDate, 
    IIF(InnerInspection.InnerInspectionId IS NULL, 
        IIF(CONVERT(VARCHAR, {Audit}.[FollowUpInspectionDate], 103) <> '01/01/1900', {Audit}.[FollowUpInspectionDate], 
            {Audit}.[NextInspectionDate]),
        InnerInspection.InnerInspectionDueDate) AS NextInspectionDueDate, 
    IIF(InnerInspection.InnerInspectionId IS NULL, {InspectionTypes}.[Label], InnerInspection.InnerInspectionType) AS LastInspectionType, 
    IIF(InnerInspection.InnerInspectionId IS NULL, {Inspection}.[Inspector], InnerInspection.InnerInspectionInspectorId) AS LastInspectorId,   
    IIF({Audit}.[IsCompliant] = 1, 'True', 'False') AS IsCompliant, InnerInspection.InnerInspectionId AS InspectionId, 
    InnerInspection.InnerInspectionBookingDate AS BookingDateTime,{Property}.[HouseNumber] AS HouseNo,{Street}.[Name] as St
FROM {Inspection}
    JOIN {Audit} ON {Audit}.[InspectionId] = {Inspection}.[Id] AND {Audit}.[IsLastPropertyAudit] = 1
    JOIN {InspectionCategory} ON {InspectionCategory}.[Id] = {Inspection}.[Category]
    JOIN {InspectionForm} ON {InspectionForm}.[InspectionId] = {Inspection}.[Id]
    JOIN {Form} ON {Form}.[Id] = {InspectionForm}.[FormId]
    JOIN {HealthPremise} ON {HealthPremise}.[Id] = {Inspection}.[HealthPremiseId]
    JOIN {InspectionTypes} ON {InspectionTypes}.[Id] = {Inspection}.[InspectionTypesId]
    JOIN {HealthPremiseClass} ON {HealthPremiseClass}.[Id] = {Inspection}.[HealthPremiseClassId]
    JOIN {PremiseClass} ON {PremiseClass}.[Id] = {HealthPremiseClass}.[PremiseClassId]
   LEFT JOIN {Property} ON {Property}.[Id] = {HealthPremise}.[PropertyId]
   JOIN {Property} ON {Property}.[StreetId]={Street}.[Id]

Hello John,

You're trying to join Property twice, and the Street entity does not appear in the FROM nor in a JOIN (and this is mandatory).
So, what you want is (I think):


JOIN {Street} ON {Property}.[StreetId]={Street}.[Id]


Cheers.

EDIT: Just remeber that if StreetID in Property is not mandatory, and there are empty fields in certain rows, the next Inner Join will remove the entire line from the results.

Thank Eduardo its working now,


But I think I'm missing the principle of the join, I thought me joining property entry to street the way i did should be enough !? 

so that the join statement with the From Inspection means that I'm joining Inspection to Street  where Street.Id=Property.StreetId !?


thanks


A quick glance on the query also tells me you can do the same with an aggregate btw..


J. wrote:

A quick glance on the query also tells me you can do the same with an aggregate btw..


I'm not sure, J. It seems there is a XML path in the query (concatanation of a sub query result?)


john smith wrote:

Thank Eduardo its working now,


But I think I'm missing the principle of the join, I thought me joining property entry to street the way i did should be enough !? 

so that the join statement with the From Inspection means that I'm joining Inspection to Street  where Street.Id=Property.StreetId !?


thanks


All tables that you wants to use must appear in one of two places: in the FROM, or in a JOIN. The Street entity was not appearning in neither, while the Property was appearing twice (something that is possible if you use alias, but was not what you wanted).


Solution

Or, expalining better.

When you use something like this:

JOIN table ON table.Id = OtherTable.Id

You are telling that you want to join table with the OtherTable. But the rule says that OtherTable MUST appear before this clause, or in another JOIN, or in the FROM clause. 

In your case, the Street didn't appear in any clause before here, so, as the Property was already present, you wanted to join Street with Property, than the correct way was the one I showed.

Hope this helps.

Solution

Eduardo Jauch wrote:

J. wrote:

A quick glance on the query also tells me you can do the same with an aggregate btw..


I'm not sure, J. It seems there is a XML path in the query (concatanation of a sub query result?)


true however, he names it "ContactName", and it simply looks like a concat for a fully contact name.

J. wrote:

Eduardo Jauch wrote:

J. wrote:

A quick glance on the query also tells me you can do the same with an aggregate btw..


I'm not sure, J. It seems there is a XML path in the query (concatanation of a sub query result?)


true however, he names it "ContactName", and it simply looks like a concat for a fully contact name.

If the sub query always returns a single contact, yes. By the query itself is not possible to say so. If so, indeed than it is very easy to do this query with an Aggregate. 



Eduardo Jauch wrote:

Or, expalining better.

When you use something like this:

JOIN table ON table.Id = OtherTable.Id

You are telling that you want to join table with the OtherTable. But the rule says that OtherTable MUST appear before this clause, or in another JOIN, or in the FROM clause. 

In your case, the Street didn't appear in any clause before here, so, as the Property was already present, you wanted to join Street with Property, than the correct way was the one I showed.

Hope this helps.

Thanks heaps Eduardo