SQL question

[stickan]

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

[mxnmx]

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

[Leather]

Eh? that shouldnt happen...

[mxnmx]

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

[Wokawidget]

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

Woka

[stickan]

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?

[Wokawidget]

Use Left outer join:

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)

[stickan]

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?

[Wokawidget]

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



Woka