SitePoint Sponsor

User Tag List

Results 1 to 9 of 9
  1. #1
    SitePoint Wizard
    Join Date
    May 2012
    Posts
    1,004
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Post Need help for my tables

    Hi, I just want to ask because i am confuse with my three tables,..my question is..Is it possible to use the join query like the inner join,left join,outer join.,etc...in the 3 tables, as i know that when we use the inner join it must be the pk and fk,example:..

    Code:
    select e.emp_no, e.f_name,e.l_name,p.proj_no,p.res_empno
    from employee e join project p
    on e.emp_no = p.res_empno
    the emp_no is pk for the employee table and the res_empno is the fk for the project table.

    but what if the design of my table is something like this

    table1
    std_idno = pk
    std_fname
    std_lname
    std_classno

    table2
    rm_stdno = pk
    rm_stdclassno
    rm_size
    rm_description

    I want that if the student has the same classno in the table2 i want to display the rm_size and the rm_description,student name and idno.

    Is it possible to use the join query on this,can i populate the exact data?please help me on this I am really confuse....


    I am using wamp and i create the database in the phpmyadmin.


    Thank you in advance

  2. #2
    Hosting Team Leader silver trophybronze trophy
    cpradio's Avatar
    Join Date
    Jun 2002
    Location
    Ohio
    Posts
    4,813
    Mentioned
    141 Post(s)
    Tagged
    0 Thread(s)
    First off, here is a brief definition of each join
    http://www.firebirdfaq.org/faq93/

    Second, based on what you are asking, you want to show students and their associated class so long as that class exists in table2.

    So I believe you could theoretically use any of the joins for this scenario, but it seem inner join may be best suited.

    Code:
    SELECT rm_size, rm_description, std_fname, std_lname, std_idno FROM table1 AS Student INNER JOIN table2 AS Room ON Student.std_classno = Room.rm_stdclassno
    Be sure to congratulate xMog on earning April's Member of the Month
    Go ahead and blame me, I still won't lose any sleep over it
    My Blog | My Technical Notes

  3. #3
    SitePoint Wizard
    Join Date
    May 2012
    Posts
    1,004
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by cpradio View Post
    First off, here is a brief definition of each join
    http://www.firebirdfaq.org/faq93/

    Second, based on what you are asking, you want to show students and their associated class so long as that class exists in table2.

    So I believe you could theoretically use any of the joins for this scenario, but it seem inner join may be best suited.

    Code:
    SELECT rm_size, rm_description, std_fname, std_lname, std_idno FROM table1 AS Student INNER JOIN table2 AS Room ON Student.std_classno = Room.rm_stdclassno

    Hi cpradio, Thank you so much for the reply,,....I forgot to put that the Student.std_classno and Room.rm_stdclassno have different data types and length associated...

  4. #4
    Hosting Team Leader silver trophybronze trophy
    cpradio's Avatar
    Join Date
    Jun 2002
    Location
    Ohio
    Posts
    4,813
    Mentioned
    141 Post(s)
    Tagged
    0 Thread(s)
    Okay, but do the values match up or what would you need to do to make the columns match up?

    Can you give sample data stored in both tables and then the desired returned result you want to see?
    Be sure to congratulate xMog on earning April's Member of the Month
    Go ahead and blame me, I still won't lose any sleep over it
    My Blog | My Technical Notes

  5. #5
    SitePoint Wizard
    Join Date
    May 2012
    Posts
    1,004
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by cpradio View Post
    Okay, but do the values match up or what would you need to do to make the columns match up?

    Can you give sample data stored in both tables and then the desired returned result you want to see?
    Hi cpradio,..I apologize for my last reply,I mean to say that Student.std_classno and Room.rm_stdclassno is not a foreign key and also it is not a primary key...but they both having the same values and datatypes...my question is this,is it okay to use inner join even they are not pK and Fk?can i still get the right data?.

  6. #6
    Hosting Team Leader silver trophybronze trophy
    cpradio's Avatar
    Join Date
    Jun 2002
    Location
    Ohio
    Posts
    4,813
    Mentioned
    141 Post(s)
    Tagged
    0 Thread(s)
    Yes, you can still get the right data. It would be nice if they had some sort of relationship to verify data integrity, but that is the only issue the fk relationship would resolve. It would prevent both tables from having invalid data.
    Be sure to congratulate xMog on earning April's Member of the Month
    Go ahead and blame me, I still won't lose any sleep over it
    My Blog | My Technical Notes

  7. #7
    SitePoint Wizard
    Join Date
    May 2012
    Posts
    1,004
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by cpradio View Post
    Yes, you can still get the right data. It would be nice if they had some sort of relationship to verify data integrity, but that is the only issue the fk relationship would resolve. It would prevent both tables from having invalid data.
    Hi thank you for this, but for example the table is already created how can i know that this table have FK relationship to other tables ?by the way i do this in phpmyadmin.

  8. #8
    Hosting Team Leader silver trophybronze trophy
    cpradio's Avatar
    Join Date
    Jun 2002
    Location
    Ohio
    Posts
    4,813
    Mentioned
    141 Post(s)
    Tagged
    0 Thread(s)
    I don't use phpmyadmin a lot, but maybe this video will be helpful
    http://www.youtube.com/watch?v=IdQGFZwP7Xc
    Be sure to congratulate xMog on earning April's Member of the Month
    Go ahead and blame me, I still won't lose any sleep over it
    My Blog | My Technical Notes

  9. #9
    SitePoint Wizard
    Join Date
    May 2012
    Posts
    1,004
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by cpradio View Post
    I don't use phpmyadmin a lot, but maybe this video will be helpful
    http://www.youtube.com/watch?v=IdQGFZwP7Xc
    Hi, cprdio thank you for helping me, Okay i will watch this video.and i will write back to you again if i get in trouble.


    More power to you always.


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
  •