Mysql left join help

[TUFKAV]

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

[r937]

should be real easy

would you please do a SHOW CREATE TABLE for each table

[TUFKAV]

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

[r937]

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
, PRIMARY KEY ( id_Chambre , id_Resident )
, INDEX reversi ( id_Resident , id_Chambre )
, 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

[TUFKAV]

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

[r937]

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

[TUFKAV]

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!

[r937]

...only the one with the biggest date in "dateDepart_ResidentChambre" only if the date is bigger than now.

this is getting messier and messier, isn't it

i think i'll wait a day to see if there are any more changes

[TUFKAV]

No problem, but I think this time I've been able to describe the situation pretty well.
Thank again!

[TUFKAV]

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!

[guelphdad]

ask clear questions get clearer answers.

[TUFKAV]

I tryed, but sometime it's hard when we ask in a other langage

[guelphdad]

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

[TUFKAV]

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

[guelphdad]

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.

[TUFKAV]

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!

[DMWDave]

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).

[guelphdad]

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.

[r937]

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 ( SELECT id_chambre
              , MAX(dateDepart_ResidentChambre) AS latest
           FROM residentchambre
          WHERE dateDepart_ResidentChambre > CURRENT_TIMESTAMP
         GROUP
             BY id_chambre ) AS max1
    ON max1.id_chambre = chambre.id_chambre
LEFT OUTER
  JOIN residentchambre
    ON residentchambre.id_chambre = chambre.id_chambre
   AND residentchambre.datedepart_residentchambre = max1.latest
LEFT OUTER
  JOIN resident
    ON resident.id_resident = residentchambre.id_resident