Dopasowanie płci

Witam wszystkich,

przygodę z bazami zacząłem dość niedawno, dziś chcę prosić o pomoc w napisaniu pewnego zapytania.

Moja baza danych składa się z czterech tabel: Kobiet, Mężczyzn, CechPoszukiwanych (przez kobiety) oraz CechMężczyzn.
Struktura wygląda nastepująco:

 create table Kobiety
(
IdKobiety int not null primary key identity (1, 1),
Nazwisko varchar(30) not null
);
GO
create table Mezczyzni
(
IdMezczyzny int not null primary key identity (1, 1),
Nazwisko varchar(30) not null
);
GO
create table CechyPoszukiwane
(
IdCechyPosz int not null primary key identity (1, 1),
IdKobiety int not null,
Cecha varchar(20) not null
);
GO
create table CechyMezczyzny
(
IdCechyM int not null primary key identity (1, 1),
IdMezczyzny int not null,
Cecha varchar(20) not null
);
GO
alter table CechyPoszukiwane
add constraint fk_cPosz_K foreign key (IdKobiety) references Kobiety(IdKobiety);
GO
alter table CechyMezczyzny
add constraint fk_cechyM_M foreign key (IdMezczyzny) references Mezczyzni(IdMezczyzny);

Chcę dla każdej kobiety wybrać mężczyznę, który posiada najwięcej cech poszukiwanych przez daną kobietę. Kombinowałem na przeróżne sposoby, jednak nie udało mi się wydostać tej informacji. Ostatnia moja próba wyglądała tak:

 select k.Nazwisko, m.Nazwisko, cp.Cecha
from Kobiety k left join CechyPoszukiwane cp on k.IdKobiety = cp.IdKobiety
join CechyMezczyzny cm on cp.Cecha = cm.Cecha
join Mezczyzni m on cm.IdMezczyzny = m.IdMezczyzny
group by k.IdKobiety, k.Nazwisko, m.Nazwisko, cp.Cecha
having COUNT(cp.Cecha) = 
(
	select MAX(result.ile) from
	(
		-- Ile wspolnych cech ma kazda kobieta z kazdym mezczyzna
		select k.Nazwisko, count(cm.Cecha) ile
		from Kobiety k join CechyPoszukiwane cp on k.IdKobiety = cp.IdKobiety
		join CechyMezczyzny cm on cp.Cecha = cm.Cecha
		join Mezczyzni m on cm.IdMezczyzny = m.IdMezczyzny
		group by k.Nazwisko, cm.Cecha
		/*select k.Nazwisko, m.Nazwisko, cm.Cecha, COUNT(cp.Cecha) as ile
		from CechyPoszukiwane cp join CechyMezczyzny cm on cp.Cecha = cm.Cecha
		join Mezczyzni m on cm.IdMezczyzny = m.IdMezczyzny
		join Kobiety k on cp.IdKobiety = k.IdKobiety
		where k.IdKobiety = cp.IdKobiety and cp.Cecha = cm.Cecha
		group by cm.IdMezczyzny, m.Nazwisko, cm.Cecha, k.Nazwisko*/
	) result
);

Próbowałem też wybrać cechy, których poszukuje każda z kobiet i porównywać z cechami mężczyzn, ale nie wiem, czy to możliwe, nie udało mi się i nie wiem jak to zrobić. Dlatego proszę o wskazówki/sugestie, coś, co mogłoby mnie jakoś naprowadzić.

Poczytaj o funkcjach rankingujących http://msdn.microsoft.com/en-us/library/ms189798.aspx

select ROW_NUMBER() OVER (PARTITION BY D.IdKobiety ORDER BY D.EqFeatures desc) as rownum,
        D.*, K.mazwisko, M.nazwisko
from
(
    select IdKobiety, IdMezczyzny, count(*) as EqFeatures
    from CechyPoszukiwane CP
    join CechyMezczyzny CM ON CP.Cecha = CM.Cecha
    group by IdKobiety, IdMezczyzny
) as D
inner join Kobiety K on D.IdKobiety = K.Id
inner join Mezczyzni M on D.IdMezczyzny = M.Id
where rownum = 1

Nie jestem tylko pewien czy w tym samym zapytaniu możesz użyć row_number i od razu jego wartość w where. Chyba nie. Więc musisz wziąć to z row_number() jako podzapytanie i je dopiero przefiltrować where. A nie mam sql servera pod ręką żeby to sprawdzić.

Bardzo dziękuję za odpowiedź. Funkcja rankingowa jest chyba tym, czego potrzebuję, ale mam problem z kilkoma wynikami zwracanymi przez podzapytanie.

 select D.*, K.Nazwisko, M.Nazwisko
FROM
(
SELECT IdKobiety, IdMezczyzny, COUNT(*) AS EqFeatures
FROM CechyPoszukiwane CP
JOIN CechyMezczyzny CM ON CP.Cecha = CM.Cecha
GROUP BY IdKobiety, IdMezczyzny
) AS D
INNER JOIN Kobiety K ON D.IdKobiety = K.IdKobiety
INNER JOIN Mezczyzni M ON D.IdMezczyzny = M.IdMezczyzny
WHERE
(
	SELECT ROW_NUMBER() OVER (PARTITION BY D.IdKobiety ORDER BY D.EqFeatures DESC) AS rownum from
	(
		SELECT IdKobiety, IdMezczyzny, COUNT(*) AS EqFeatures
		FROM CechyPoszukiwane CP
		JOIN CechyMezczyzny CM ON CP.Cecha = CM.Cecha
		GROUP BY IdKobiety, IdMezczyzny
	) sth
) = 1

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

