how is it possible to left outer join 2 tables?
class Person {
String firstName
String lastName
String gender
//static hasMany = [votes: Vote]
static mapping = {
version false
}
static constrains = {
}
}
class Vote {
Person voter;
Person subject;
static mapping = {
version false
}
static constraints = {
voter nullable: false
subject nullable: false
}
}
i need to get every person thats not subjected to a vote, for a specific person. lets say person 1 votes for 3 out of 5 persons, i need the other 2 that he didnt vote for to show up for him. How is the query supposed to be?
EDIT:
def personInstance1 = new Person(firstName: "Antonio", lastName: "Vivaldi", gender: "m")
def personInstance2 = new Person(firstName: "Dennis", lastName: "Rodman", gender: "m")
def personInstance3 = new Person(firstName: "Marc", lastName: "Oh", gender: "m")
def personInstance4 = new Person(firstName: "Gudrun", lastName: "Graublume", gender: "w")
def personInstance5 = new Person(firstName: "Hilde", lastName: "Feuerhorn", gender: "w")
def personInstance6 = new Person(firstName: "Mandy", lastName: "Muller", gender: "w")
personInstance1.save()
personInstance2.save()
personInstance3.save()
personInstance4.save()
personInstance5.save()
personInstance6.save()
def voteInstance1 = new Vote(voter: personInstance1, subject: personInstance2)
def voteInstance2 = new Vote(voter: personInstance1, subject: personInstance3)
def voteInstance3 = new Vote(voter: personInstance1, subject: personInstance4)
def voteInstance4 = new Vote(voter: personInstance1, subject: personInstance5)
def voteInstance5 = new Vote(voter: personInstance2, subject: personInstance1)
voteInstance1.save()
voteInstance2.save()
voteInstance3.save()
voteInstance4.save()
voteInstance5.save()
this is my grails bootstrap-file , Antonio and Dennis have voted, and each need to be presented a list of people they didnt vote for.
EDIT: this way i seem to get a result for Dennis, since he only voted once, but if i put v.voter_id = 1, to get a result for Antonio, the result doubles according to how many votes he did.
SELECT first_name FROM vote as v
LEFT OUTER JOIN person as p
ON v.subject_id != p.id AND v.voter_id = 2
WHERE p.id IS NOT NULL
Try this:
SELECT * FROM Person P
WHERE NOT EXISTS(
SELECT 'Vote' FROM Vote V
WHERE V.subject = P
)
In this way you'll extract all Person without Vote
EDIT
In SQL you can retrieve a matrix in this way:
CREATE TABLE #person (nome varchar(30))
CREATE TABLE #vote (votante varchar(30), candidato varchar(30))
INSERT INTO #person values
('Antonio Vivaldi'),
('Dennis Rodman'),
('Marc Oh'),
('Gudrun Graublume'),
('Hilde Feuerhorn'),
('Mandy Muller')
INSERT INTO #vote values
('Antonio Vivaldi', 'Dennis Rodman'),
('Antonio Vivaldi', 'Marc Oh'),
('Antonio Vivaldi', 'Gudrun Graublume'),
('Antonio Vivaldi', 'Hilde Feuerhorn'),
('Dennis Rodman', 'Antonio Vivaldi')
SELECT *
FROM #person p
CROSS JOIN #person c
WHERE NOT EXISTS(
SELECT 'X'
FROM #vote v
WHERE v.votante = p.nome
AND v.candidato = c.nome
)
AND p.nome <> c.nome
ORDER BY p.nome
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments