1 query I cant figure out .. but it stops my whole app

[mcsolas]

I have posted before and I am reading the SQL in 21 days book .. im trying but I just cant figure out this one thing I need here. I have 2 tables that I need to join together > Provider & Users. Going to try and break it down to simple form in order to get this solved. This query is really working me as I am relying on this info to run the whole APP I am desiging. So until I fix this .. yeah I am just SOL. Please help :'(

About the Users table its there solely to hold login information for 6 other tables on the site ( Provider, Consumer, etc .. ) Each one of these tables points to the other tables with a combo of 2 fields: Users.UserTable, Users.UserTableID. So to display a Username on a details page where we are looking at Provider 18, I lookup the username like this:

Select * FROM Users WHERE Table = 'Provider' AND TableID = '18'

Now I get this far. The difference between the query I have below and the one I need:
This one will show me ALL the Providers that DO have a UserName associated. The trick is the MOST providers WONT have a login, its optional in this system for that to be setup. I have 1000 records and this query bring back 20 .. because only 20 have setup a login name. That is correct of course if that was the info I needed to show (it isn't)

Select Provider.ProviderID, Users.UserID, Users.UserName FROM Provider,Users
WHERE Users.UserTable = 'Provider' AND Provider.ProviderID = Users.UserTableID

Example result from this query (shows 20 of 1000 records )

ProviderID, UserName

3 ProviderUserName1
5 ProviderUserName2

What I need to show ( essentially SELECT * - 1000 records )

ProviderID, UserName

1 NULL
2 NULL
3 ProviderUserName1
4 NULL
5 ProviderUserName2

[davidjmedlock]

Here ya go:

Select Provider.ProviderID, Users.UserID, Users.UserName FROM Provider,Users
WHERE Users.UserTable = 'Provider' AND Provider.ProviderID *= Users.UserTableID

Notice the '*' in the clause. That means that you want to bring in all provider records, even if there is no user record. Hope that works for ya.

[mcsolas]

Ahh .. the limitations of MS access. I can use this once I get myself to SQL server.

So I am trying another way...

SELECT Provider.ProviderID, Users.UserID, Users.UserName
FROM Provider
LEFT OUTER JOIN Users ON Provider.ProviderID=Users.UserTableID
WHERE Users.UserTable='Provider'

It seems that this type of join is what I need, I am reading an ebook with a nice example... but its not quite the same cause it doesn't show how to use the where statement along with it. I tried without the "WHERE Users.UserTable='Provider'" part too.

With WHERE > returns only the ones with a user name
without where > it returns NULL values for many fields but it matched the RowID to anyones row ( matching a provider to a consumer login for instance ) because without telling it that I need those with UserTable = 'Provider' brings back all users.

Alternate thinking: Originaly I had the relationship setup in the Provider table itself to point to the username field. It was much easier to work with through the sql .. although its not necessary .. I just cant get this to work and I am deperate to do so now.

Im still a newb at sql & db design here obviously.

[r937]

whenever you post a question in the Databases forum, it is a good idea to mention what database you're using so that you don't waste people's time giving you the wrong syntax

for example, dave gave you oracle (or was it sybase?) outer join syntax, when in fact you needed microsoft access outer join syntax

anyhow, your problem is simple

change this --

SELECT Provider.ProviderID, Users.UserID, Users.UserName
FROM Provider
LEFT OUTER JOIN Users
ON Provider.ProviderID=Users.UserTableID
WHERE Users.UserTable='Provider'

to this --

SELECT Provider.ProviderID, Users.UserID, Users.UserName
FROM Provider
LEFT OUTER JOIN Users
ON Provider.ProviderID=Users.UserTableID
AND Users.UserTable='Provider'



and yes, if you are storing user information in 6 different tables, you might want to take a closer look at the benefits of that design versus one single table

[mcsolas]

whenever you post a question in the Databases forum, it is a good idea to mention what database you're using so that you don't waste people's time giving you the wrong syntax

Duly noted and I apologize for the omission. I have been trying to figure out this query for some time ( I had a long long long UNION one working but it was crashing the app occasionally. At this point, I was seeing crosseyed The query I just replaced is a page long + like 10 dynamic statements to adjust the parameters.

anyhow, your problem is simple

THANK YOU! Now I learned this and I should be fine. Going to apply this method to 6 pages on the site now. Had to add these ( to make access happy )

SELECT Provider.ProviderID, Users.UserID, Users.UserName
FROM Provider
LEFT OUTER JOIN Users ON (
Provider.ProviderID=Users.UserTableID
AND Users.UserTable='Provider'
)

and yes, if you are storing user information in 6 different tables, you might want to take a closer look at the benefits of that design versus one single table

Yes I have been thinking about this for some time. The business is entity based. We are grouping a portion of 1 type to service another. Each of these entities has both group and inividual records. So our tables are as follows: ProviderGroup ( 1 row ) for many Providers who relate to that group. Same with ConsumerGroup / Consumer side.

Now, its not required that they login to participate in the system. So MANY of these guys are not going to have logins ( hence the need for this type of query ) .. but it is optional for them to setup. So I created the tables based on the entities and then that master Users table to store login data in 1 place sitewide. There is also a unique referrerID number that people will be getting, which is also in my Users table. So my 2 fields I want to keep unique UserName and ReferrerID are now in the Users table... so I am not sure if that shows up as red flags in your eyes, but I would love to know.

[r937]

login data in one place i can understand, assuming you actually need separate tables for the individuals

providergroup--providers and consumergroups--consumers might be combined, though

i'd really have to see your entire application and the types of processes you run against the database, and i'd actually rather not (no offence, okay?)

[mcsolas]

Dont worry about it, you already helped me with the real problem!