SitePoint Sponsor

User Tag List

Results 1 to 7 of 7
  1. #1
    SitePoint Wizard mcsolas's Avatar
    Join Date
    Jul 2004
    Location
    Hermosa Costa Rica
    Posts
    1,707
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    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

  2. #2
    SitePoint Wizard davidjmedlock's Avatar
    Join Date
    Dec 2002
    Location
    Nashville, TN USA
    Posts
    1,688
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.

  3. #3
    SitePoint Wizard mcsolas's Avatar
    Join Date
    Jul 2004
    Location
    Hermosa Costa Rica
    Posts
    1,707
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,263
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Wizard mcsolas's Avatar
    Join Date
    Jul 2004
    Location
    Hermosa Costa Rica
    Posts
    1,707
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by 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
    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.

    Quote Originally Posted by r937
    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'
    )

    Quote Originally Posted by r937
    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.

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,263
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    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?)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Wizard mcsolas's Avatar
    Join Date
    Jul 2004
    Location
    Hermosa Costa Rica
    Posts
    1,707
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Dont worry about it, you already helped me with the real problem!


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •