Using nested queries:SQL

Question

I am trying to store the following data in a table where the visible_to is a multivalued attribute.

Wall_ID Facebook_ID Visible_To
W1      F1          F2,F3,F4
W2      F2          F1
W3      F3          F1
W4      F4          F1

I am trying to emulate Facebook on Oracle. I want to find the user who can view the max no of other's wall(here:F1).

I have gotten to the point of storing the multivalued attribute using NESTED table in Oracle 11g. Do I have to un-nest the table to find the result of the query or is there another way to do it?

Thanks!

The only other solution I can think of is storing the Visible_to in a separate table with Wall_ID as foreign key. — P R
– P R, Commented Feb 23, 2013 at 10:44
This scenario would be much easier to implement with a NOSQL graph database. Especially if you have many connections to other data sets the performance will be much better, too. — Xander
– Xander, Commented Feb 23, 2013 at 11:07
Hi, this is for a simple DB with a max of ten records. Is there anyway to continue with the nested table method and answer the query?? — P R
– P R, Commented Feb 23, 2013 at 11:22

Ben · Accepted Answer · 2013-02-23 12:57:54Z

If I'm understanding what you're trying to do correctly, then something like this should work (Please see the SQL Fiddle):

SELECT *
FROM (
  SELECT 
    f.wall_id
  , f.facebook_id
  , COUNT(t.COLUMN_VALUE) visible_to_count
  FROM facebook_data f
  CROSS JOIN TABLE(f.visible_to) t
  GROUP BY f.wall_id, f.facebook_id
  ORDER BY visible_to_count DESC
)
WHERE ROWNUM = 1

Results:

| WALL_ID | FACEBOOK_ID | VISIBLE_TO_COUNT |
--------------------------------------------
|      W1 |          F1 |                3 |

This simply unfolds the nested table and then aggregates so you can count up the number of values. You might also want to consider adding the DISTINCT keyword to the COUNT() function if you're likely to store duplicate values, or otherwise introduce cardinality in the query.

Collectives™ on Stack Overflow

Using nested queries:SQL

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related