vbscript and SQL versus joins

[Ulrik48]

I have a database - with two tables, one which contains different countries and one containing user names and their choices - they are connected (relational) via a field called country number.

Now i want to make a selection showing which contries a user has picked - meaning which contries from the "available" table, has a certain user NOT selected. - this is my code but it doesnt work.

'VB
sub main()
Dim Portal
Dim Query
Dim uRS
Dim uSQL

'Connect to DB
'*************
Set uConn = server.CreateObject("ADODB.Connection")
uConn.Open Application("countrystr")

uSQL = "SELECT DISTINCT CountryCode, Name, User, CountrySelected FROM Contries FULL OUTER JOIN Selected ON countries.CountryCode <> Selected.CountrySelected WHERE User = 'Tom' AND countries.CountryCode <> Selected.CountrySelected ORDER BY CountryCode"

Set uRS = uConn.Execute(uSQL)

Do While not uRS.EOF
Content.Add(uRS("CountryCode") & " - " & uRS("Name") & &"</a><BR />")
uRS.MoveNext
Loop
Set uRS = Nothing
uConn.Close
end sub

[vass0922]

I dont believe you can use a join with <>
Wouldn't make much sense
The FULL OUTER JOIN should fulfill that purpose

[Ulrik48]

So what you mean is

uSQL = "SELECT DISTINCT CountryCode, Name, User, CountrySelected FROM Contries FULL OUTER JOIN Selected ON countries.CountryCode = Selected.CountrySelected WHERE User = 'Tom' AND countries.CountryCode <> Selected.CountrySelected ORDER BY CountryCode"

[Ulrik48]

It doesn't work - it gives all contries that the user has selected, I want those from the first table, that the user did not select

[Ulrik48]

uSQL = "SELECT DISTINCT CountryCode, Name, User, CountrySelected FROM Contries FULL OUTER JOIN Selected ON countries.CountryCode <> Selected.CountrySelected WHERE User = 'Tom' ORDER BY CountryCode"

Doesn't work either - same result

[vass0922]

OH then you need a left or right outer join, not full

try LEFT, see if you get the correct results.. if that doesn't work try RIGHT

[Ulrik48]

I did try both, both either works

[vass0922]

tell me this thing is a typo?

uSQL = "SELECT DISTINCT CountryCode, Name, User, CountrySelected FROM Contries FULL OUTER JOIN Selected ON countries.CountryCode <> Selected.CountrySelected WHERE User = 'Tom' AND countries.CountryCode <> Selected.CountrySelected ORDER BY CountryCode"

you're linking contries instead of countries

[Ulrik48]

its a typo - Im actually getting output - either all records from Selected or all records in Selected where User = "ULS" or nothing.
I Want it to show all records from Countries, that ULS did not select

[Ulrik48]

I have solved it - yes yes