Multi Share View

Posted on 2012-09-14
Last Modified: 2012-11-04
I have a view that I need to create. But instead of the "sharegrp" from the user being a one to one relationship I need a "contains" relationships. Here is an example:

User John: Sharegrp - Blue, Yellow
User Bill: Sharegrp - Yellow

Marketing Record Sharegrp: Yellow

BOTH John and Bill see it...

What I have below ONLY works for a one to one group mapping, not contains.

SELECT AS property,
  property_marketing.development AS development,
  property_marketing.unit AS unit,
  property_marketing.type AS type,
  property_marketing.title AS title,
  property_marketing.purpose AS purpose,
  property_marketing.price_sale AS price_sale,
  property_marketing.price_rent AS price_rent, AS city,
  property_marketing.sharegrp AS sharegrp,
  property_marketing.rooms AS rooms,
  property_marketing.size AS size,
  property_marketing.description AS description,
  property_marketing.amenities AS amenities,
  property_marketing.picture AS picture,
  property_marketing.status AS status,
  property_marketing.Userid AS Userid, AS name, AS email, AS phone
FROM (property_marketing JOIN
    user ON user.sharegrp = property_marketing.sharegrp) JOIN
  user user1 ON property_marketing.Userid = user1.userid
Question by:Starquest321
    LVL 41

    Accepted Solution

    If I understood correctly,
    user.sharegrp could have a comma separated string and

    property_marketing.sharegrp single values. Right?

    If so, what about something like this?

    FROM property_marketing JOIN
        user ON ',' + user.sharegrp + ',' like '%,' + property_marketing.sharegrp + ',%'

    Author Comment

    user.sharegrp could have a comma separated string and

    property_marketing.sharegrp single values.

    BUT also multiple values. . .

    So a more complex situation would be:

    User John: Sharegrp - Blue, Yellow, Green
    User Bill: Sharegrp - Yellow, Red
    User: Smith: Sharegrp - Red, Green

    Marketing Record 1  Sharegrp: Yellow, Green
    Marketing Record 2 Sharegrp: Red

    Share record  1 with John and Smith
    Share record 2 Smith
    etc. etc.

    Also since I am a little new at this if you can post the whole select statement I would appreciate it so I can cut and paste. I don't know how to write my own yet :(
    LVL 41

    Assisted Solution

    Ok, so you definitively have a design problem there. You should have had those multiple concatenated values in individual rows. That will make the join really easy.

    Now, having said that, can you please advise what database engine are you using? MySQL and MS SQL server are different, and as such, different solutions will apply.

    Author Comment

    mysql. . .
    LVL 41

    Expert Comment

    Alright, in mysql things will get a bit complicated. Again, that's because your database is not normalized. Check this link to understand what I'm talking about:

    So to split your comma separated values, you will need a function similar to Explode in PHP. Unfortunately, there's no explode function in mySQL. If you can go with PHP, then that will be easier. If not, then you will have to create a procedure like the one suggested below

    And then loop through each row in the tables and create new normalized ones.

    Author Comment

    I am in php :)

    I am assuming it's not a simple select statement . . .
    LVL 41

    Expert Comment

    Ok, can you make changes to your table design? that's what's causing this problem. You need to normalize your data.

    In php you want to use explode with array_intersect and put that in a loop to compare them. Here's the high level idea:

    $a1 = explode(",", yourcolumn)
    $a2 = explode(",", yourothercolumn)
    if(!empty(array_intersect($a1, $a2))
    //do something as matches were found 

    Open in new window


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    Foreword In the years since this article was written, numerous hacking attacks have targeted password-protected web sites.  The storage of client passwords has become a subject of much discussion, some of it useful and some of it misguided.  Of cou…
    More Fun with XML and MySQL – Parsing Delimited String with a Single SQL Statement Are you ready for another of my SQL tidbits?  Hopefully so, as in this adventure, I will be covering a topic that comes up a lot which is parsing a comma (or other…
    Migrating to Microsoft Office 365 is becoming increasingly popular for organizations both large and small. If you have made the leap to Microsoft’s cloud platform, you know that you will need to create a corporate email signature for your Office 365…
    In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor ( If you're interested in additional methods for monitoring bandwidt…

    737 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    22 Experts available now in Live!

    Get 1:1 Help Now