SitePoint Sponsor

User Tag List

Results 1 to 19 of 19
  1. #1
    SitePoint Member
    Join Date
    Jul 2011
    Posts
    9
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Mysql left join help

    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

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,220
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    should be real easy

    would you please do a SHOW CREATE TABLE for each table
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Member
    Join Date
    Jul 2011
    Posts
    9
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,220
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    Code:
    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
    Code:
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Member
    Join Date
    Jul 2011
    Posts
    9
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,220
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Member
    Join Date
    Jul 2011
    Posts
    9
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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!

  8. #8
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,220
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by vaielab View Post
    ...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

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  9. #9
    SitePoint Member
    Join Date
    Jul 2011
    Posts
    9
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    No problem, but I think this time I've been able to describe the situation pretty well.
    Thank again!

  10. #10
    SitePoint Member
    Join Date
    Jul 2011
    Posts
    9
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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!

  11. #11
    SitePoint Wizard guelphdad's Avatar
    Join Date
    Oct 2003
    Location
    St. Catharines, ON Canada
    Posts
    1,707
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    ask clear questions get clearer answers.

  12. #12
    SitePoint Member
    Join Date
    Jul 2011
    Posts
    9
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I tryed, but sometime it's hard when we ask in a other langage

  13. #13
    SitePoint Wizard guelphdad's Avatar
    Join Date
    Oct 2003
    Location
    St. Catharines, ON Canada
    Posts
    1,707
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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

  14. #14
    SitePoint Member
    Join Date
    Jul 2011
    Posts
    9
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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

  15. #15
    SitePoint Wizard guelphdad's Avatar
    Join Date
    Oct 2003
    Location
    St. Catharines, ON Canada
    Posts
    1,707
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.

  16. #16
    SitePoint Member
    Join Date
    Jul 2011
    Posts
    9
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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!

  17. #17
    SitePoint Member
    Join Date
    Mar 2011
    Location
    Tyne & Wear
    Posts
    20
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

  18. #18
    SitePoint Wizard guelphdad's Avatar
    Join Date
    Oct 2003
    Location
    St. Catharines, ON Canada
    Posts
    1,707
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.

  19. #19
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,220
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    Code:
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •