CODEWITHSUNDEEP
sqlsql-server
CompilerSaysNo
CompilerSaysNo

Reputation: 415

SQL Query Count, From, Not Equal to

I am looking to retrieve from a database in SQL Server with the following query: 

SELECT COUNT(datawarehouse.TBL_Referral.LeadId) 
FROM datawarehouse.TBL_Referral 
WHERE datawarehouse.TBL_Referral.PFAForVictimsLocation<>mapping.TBL_PoliceMapping.PoliceForce;

I have a table of Referrals with 'LeadID' as primary key. This database features 'Referrals' (essentially reported crimes) that have been logged. There is a Police Force Area for Victims Location 'PFAForVictimsLocation' column in the Referral Table, where the police force area is located where the victim has reported the crime and then another 'PoliceMapping' table with a 'PoliceForce' column, which tells me where the PoliceForce is located. I would like to extract a count of referrals where the PFAForVictimsLocation is not equal to PoliceForce -in other words I want to know a count of where the victim has logged a crime in a different area to where they live. Sorry if I have described that in a convoluted way. I have written the above query but it is not working - syntax issue I think? Thank you in advance.

Upvotes: 0

Views: 1341

Answers (1)

Joeri
Joeri

Reputation: 225

You're trying to compare two different tables TBL_Referral and TBL_PoliceMapping, but you have not joined them. You should connect these tables if you want to do a compare (where) on them.

SELECT 
    COUNT(datawarehouse.TBL_Referral.LeadId) 
FROM 
    datawarehouse.TBL_Referral 
    inner join 
        mapping.TBL_PoliceMapping
        on 
            datawarehouse.TBL_Referral.SomeId = mapping.TBL_PoliceMapping.SomeId
WHERE 
    datawarehouse.TBL_Referral.PFAForVictimsLocation <> mapping.TBL_PoliceMapping.PoliceForce;

Upvotes: 1

Related Questions