[MS-SQL] Problem with left join... Any Guru??

Hello,

I am using this code:


It is working fine WITH one MAJOR exception
I can retrieve all the fields from the PDM_USER_2 except the MAINKEY....
And that's very annoying, I'm stuck!

the problem must come from the part:
and
But I cannot change them, as I need to retrieve all the users from PDM_USER_2 that have no record for a specific period in PDM_USER_DOSE_2

Any idea how to get my fields MAINKEY from PDM_USER_2 ???

Thanks in advance , cos I'm really really stuck!
Ludo

your question is very nicely written and it's quite clear what you want

however, you should already be getting MAINKEY if this is indeed a column in the PDM_USER_2 table

do yourself a favour, don't use "select star"

instead, list all the columns you want

in this particular case, where you are actually looking for rows in the left table that don't have a match in the right table, "select star" will always include all columns from both tables, but the columns from the right table will all contain nulls, which, when you think about it, is exactly what you asked for, so why ask for columns from the right table?

rudy

Hello

Ok, I will try to explain a bit more
With the result of this query I display a table:


It is normal to have the dose column empty as they have no result for this period..
However, I need that their name link them to their profile ex:

In this code the "3", is the MAINKEY from the PDM_USER_2.

But with the query, mentionned in my first post, the MAINKEY from PDM_USER_2 allways return "" ...
then all the links are like


and that's why I have to find how to get this MAINKEY, which is not taken actually!

Any idea?
thxs Ludo

run your query outside of your asp script and see what it returns

if MAINKEY is actually a column in the left table of a left outer join, you will see it

i'm thinking it's your script that's the problem

hmm how to run the script outside asp? I don't know

but the script is working if I display all the users.