SQL Query to get count of matching data from two table

**Kiran2012** · 02-28-2013, 01:58 AM

Hi all,

I have 3 tables :

Travellers_Report-

Name Country Place_of_visit Return_date
A United States Los Angeles 2/27/2013
B United Arab Emirates DUBAI 2/21/2013
C Puerto Rico San Juan 2/28/2013
D United States Los Angeles 2/27/2013
E United States Boston 2/27/2013
F Malaysia Kulalampur 3/1/2013
G United Arab Emirates DUBAI 2/15/2013
H Malaysia Kulalampur 3/15/2013
I United States Los Angeles 2/27/2013

Company_presence-

Country City
United States Los Angeles
United Arab Emirates DUBAI
United States Boston

I need to compare the 'Travellers_Report' table's 'Place_of_visit' field to 'Company_presence' table's 'City field' and get the count.
Expected output:

Country Place_of_visit Count
United States Los Angeles 3
United Arab Emirates DUBAI 2
United States Boston 1

as of now i have 2 queries but dont know how to connect this to get the expected result.

   sqlstr = "SELECT * " & _
      "FROM Travellers_report " & _
      "WHERE Travellers_report.Return_Date >= #" & DOI & "# AND Travellers_report.Return_Date <=#" & tempd & "#;"

and

select Travellers_report.Place_of_Visit, count(1) from Travellers_report 
  group by Place_of_Visit

Please suggest some solutions...

**Kiran2012** · 02-28-2013, 04:06 AM

Played around with this and got:

SELECT sub.company_Presence.Facility, Count(company_Presence.Facility) AS CityCount FROM (SELECT Travellers_report.*, company_Presence.Location, company_Presence.Facility from Travellers_report,company_Presence WHERE Travellers_report.Return_Date >= #2/20/2013# AND Travellers_report.Return_Date <=#2/27/2013# and Travellers_report.Place_of_Visit = company_Presence.Facility) AS sub GROUP BY sub.company_Presence.Facility

Now only Two columns is there 'Facility' & 'CityCount'. How to add one more column in beginning (Country) by altering this query?

**alansidman** · 02-28-2013, 08:17 AM

SELECT Travellers_Report.Country, Travellers_Report.Visit, Count(Travellers_Report.Visit) AS CountOfVisit
FROM Travellers_Report INNER JOIN Company_Presence ON (Travellers_Report.Visit = Company_Presence.City) AND (Travellers_Report.Country = Company_Presence.Country)
GROUP BY Travellers_Report.Country, Travellers_Report.Visit;

Try the above

**Kiran2012** · 03-18-2013, 01:37 AM

Hi,

I posted same question by mistake again. How to delete that post?

Also above query is working fine. One more thing. This query will compare the two tables and get the result where place of visit in 1st table and City in 2nd table matches i.e. Travellers_Report.Visit = Company_Presence.City
How to get the count of not matching records? tried

Travellers_Report.Visit <> Company_Presence.City

in the same query but not getting the correct result. Pls suggest some solutions.

**Kiran2012** · 03-18-2013, 02:27 AM

Finally got it....

SELECT sub.Travellers_report.[Place_of_Visit], Count(Travellers_report.[Place_of_Visit]) AS CityCount from (SELECT Travellers_report.Country, Travellers_report.Place_of_Visit
FROM Travellers_report LEFT JOIN Company_Presence ON Travellers_report.[Place_of_Visit] = Company_Presence.[Facility]
WHERE (((Company_Presence.Facility) Is Null)) and Travellers_report.Return_Date >= #2/20/2013# AND Travellers_report.Return_Date <=#2/27/2013#) AS sub
GROUP BY sub.Travellers_report.[Place_of_Visit];