Keskustelu: Ohjelmointikysymykset: SQL-lause

Muutaman päivän tässä koittanu yhtä projektia tehdä, mutta kun ei ole sql oikeen vielä hallinnassa.
Ajattelinkin kysyä, jospa täällä olisi viisaampi ratkomaan ongelman mun puolesta.

Eli projektina on tehdä eräänlainen rekrylomake. Käytössä on tietokannassa 3 taulua, joissa yksi sisältää työnhakijat, toinen työnantajat ja kolmas tiedot kenelle työnantajalle on ilmoitettu työntekijästä x ja random salasana CV:n katselua varten.

Kolmanteen tauluun sijoitetaan työnantajan tietojen id sekä työnhakijan id.
Kysymys kuuluukin, miten saisin nyt eroteltua nämä tiedot pois hausta, jolla haen uusia vastaavuuksia tiedoista.

Jos en nyt ihan metsässä ole (vaikkakin epäilen vahvasti olevani) niin annan tähän esimerkin miten olen koittanut tehdä.

Taulu tyonhakija sisältää kentän id (thid), nimi (nimi).
Taulu tyonantaja sisältää kentän id (taid), yritys (yritys).
Taulu salasanat sisältää työnhakijan id (thid), työnantajan id (taid), salasana (passwd).

haen tietoa seuraavalla lauseella:
select * from tyonhakija,tyontekija,salasanat where (tyonhakija.thid != salasanat.thid AND tyonantaja.taid != salasanat.taid)

Kuitenkin lauseella haettu tieto sisältää myös tiedot, jotka pitäisi poissulkea.
Jotain siis teen väärin, mutta en itse keksi enää, että missä menee pieleen.

Toivottavasti joku ymmärsi mitä haen takaa ja osaa auttaa. :)

Tuollainen haku on järjetön, koska työnhakija- ja yritystauluissa ei ole mitään yhteistä tekijää. Tämä johtaa siihen, että jokainen tuontyyppinen haku sisältää kaikki työnantajat. Voisit samalla muuten kertoa, miten tuon haun pitäisi antaa, silloin kun se toimii oikein.

Teuro on aivan oikeassa. Muodostapa ensiksi haku, joka löytää ne yhteydet välittämättä siitä, onko ne löydetty jo ennenkin. Tässä haussa EI pitäisi olla ollenkaan taulua salasanat. Vasta kun tämä on selvä, voit ruveta tuota esittämääsi kysymystä miettimään.

Yksi vaihtoehto siinä vaiheessa on subquery, jossa tarkistetaan tuo löytyminen. Toinen (nopeampi?) vaihtoehto taas on LEFT JOIN. Tässäpä nuo molemmat. Kyselyn alku on kummassakin sama, LIKE-ehto nyt simuloi sitä, mitä ylemmässä kappaleessa mainitsin sinun vielä tarvitsevan. ;)

Subquerylla lasketaan löytyneet salasanarivit, ei saisi löytyä.

SELECT * FROM tyonantaja, tyonhakija
WHERE
  tyonantaja.tarjous LIKE tyonhakija.haluaa
  AND (
    SELECT COUNT(*) FROM salasanat
    WHERE taid = tyonantaja.taid AND thid = tyonhakija.thid
  ) = 0

LEFT JOIN laittaa nullit, jos salasanariviä ei löydy. Otetaan siis vain ne tulokset, joissa salasana on NULL.

SELECT * FROM tyonantaja
INNER JOIN tyonhakija
  ON tyonantaja.tarjous LIKE tyonhakija.haluaa
LEFT JOIN salasanat
  ON salasanat.taid = tyonantaja.taid AND salasanat.thid = tyonhakija.thid
WHERE salasanat.salasana IS NULL

Arvelinkin että joku mättää :D
Kunhan juon kahvia niin luen uudestaan ja alan pohtimaan ratkaisua. Kyselen sit uudestaan jos tarviaa.

Kiitoksia tästä jo :)

// Edit: Joko en osaa tai sit se ei oikeesti meinaa toimia. Kumpikaan ehdotetuista ei palauta haluttua tulosta.
Voipi olla siis parempi miettiä koko taulurakennetta uusiksi.

Ylempään koodiin oli lipsahtanut triviaaleja virheitä (erisuuruus yhtäsuuruuden sijaan, virheitä taulujen nimissä), korjasin nämä. Molempien koodien pitäisi toimia aivan hyvin, kunhan väsäät sen vastaavuuden tarkistamisen järkevästi.

Seuraava koodi on testattu. Relaatioita en jaksanut kirjoitella, ne olisi hyvä lisätä, ettei salasanat-tauluun jää rivejä, joiden työnantajat tai -hakijat on jo poistettu.

# luodaan taulut
CREATE TABLE tyonhakija (thid INTEGER PRIMARY KEY AUTO_INCREMENT, haluaa VARCHAR(255));
CREATE TABLE tyonantaja (taid INTEGER PRIMARY KEY AUTO_INCREMENT, tarjous VARCHAR(255));
CREATE TABLE salasanat (salasana VARCHAR(16), taid INTEGER, thid INTEGER);

# lisätään pari työnantajaa
INSERT tyonantaja SET tarjous = "%A%B%";
INSERT tyonantaja SET tarjous = "%B%C%";
INSERT tyonantaja SET tarjous = "%A%C%";

# lisätään pari työntekijää
INSERT tyonhakija SET haluaa = "%A%B%C%";
INSERT tyonhakija SET haluaa = "%C%";
INSERT tyonhakija SET haluaa = "%A%";

# valitaan ne, jotka liittyvät toisiinsa ja joilla ei ole salasanoja
# (parempi tapa)
SELECT * FROM tyonantaja
INNER JOIN tyonhakija
  ON tyonantaja.tarjous LIKE tyonhakija.haluaa
LEFT JOIN salasanat
  ON salasanat.taid = tyonantaja.taid AND salasanat.thid = tyonhakija.thid
WHERE salasanat.salasana IS NULL;

#+------+---------+------+--------+----------+------+------+
#| taid | tarjous | thid | haluaa | salasana | taid | thid |
#+------+---------+------+--------+----------+------+------+
#|    2 | %B%C%   |    2 | %C%    | NULL     | NULL | NULL |
#|    3 | %A%C%   |    2 | %C%    | NULL     | NULL | NULL |
#|    1 | %A%B%   |    3 | %A%    | NULL     | NULL | NULL |
#|    3 | %A%C%   |    3 | %A%    | NULL     | NULL | NULL |
#+------+---------+------+--------+----------+------+------+

# lisätään salasana yhdelle parille
INSERT salasanat SET taid = 1, thid = 3, salasana = "koe";

# valitaan ne, jotka liittyvät toisiinsa ja joilla ei ole salasanoja
# (hitaampi tapa)
SELECT * FROM tyonantaja, tyonhakija
WHERE
  tyonantaja.tarjous LIKE tyonhakija.haluaa
  AND (
    SELECT COUNT(*) FROM salasanat
    WHERE taid = tyonantaja.taid AND thid = tyonhakija.thid
  ) = 0;

#+------+---------+------+--------+
#| taid | tarjous | thid | haluaa |
#+------+---------+------+--------+
#|    2 | %B%C%   |    2 | %C%    |
#|    3 | %A%C%   |    2 | %C%    |
#|    3 | %A%C%   |    3 | %A%    |
#+------+---------+------+--------+

# katsotaan vielä salasanat
SELECT * FROM salasanat;

#+----------+------+------+
#| salasana | taid | thid |
#+----------+------+------+
#| koe      |    1 |    3 |
#+----------+------+------+

# testikoodi, joten vedetään samalla vauhdilla taulut pois. :)
DROP TABLE salasanat;
DROP TABLE tyonhakija;
DROP TABLE tyonantaja;

Oh. Se toimii nyt. Kiitos paljon avusta. :)