what is wrong with this query

[hussein2000]

i have this query i could not execute it cause there is an error occured
i use not in but it is slow and take more sampling time.

so can u please try this query for me

SELECT C.CheckIn_Rec_Id,
S.FirstName,
S.FatherName,
S.GrandFatherName,
S.FamilyName,
C.visaNumber,
C.roomNumber,
C.checkInDate,
C.checkInTime
from ((checkIn c INNER JOIN ServiceCustomer S
on (S.ServiceCustomer_Rec_ID = C.serviceCustomer))
LEFT OUTER JOIN (SELECT A.CheckIn_Rec_ID FROM CheckOut A
ON
(A.CheckIn_Rec_ID = C.CheckIn_Rec_ID)
order by 1 desc


thank u

[ITGURU]

Dear hussein2000,

Problem is in your query, you better use following query:

SELECT C.CheckIn_Rec_Id,
S.FirstName,
S.FatherName,
S.GrandFatherName,
S.FamilyName,
C.visaNumber,
C.roomNumber,
C.checkInDate,
C.checkInTime
from checkIn c INNER JOIN ServiceCustomer S
on C.serviceCustomer = S.ServiceCustomer_Rec_ID
WHERE EXISTS (SELECT A.CheckIn_Rec_ID FROM CheckOut A
WHERE A.CheckIn_Rec_ID = C.CheckIn_Rec_ID)
order by 1 desc

Hope this will solve u r problem

[hussein2000]

Dear ITGURU

THANK U FOR YOUR REPLAY
YES YOUR QUERY IS GOOD AND IT IS WORKING AS I WANT

ONLY I USE NOT EXISTS INSTADE EXISTS AND RETRIVE THE SAME NO OF RECORDS . AGAIN I THANK U .

HUSSEIN

[hussein2000]

hi ITGURU

in these same two queries i use inner join and in another where clause
but still working slow.

SELECT C.CheckIn_Rec_Id,
S.FirstNameArabic,
S.FatherNameArabic,
S.GrandFatherName,
S.FamilyName,
S.ServiceCustomer_Rec_Id,
C.roomNumber,
C.checkInDate,
C.checkInTime,
D.DataStatus
FROM CheckIn C, ServiceCustomer S, DataStatus D
WHERE (S.ServiceCustomer_Rec_ID = C.serviceCustomer
or S.relatedTo = CStr(C.serviceCustomer) )
AND C.Ready = D.Status_Rec_ID
ORDER BY 1 DESC;


++++++++

SELECT C.CheckIn_Rec_Id, S.FirstNameArabic,
S.FatherNameArabic,S.GrandFatherName,
S.FamilyName,S.ServiceCustomer_Rec_Id,
C.roomNumber, C.checkInDate,
C.checkInTime, D.DataStatus
FROM ((CheckIn C INNER JOIN ServiceCustomer S ON
(S.ServiceCustomer_Rec_ID=C.serviceCustomer or
S.relatedTo=CStr(C.serviceCustomer)))
INNER JOIN DataStatus D ON
(C.Ready=D.Status_Rec_ID)) ORDER BY 1 DESC;

so any other query can i use to get the result in less time.

thanks

[ITGURU]

Dear hussein2000,

From your query I have seen that u using MSACCESS as your database and me does not have any idea for how to check optimzation issue in MSACCESS but still i have one good solution for your problem before i can give you the solution i have found following problem in your Query:
1. Always write field in where/join (on) clause as per order defined for the table in From clause.

2. Avoid using function in where/join (on) clause if possible.


Now your modified query:
SELECT C.CheckIn_Rec_Id, S.FirstNameArabic,
S.FatherNameArabic,S.GrandFatherName,
S.FamilyName,S.ServiceCustomer_Rec_Id,
C.roomNumber, C.checkInDate,
C.checkInTime, D.DataStatus
FROM CheckIn C
INNER JOIN ServiceCustomer S
ON (C.serviceCustomer = S.ServiceCustomer_Rec_ID and
CStr(C.serviceCustomer) <> S.relatedTo
INNER JOIN DataStatus D
ON C.Ready=D.Status_Rec_ID
UNION
SELECT C.CheckIn_Rec_Id, S.FirstNameArabic,
S.FatherNameArabic,S.GrandFatherName,
S.FamilyName,S.ServiceCustomer_Rec_Id,
C.roomNumber, C.checkInDate,
C.checkInTime, D.DataStatus
FROM CheckIn C
INNER JOIN ServiceCustomer S
ON (C.serviceCustomer <> S.ServiceCustomer_Rec_ID and
CStr(C.serviceCustomer) = S.relatedTo
INNER JOIN DataStatus D
ON C.Ready=D.Status_Rec_ID
UNION
SELECT C.CheckIn_Rec_Id, S.FirstNameArabic,
S.FatherNameArabic,S.GrandFatherName,
S.FamilyName,S.ServiceCustomer_Rec_Id,
C.roomNumber, C.checkInDate,
C.checkInTime, D.DataStatus
FROM CheckIn C
INNER JOIN ServiceCustomer S
ON (C.serviceCustomer = S.ServiceCustomer_Rec_ID and
CStr(C.serviceCustomer) = S.relatedTo
INNER JOIN DataStatus D
ON C.Ready=D.Status_Rec_ID
ORDER BY 1 DESC;

Hope this will solve u r problem.

[hussein2000]

dear ITGURU

yes
i try the query but was not execute may some error is there

can u try another plz.


thank u

[ITGURU]

Dear hussein2000,

Without having database, i am unable to find the problem u facing, so it will be better if you send me database so that i can modify your query which will execute faster than what u want.