?
Solved

Multi Share View

Posted on 2012-09-14
9
Medium Priority
?
264 Views
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 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
0
Comment
Question by:Starquest321
  • 4
  • 3
7 Comments
 
LVL 41

Accepted Solution

by:
ralmada earned 2000 total points
ID: 38398815
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
 

Author Comment

by:Starquest321
ID: 38399455
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
 
LVL 41

Assisted Solution

by:ralmada
ralmada earned 2000 total points
ID: 38399728
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
Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

 

Author Comment

by:Starquest321
ID: 38400371
mysql. . .
0
 
LVL 41

Expert Comment

by:ralmada
ID: 38405672
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
 

Author Comment

by:Starquest321
ID: 38408269
I am in php :)

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

Expert Comment

by:ralmada
ID: 38409448
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

Featured Post

 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows the steps required to install WordPress on Azure. Web Apps, Mobile Apps, API Apps, or Functions, in Azure all these run in an App Service plan. WordPress is no exception and requires an App Service Plan and Database to install
Containers like Docker and Rocket are getting more popular every day. In my conversations with customers, they consistently ask what containers are and how they can use them in their environment. If you’re as curious as most people, read on. . .
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
Suggested Courses
Course of the Month15 days, 23 hours left to enroll

850 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