Difficult Multi-table Query with OLEDB 1

[MarvinTheAndroid]

Hi,

I have spent the last several days trying to figure out how to do a particular query, and I am ready to pull my hair out. I will try to explain in as much detail as possible what I am trying to do.

OK, First off I am using Visual Studio 2003 to write a ASP.NET (VB.NET Code) web page that uses an MS ACCESS 2003 database. I know SQL is better, but this will be a home-grown self contained server-

The webpage will allow users to join, and once joined, will allow them to post/list publicly available game servers. Then other users will be able to subscribe to mailing lists for those servers. Got it?

So I have 3 tables: Users, Servers, and MailList

Users has the fields: ID,Name,Password,Email
Servers has the fields: Name, Info, Owner, ID
Maillist has the fields: UserID, ServerID

ID in both users and servers is a autonumber field, and the primary key

The relationships are straightforward:
Users.ID maps to Servers.Owner and Maillist.UserID
Servers.ID maps to Maillist.ServerID

So all three tables are linked.

Now I have a page called "Server list". The user logs in, and I store their User.ID in a session variable.
Basically, on the easy side, all the query does is return all the rows of Servers, and also returns the Owners name.
So I can list it like "Name: Joe's Server IP:xxx.xxx... Owner: BillyBob23"

This works: "SELECT * FROM Server,Users WHERE (Users.ID=Servers.Owner)"

Simple! Heres where it gets hard: I will have a hyperlink that will say "Join Mailing List" if they are not already the list for a particular server, which will say "Remove" if they are. I want to be able to tell if the user is already in a server's mailing list without having to re-query, since I need this info for each server in the list to set the hyperlink.

So basically, I want to query the maillist for only entries that relate to the current user: "SELECT * FROM maillist WHERE UserID=25" (or whatever the userID is)

Then I want to use that as a subquery, and match it up by server ID, so that all entries in the server list will show, and where there are matching entries in the mail list, return those too.

This works inside Access using 2 queries:
the first is the one I stated earlier that returns only my maillist entries. Note that I did not type this- I used the query designer, picked the fields, queries, tables, and changed the join methods.

the actual query is this : "SELECT Servers.*, Users.Name, [Mailinglist Filter by User].Server
FROM [Mailinglist Filter by User] RIGHT JOIN (Servers INNER JOIN Users ON Servers.Owner=Users.ID) ON [Mailinglist Filter by User].Server=Servers.ID
WHERE (((Users.ID)=Servers.Owner));"

This doesn't work from VB.NET since there are two queries, and I cannot figure out how to make it work. RIGHT JOIN does not seem to work at all.

I am completely open to suggestions. Change the table layout- whatever. I am really just learning DB programming, so I am overly eager to find an answer.

Is there a problem with LEFT or RIGHT JOIN in OLEDB? How do I do a subquery? What about syntax?

I'll be checking this entry probably every 10 minutes I am so stumped Thanks.

 

[PHV]

Something like this ?
"SELECT S.*, U.Name, M.ServerID
FROM (Servers S INNER JOIN Users U ON S.Owner=U.ID)
LEFT JOIN (SELECT ServerID FROM Maillist WHERE UserID=" & yourSessionVar & ") M ON S.ID=M.ServerID"

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[MarvinTheAndroid]

Hi, I'm not sure It looks like you abbreviated some of the table names. I'm not sure what this is :

"(Servers S INNER JOIN Users U ON S.Owner=U.ID)"

What is the S after Servers and the U after Users for?
I changed it to "Servers S INNER JOIN Users U ON Servers.Owner=Users.ID" but it didn't work.

 

[PHV]

Why changing something you don't understand ?
Have a look at Alias and embedded view.

 

[MarvinTheAndroid]

OK,

I tried it again, and it did not work. I keep getting this error:

"Syntax error in JOIN operation"

Do OLEDB JET connections support LEFT or RIGHT JOINS?

 

[PHV]

If you have the database at hand display the generated sql code in a textbox, copy it and paste it in the sql view of the query window.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[MarvinTheAndroid]

YOU ARE THE MAN!!! Sorry, I am really excited- I having been trying to figure this out since last week and I was at wit's end. It's not often Google fails me, but thankfully tek-tips and PHV were here!!!!!!!!!!

Works perfectly! I can now use this to improve our company intranet site as well.