Problem with select criteria with conditions

[rehanmomin]

BACKGROUND
I was trying the figure out a query for my sisters optometrist office. I have two tables, one that contains customers demographics and another table that contains patient's examination.

The two tables are called Customers and Examination.
Customers contains 2494 records and Examination contains 3496 records.

Not all customes have examinations because some customers may come in to purchase glasses. The extra records in Examination are for customers that are repeat customers who come in for an exam every year.

Table: Customers
Column Name: Cust_ID, Cust_First, Cust_Last

Table: Examination
Column Name: Exam_ID, Exam_CustID, Exam_LastExam

NOW THE QUESTION REGARDING SQL
I want to create a view and select all 2494 records from Customers and check if the customer ID exists in Examination.
If it does exists, I want to choose the most recent record from Examination corresponding to that customer.
If it does not exists, I want the value to be NULL.

Current my code is:

SELECT     TOP (100) PERCENT business.person.pers_key, business.person.pers_firstname, business.person.pers_lastname, MAX(exam.examination.exam_date) AS Expr1
FROM         business.person INNER JOIN exam.examination ON business.person.pers_key = exam.examination.exam_patientkey
GROUP BY business.person.pers_key, business.person.pers_firstname, business.person.pers_lastname, business.person.pers_key
ORDER BY business.person.pers_key
 

When I run the query, I get 2098 records instead of 2494 records. It seems that if the customer does not have an examination, it does not return the record.

Any suggestions?

Thanks in advance

[ck9663]

BACKGROUND
I was trying the figure out a query for my sisters optometrist office. I have two tables, one that contains customers demographics and another table that contains patient's examination.

The two tables are called Customers and Examination.
Customers contains 2494 records and Examination contains 3496 records.

Not all customes have examinations because some customers may come in to purchase glasses. The extra records in Examination are for customers that are repeat customers who come in for an exam every year.

Table: Customers
Column Name: Cust_ID, Cust_First, Cust_Last

Table: Examination
Column Name: Exam_ID, Exam_CustID, Exam_LastExam

NOW THE QUESTION REGARDING SQL
I want to create a view and select all 2494 records from Customers and check if the customer ID exists in Examination.
If it does exists, I want to choose the most recent record from Examination corresponding to that customer.
If it does not exists, I want the value to be NULL.

Current my code is:

SELECT     TOP (100) PERCENT business.person.pers_key, business.person.pers_firstname, business.person.pers_lastname, MAX(exam.examination.exam_date) AS Expr1
FROM         business.person INNER JOIN exam.examination ON business.person.pers_key = exam.examination.exam_patientkey
GROUP BY business.person.pers_key, business.person.pers_firstname, business.person.pers_lastname, business.person.pers_key
ORDER BY business.person.pers_key
 

When I run the query, I get 2098 records instead of 2494 records. It seems that if the customer does not have an examination, it does not return the record.

Any suggestions?

Thanks in advance

"INNER JOIN" returns record that are existing on the two tables being "joined". you might want to try "LEFT JOIN"