Hello,
I have a problem in mysql that is quite difficult to explain, but I hope you will understand me… even more hard since I don’t normally speak english
So I have 3 tables: room, people and a intersection table called roomPeople
Room & people contain an id, and roomPeople contain the id of the 2 others rooms plus the date they left
I want to be able to select all rooms, and if the date is not expired the person in the room at this moment
I hope you can understand what I just said
Thx
should be real easy
would you please do a SHOW CREATE TABLE for each table
The database is in french
This is the People table
CREATE TABLE Resident (
id_Resident int(11) NOT NULL AUTO_INCREMENT,
nom_Resident varchar(200) NOT NULL,
prenom_Resident varchar(200) NOT NULL,
photo_Resident varchar(20) DEFAULT NULL,
dateNaissance_Resident date NOT NULL,
codeIncendie_Resident int(11) NOT NULL,
PRIMARY KEY (id_Resident)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8
The room table
CREATE TABLE Chambre (
id_Chambre int(11) NOT NULL AUTO_INCREMENT,
id_Departement int(11) NOT NULL,
nom_Chambre varchar(50) NOT NULL,
description_Chambre text NOT NULL,
prix_Chambre int(11) NOT NULL,
actif_Chambre tinyint(1) NOT NULL,
superficie_Chambre int(11) NOT NULL,
plan_Chambre varchar(20) DEFAULT NULL,
position_Chambre varchar(20) DEFAULT NULL,
PRIMARY KEY (id_Chambre),
KEY id_Departement (id_Departement),
CONSTRAINT Chambre_ibfk_1 FOREIGN KEY (id_Departement) REFERENCES Departement (id_Departement)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8
The intersection table between people & room
CREATE TABLE ResidentChambre (
id_ResidentChambre int(11) NOT NULL AUTO_INCREMENT,
dateArrive_ResidentChambre date NOT NULL,
dateDepart_ResidentChambre date DEFAULT NULL,
prix_ResidentChambre int(11) NOT NULL,
bail_ResidentChambre varchar(20) DEFAULT NULL,
dateFinBail_ResidentChambre date DEFAULT NULL,
id_Chambre int(11) NOT NULL,
id_Resident int(11) NOT NULL,
PRIMARY KEY (id_ResidentChambre),
KEY id_Resident (id_Resident),
KEY id_Chambre (id_Chambre),
CONSTRAINT ResidentChambre_ibfk_2 FOREIGN KEY (id_Chambre) REFERENCES Chambre (id_Chambre),
CONSTRAINT ResidentChambre_ibfk_1 FOREIGN KEY (id_Resident) REFERENCES Resident (id_Resident)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8
The date that is important is dateDepart_ResidentChambre
And one more catch, if it’s null, it’s the one that got to get retrieved
SELECT chambre.id_chambre
, chambre.autres_colonnes_du_chambre
, residentchambre.datearrive_residentchambre
, resident.id_resident
, resident.autres_colonnes_du_resident
FROM chambre
INNER
JOIN residentchambre
ON residentchambre.id_chambre = chambre.id_chambre
AND residentchambre.datedepart_residentchambre IS NULL
INNER
JOIN resident
ON resident.id_resident = residentchambre.id_resident
note you want inner joins, not left outer 
also, the auto_increment in the relationship table should be dropped – it is useless
CREATE TABLE ResidentChambre
( id_Chambre INTEGER NOT NULL
, id_Resident INTEGER NOT NULL
, [COLOR="Blue"]PRIMARY KEY ( id_Chambre , id_Resident )[/COLOR]
, [COLOR="blue"]INDEX reversi ( id_Resident , id_Chambre )[/COLOR]
, dateArrive_ResidentChambre DATE NOT NULL
, dateDepart_ResidentChambre DATE DEFAULT NULL
, prix_ResidentChambre INTEGER NOT NULL
, bail_ResidentChambre VARCHAR(20) DEFAULT NULL
, dateFinBail_ResidentChambre DATE DEFAULT NULL
, CONSTRAINT ResidentChambre_ibfk_2 FOREIGN KEY (id_Chambre) REFERENCES Chambre (id_Chambre)
, CONSTRAINT ResidentChambre_ibfk_1 FOREIGN KEY (id_Resident) REFERENCES Resident (id_Resident)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8
Thank you very much for this fast answer!
But is there a way to retrieve a room even if their no one in it?
Thank you again
empty rooms as well? okay, now i can see why you wanted LEFT OUTER JOINs
change the joins back, and then move the IS NULL condition out of the ON clause and into the WHERE clause
Thank again and Sorry I ask a lot, and I know i’m not very clear
But I want that if their multiple “ResidentChambre” with the same “id_Chambre” that I retrieve only the one with the biggest date in “dateDepart_ResidentChambre” only if the date is bigger than now. Otherwise retrieve the one with the “dateDepart_ResidentChambre” that is null, and if their no null, only retrieve “Chambre” (the room) like a simple left join
In other word, dateDepart_Resident is the date where they leave. If dateDepart_Resident if null, it’s because they are still here and don’t know when their gonna leave.
And I wish to list all room (Chambre) with the person (Resident) in it, if their someone in it
I hope you understood me, I hate to make you work for nothing 
Thank again!
this is getting messier and messier, isn’t it
i think i’ll wait a day to see if there are any more changes
No problem, but I think this time I’ve been able to describe the situation pretty well.
Thank again!
Well I find a way…
I use 2 different sql query, and a lots of php to validate if it’s null, or the date is smaller than now or any other conditions…
It’s not sexy, and probably slower, but I don’t have a lot of data
Thank you for your help!
ask clear questions get clearer answers.
I tryed, but sometime it’s hard when we ask in a other langage
Good steps, irrespective of your language
show your tables
only show relevant columns
provide INSERT statements for your tables
provide a result set of what you expect the query to produce
Don’t change what you are asking for, ask what you need in your first post
Sorry, I might be new on this forum, and maybe it’s only a language barriers, but personally when I help someone, I help them… I don’t write philosophical quote than don’t help at all except maybe showing them that i’m superior to them. And this is even more the case when I didn’t even help them.
If you look at my first post, you will see that I never changed my question, this is and has always been my question, the only difference is that we didn’t understand each other respective to my language.
I didn’t show my table at first since it was in a other language and it was more likely to confuse them… once again respective to my language
BUt I did describe them and told about the relavant columns
My moms always say that if I didn’t have anything construbtive to say, I should say anything… actually she never said that, but I thought it would fit perfectly here
And one more catch, if it’s null, it’s the one that got to get retrieved
That isn’t in your original post so yes your paramaters did change.
I merely pointed out relevant steps that you should include (including table/column names in French is fine if that is how they are created).
As a regular forum member I’m only trying to assist you in getting a clear answer for the next time you post. You are a new member here and may post in other forums as well where clear guidelines will help you.
As a regular forum member I’m only trying…
Indeed, you really like show that you are superior to others, good for you!
Just to tell you how much superior you are, I had a great time here receiving help really fast from someone who knew what he was talking about… but lucky me, you, superior man, come and only point every single flaw that I have… Btw i’m a little bit shorter than most people, you could use that next time you want to show a flaw, it’s gonna be just like high school, it’s gonna be awesome!
:goof:
Whats all that about then? He was only saying get your point across clear, You changed your requirements mid-way through this thread, so you have left people wondering if you really have sat down and planned out your application before attacking it with code (A very common mistake).
Save yourself some trouble, grow a thicker skin before you post in public forums.
I was merely offering thoughts on how to post in order to get a quick answer.
I’m sure this isn’t the only question you will ever have and I was suggesting ways for you to post in the future so you would get a concise answer and quickly.
SELECT chambre.id_chambre
, chambre.autres_colonnes_du_chambre
, residentchambre.datearrive_residentchambre
, resident.id_resident
, resident.autres_colonnes_du_resident
FROM chambre
LEFT OUTER
JOIN [COLOR="blue"]( SELECT id_chambre
, MAX(dateDepart_ResidentChambre) AS latest
FROM residentchambre
WHERE dateDepart_ResidentChambre > CURRENT_TIMESTAMP
GROUP
BY id_chambre ) AS max1[/COLOR]
ON max1.id_chambre = chambre.id_chambre
LEFT OUTER
JOIN residentchambre
ON residentchambre.id_chambre = chambre.id_chambre
[COLOR="Blue"]AND residentchambre.datedepart_residentchambre = max1.latest[/COLOR]
LEFT OUTER
JOIN resident
ON resident.id_resident = residentchambre.id_resident