Multi Share View

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 property_marketing.property 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,
  property_marketing.city 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,
  user1.name AS name,
  user1.email AS email,
  user1.phone AS phone
FROM (property_marketing JOIN
    user ON user.sharegrp = property_marketing.sharegrp) JOIN
  user user1 ON property_marketing.Userid = user1.userid
Starquest321Asked:
Who is Participating?
 
ralmadaConnect With a Mentor Commented:
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 + ',%'
...
0
 
Starquest321Author Commented:
Correct:
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


Result:
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 :(
0
 
ralmadaConnect With a Mentor Commented:
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.
0
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

 
Starquest321Author Commented:
mysql. . .
0
 
ralmadaCommented:
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:

http://forums.phpfreaks.com/index.php?topic=153010

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

http://stackoverflow.com/questions/11100501/split-comma-separated-values-from-one-column-to-2-rows-in-the-results-mysql

And then loop through each row in the tables and create new normalized ones.
0
 
Starquest321Author Commented:
I am in php :)

I am assuming it's not a simple select statement . . .
0
 
ralmadaCommented:
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

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.