Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 5 of 5

Thread: Join

  1. #1
    New to the CF scene
    Join Date
    Feb 2007
    Posts
    6
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Join

    Im in a bit of a muddle trying to join two tables. As an example I have the following two tables and need to join the second table onto 2 fields on the first table:

    main_dat:
    name, place1, place2
    Andy 1 2
    Mark 3 1

    place_nms:
    place, pname
    1 First
    2 Second
    3 Third

    I want to select all in the main_dat table and output:

    Andy First Second
    Mark Third First

    Can anyone point me in the right direction with this? Thanks.

  • #2
    Senior Coder
    Join Date
    Sep 2005
    Posts
    1,791
    Thanks
    5
    Thanked 36 Times in 35 Posts
    Code:
    SELECT md.name, pn1.name AS place1, pn2.name AS place2
    FROM main_dat md 
    JOIN place_nms pn1 ON (md.place1=pn1.place)
    JOIN place_nms pn2 ON (md.place2=pn2.place);
    Try that
    My thoughts on some things: http://codemeetsmusic.com
    And my scrapbook of cool things: http://gjones.tumblr.com

  • #3
    Super Moderator guelphdad's Avatar
    Join Date
    Mar 2006
    Location
    St. Catharines, Ontario Canada
    Posts
    2,633
    Thanks
    4
    Thanked 148 Times in 139 Posts
    Gjay, seeing as that person posted that question here as their first post, and it is pretty basic join, you should get them to post what they tried first.

    Not saying you were incorrect to do so, but it smells like homework to me. Don't want to have people show up with homework only never to contribute.

    Dave

  • #4
    New to the CF scene
    Join Date
    Feb 2007
    Posts
    6
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Thanks GJay. Worked a treat.

    guelphdad - I tried left join from the tutorials I looked at, but none of them went into any detail about how to do what GJay has just explained. I would love to contribute and help other people, but i think I am quite a way off that stage and would only make things worse for them with my current knowledge! Also I asked for pointers not the complete code, so I could try and work it out myself and not use up anyones time more than I need to. I wish my homework was like that when I was at school, but unfortuanely this is just for a personal project and my existing tables and fields would look very strange and complicated if I used them and not the plain example above.

    Thanks again Gjay.

  • #5
    Super Moderator guelphdad's Avatar
    Join Date
    Mar 2006
    Location
    St. Catharines, Ontario Canada
    Posts
    2,633
    Thanks
    4
    Thanked 148 Times in 139 Posts
    no problem. I just know people post homework, which there isn't a problem with when two things are done:

    1) mark it as homework

    2) say, here is some code I've tried, can you help me fix it

    my first inkling was that the above was homework.

    Hope you improve enough to come back and help.


  •  

    Posting Permissions

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