Ten sam problem miałem próbując to rozwiązać innymi sposobami.

Natomiast takie zapytanie:

SELECT K.Nazwisko, M.Nazwisko
FROM
(
SELECT IdKobiety, IdMezczyzny, COUNT(*) AS IleCechWspolnych
FROM CechyPoszukiwane CP
JOIN CechyMezczyzny CM ON CP.Cecha = CM.Cecha
GROUP BY IdKobiety, IdMezczyzny
) AS D
INNER JOIN Kobiety K ON D.IdKobiety = K.IdKobiety
INNER JOIN Mezczyzni M ON D.IdMezczyzny = M.IdMezczyzny
group by K.Nazwisko, M.Nazwisko, D.IleCechWspolnych
having D.IleCechWspolnych = 
(
	select MAX(res.ile) from
	(
	SELECT IdKobiety, IdMezczyzny, COUNT(*) AS ile
	FROM CechyPoszukiwane CP
	JOIN CechyMezczyzny CM ON CP.Cecha = CM.Cecha
	GROUP BY IdKobiety, IdMezczyzny
	) res
) 

zwraca mi parę najlepiej dopasowaną. Nie wiem, w jaki sposób opanować kilka wyników zwracanych przez podzapytanie.

Ale nie tak. Podzapytanie to nie znaczy wrzuć kawał kodu w where.
Zastanów się poza tym ile masz zbędnych złączeń.

select  D.IdKobiety, K.nazwisko as KobietaNazwsko, D.IdMezczyzny, M.nazwisko as MezczyznaNazwisko
from
(
  SELECT ROW_NUMBER() OVER (PARTITION BY D.IdKobiety ORDER BY D.EqFeatures DESC) AS rownum,
          D.*
  FROM
  (
      SELECT IdKobiety, IdMezczyzny, COUNT(*) AS EqFeatures
      FROM CechyPoszukiwane CP
      JOIN CechyMezczyzny CM ON CP.Cecha = CM.Cecha
      GROUP BY IdKobiety, IdMezczyzny
  ) AS D
)
INNER JOIN Kobiety K ON D.IdKobiety = K.Id
INNER JOIN Mezczyzni M ON D.IdMezczyzny = M.Id
WHERE D.rownum = 1

Dziękuję, to bardzo pożyteczna lekcja.

Chciałbym teraz wyświetlić wszystkie kobiety, bez względu na to, czy są jacyś mężczyźni spełniający ich wymagania czy nie, w jaki sposób to zrobić? Zmiana

inner join Kobiety K on D.IdKobiety = K.IdKobiety 

na RIGHT JOIN nie załatwi sprawy, bo w wewnętrznym podzapytaniu wybrane będą tylko IdKobiet, które szukają jakichś cech, a w wymienionej linii warunkiem złączenia jest istnienie IdKobiety w tabeli D.

zacznij od tabeli Kobiety a do niej left join dodaj tą część dobierającą mężczyznę (czyli z przefiltrowaniem po rownum=1)

Przepraszam za ignorancję, jeżeli o to chodziło to nie dostaję nazwiska kobiety, której cechy poszukiwane nie pasują do tych posiadanych przez istniejących mężczyzn:

    SELECT D.IdKobiety, K.nazwisko AS KobietaNazwsko, D.IdMezczyzny, M.nazwisko AS MezczyznaNazwisko
    FROM Kobiety K LEFT JOIN
    (
    SELECT ROW_NUMBER() OVER (PARTITION BY D.IdKobiety ORDER BY D.EqFeatures DESC) AS rownum,
    D.*
    FROM
    (
    SELECT IdKobiety, IdMezczyzny, COUNT(*) AS EqFeatures
    FROM CechyPoszukiwane CP
    JOIN CechyMezczyzny CM ON CP.Cecha = CM.Cecha
    GROUP BY IdKobiety, IdMezczyzny
    ) AS D
    ) AS D
    ON D.IdKobiety = K.IdKobiety
    LEFT JOIN Mezczyzni M ON D.IdMezczyzny = M.IdMezczyzny
    WHERE D.rownum = 1

SELECT D.IdKobiety, K.nazwisko AS KobietaNazwsko, D.IdMezczyzny, M.nazwisko AS MezczyznaNazwisko
FROM Kobiety K 
LEFT JOIN
    (
      select T.IdKobiety, T.IdMezczyzny from
      (
        SELECT ROW_NUMBER() OVER (PARTITION BY D.IdKobiety ORDER BY D.EqFeatures DESC) AS rownum,
                    IdKobiety, IdMezczyzny
        FROM
        (
          SELECT IdKobiety, IdMezczyzny, COUNT(*) AS EqFeatures
          FROM CechyPoszukiwane CP
          JOIN CechyMezczyzny CM ON CP.Cecha = CM.Cecha
          GROUP BY IdKobiety, IdMezczyzny
        ) AS D
      ) AS T
      where T.rownum = 1
    ) AS D ON D.IdKobiety = K.IdKobiety
    LEFT JOIN Mezczyzni M ON D.IdMezczyzny = M.IdMezczyzny

Wszystkie kobiety i ewentualnie mężczyźni, którzy spełniają najwięcej pożądanych kryteriów.

Jest mi trochę głupio, bo rozmiar pomocy jest dość znaczny, ale nauczyłem się kolejnych sposobów rozwiązań za co szczerze dziękuję.

Tak zrobiłem.

Użyłem też dense_rank, by wyświetlić wszystkich mężczyzn, którzy spełniają wymagania kobiety.

Nie ma mężczyzn, którzy spełniają wymagania kobiety. Nawet jeśli kiedyś jakiś spełniał, to po delta t dążącym do zera przestał. ;P