do my sql homework

you've got an entity called dude
dude likes porn
your task is to find for every dude a second dude whose taste in porn matches the most in percent
the resulting table would look like this:

dude1 dude2 match
    1     2   100%
.
.
.
shit requires some serious thinking
you're not allow to create own tables in the database but you can use as many views as you feel like

I fire up Scheme and my wizard book.

what's the input table? if i don't have anything to work from, i can't do much.

>>3
there're only two entities
dude with the attribute dudeID and porn with the attribute pornID
it's a 1 to n relationship
so this is how it'd look like:

dude_porn_relationship
dude id    porn id
   1          1
   1          2
   1          3
   2          2
   2          3
   3          1
   3          3
   4          2
   5          null            (fag)
   6          null            (another fag)

it's a 1 to n relationship
i noticed that this could be confusing
lets use (min, max) notation instead: dude (0,*) --- (1, *) porn

First, you'll need to define what a match is. Given dudes A and B, something like this would work:

match = common_porn(A, B) / max(porn(A), porn(B))

The numerator can be calculated by counting dude_porn_relationship joined with itself on porn id. You can get the denominator using a couple of simple count queries matching IDs of A and B. For both the numerator and denominator, you'll need to "tag" the running results with the two dude IDs so that you can join them to finally get your match column. You'll want to special-case a denominator of zero to map to a match of 0%. Sorting on the match column and aggregating the max grouped by one of the dude id columns should be easy.

Any SQL gurus know if this sort of thing is possible without subqueries?

Hmm...

create table dude_porn (dude_id integer, porn_id integer);
insert into dude_porn values (1,1),(1,2),(1,3),(2,2),(2,3),(3,1),(3,3),(4,2),(5,null),(6,null);
create view dude_porn_count as select dude_id, count(porn_id) from dude_porn group by dude_id;
select distinct a.dude_id as dude1,b.dude_id as dude2,100-100*abs(ac.count-bc.count)/(select max(count) from dude_porn_count) as match from dude_porn a, dude_porn b, dude_porn_count ac, dude_porn_count bc where a.dude_id=ac.dude_id and b.dude_id=bc.dude_id and a.dude_id<b.dude_id order by match desc;

 dude1 | dude2 | match
-------+-------+-------
     2 |     3 |   100
     5 |     6 |   100
     1 |     2 |    67
     1 |     3 |    67
     2 |     4 |    67
     3 |     4 |    67
     4 |     5 |    67
     4 |     6 |    67
     1 |     4 |    34
     2 |     5 |    34
     2 |     6 |    34
     3 |     5 |    34
     3 |     6 |    34
     1 |     5 |     0
     1 |     6 |     0
Seems to produce the results you're looking for, although I'm sure it could be optimized.

>>1
What do you want displayed if a dude has more than one other dude with the same number of matches, and they are the best match?

>>8

>>7 already solved it
kudos

Note, that only takes into account how much porn each dude has... If you want to know what dudes have the same porn, you'd need to add another join for that. I think if you make a view joining dude1.porn_id to dude2.porn_id and built the count off of that, it'd work.

Also if I ever become a CS prof I'm totally going to base my lectures on dudes and porn.

I will also base mine on dudes in porn.

Don't call me gay, but I need some mary jay!

Marijuana MUST be legalized.