SQL question

I have ACCESS 2000 in two different computers and copy the same db to these, and then copy the same SQL(Select)-question and get different answers, what can be the reason :confused:

Why dont you post your database & the SQL statement here...

Eh? that shouldnt happen...

Yea, that shouldnt happen, it might just be some small problem...somewhere....

Not if they are searching on todays date and the dates are set incorrectly for each comp :D

Woka

I think that I know where the problem is

Say that I am joining two tables (The SQL question is mutch bigger than this in reality)

Select Table1.Field1, Table2.Field2
FROM Table1 LEFT JOIN Table2 ON Table1.Field1 = Table2.Field1
WHERE ...

Now Field2 is not always existing in Table2, but I still want the value of Field1 in Table1, So I choose to use a LEFT JOIN

Now I want to have a selection on which field2:s in Table2 I want to join with.

I can't have it after WHERE Like:
"WHERE Table2.Field3 = 1" when it would conflict with which fields of Table1.Field1 that would be Selected so i tried something like

FROM Table1 LEFT JOIN Table2 ON Table1.Field1 = Table2.Field1 AND Table2.Field3 = 1
WHERE

Access accepts it...
but it seems like the question get ambiguous then...
(At least I can see that it happens...)

Maybee I'm overlooking something easy, when the question is quite complex with 2 other questions inside itself (One as a selected field and one as a selection) and 2 left joins but how do I restrict the the joined fields whitout affecting the hole question in the question above?

Use Left outer join:

Code:

---

SELECT tblJobs.JobNumber, ISNULL(tblInvoices.InvoiceNumber, '') AS invoiceNumber
FROM tblJobs
LEFT OUTER JOIN tblInvoices ON tblInvoices.JobNumber = tblJobs.JobNumber AND tblInvoices.Cancelled = FALSE
WHERE tblJobs.Cancelled = FALSE

---

Woka

PS It still won't affect your original Q. (Post number 1)

I will try that as soon as I can get my hand on the computer where the question don't work. On my computer the OUTER don't affect a thing, I still get the right answer with or without it. Hopfully I get the same right answer on the other computer if I use it, otherwise I'll be back... ;)

Hmm... do you think that MDI-forms isn't good for anything?

They are good for MDI applications...like word, excel etc THAT's IT!

:D

Woka