Query annoying me (1 Viewer)

[Beany]

Hi,

im using the following query:

SELECT Contacts.ContactID, Contacts.DateOfBirth, Contacts.Disabled, Contacts.PostalCode1, Contacts.Ethnicity, [Language].[Language], Contacts.Sex, Calls.Callid, Calls.Subject_Category, Calls.[Level], Calls.ContactMethod, (SELECT Calls.CallDate IF Calls.CallDate = Contacts.DateFirstContact) FROM (Contacts INNER JOIN Calls ON Contacts.ContactID = Calls.ContactID) LEFT JOIN [Language] on Contacts.[Language] = [Language].LanguageID WHERE CallDate BETWEEN " & fromDate & " AND " & toDate

There seems to be a problem with this, i cant work it out??

Can someone please have a look for me? Many thanks

 

[namliam]

it would be very nice to know what your problem actually is so we can do something about it.

It would also be nice to see the WHOLE code instead of just this bit of SQL.

 

[Beany]

well there isnt any problem with the code because i dont get problem with other quieries. With this query i am getting a syntax error.

The problem in more detail is this:
I am using vb.net and connecting to access database.
i have two tables contacts and calls which has a relationship of one to many e.g. one contact can have many calls contact has a field datefirstcontact and call has a field date.call i need to get the information from both tables (the first select query above) but i also need to know if it is the contacts first call or not (the second select query) But i am getting a syntax error when i run the whole query but if i take the second query out it runs fine.

I hope this information helps

 

[namliam]

SELECT Contacts.ContactID, Contacts.DateOfBirth, Contacts.Disabled, Contacts.PostalCode1, Contacts.Ethnicity, [Language].[Language], Contacts.Sex, Calls.Callid, Calls.Subject_Category, Calls.[Level], Calls.ContactMethod, (SELECT Calls.CallDate IF Calls.CallDate = Contacts.DateFirstContact) FROM (Contacts INNER JOIN Calls ON Contacts.ContactID = Calls.ContactID) LEFT JOIN [Language] on Contacts.[Language] = [Language].LanguageID WHERE CallDate BETWEEN " & fromDate & " AND " & toDate



The red bit is totaly wrong, you cannot do that that way... Where did you pick up SQL like that??

The purple part tells me this query is build up someplace in coding... but that doesnt matter (much)

 

[Beany]

I am using vb.net and connecting to access. My database connection code is not the problem because i have done other queries that work fine.

How would you suggest i go about quering this, The problem i am having is doing a select within select in access because its bringing syntax errors up?

Many thanks

 

[namliam]

Yes well for one the red part is NOT a valid SQL statement as far as I know in ANY sql language (please tell me if I am wrong)

A valid sql statement goes like
Select
From
Where

I have no clue as to exactly what you are doing... but
FROM (Contacts INNER JOIN
Calls ON Contacts.ContactID = Calls.ContactID) LEFT JOIN [Language] on Contacts.[Language] = [Language].LanguageID WHERE CallDate BETWEEN " & fromDate & " AND " & toDate

In above you are using the calls and contacts table and the "extra" (red) select must be done in a simular way.

 

[Beany]

So you cannot do a select within a select?

 

[namliam]

You could... but it is teribly slow and certainly not in this way.