SitePoint Sponsor

User Tag List

Results 1 to 6 of 6
  1. #1
    SitePoint Guru afridy's Avatar
    Join Date
    Mar 2007
    Posts
    960
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Pulling data from identical tables

    Hai folks,

    we are recruiting candidates for 3 projects.
    project a , project b and project c

    candidats are put on 3 tables for the projects they were hired

    candidates_project_a
    candidates_project_b
    candidates_project_c

    i want to list all the candidats from 3 tables on the screen. we have total 400 candidates.

    i have the flowing query

    Code:
    	$query="SELECT candidates_project_a.*, candidates_project_b.*, candidates_project_c.*
                From candidates_project_a, candidates_project_b, candidates_project_c
    now this displays 14000+ records while i only have 400

    what could be wrong?

  2. #2
    Programming Since 1978 silver trophybronze trophy felgall's Avatar
    Join Date
    Sep 2005
    Location
    Sydney, NSW, Australia
    Posts
    16,598
    Mentioned
    24 Post(s)
    Tagged
    1 Thread(s)
    When you join tables together like that every record in the first table is combined with every record in the second table and those entries are then combined with every record from the third table giving you potentially millions of results.

    What you need to get the results you are after is to UNION the results of three SELECTs each of which gets the records from only one table.
    Stephen J Chapman

    javascriptexample.net, Book Reviews, follow me on Twitter
    HTML Help, CSS Help, JavaScript Help, PHP/mySQL Help, blog
    <input name="html5" type="text" required pattern="^$">

  3. #3
    SitePoint Guru afridy's Avatar
    Join Date
    Mar 2007
    Posts
    960
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by felgall View Post
    When you join tables together like that every record in the first table is combined with every record in the second table and those entries are then combined with every record from the third table giving you potentially millions of results.

    What you need to get the results you are after is to UNION the results of three SELECTs each of which gets the records from only one table.
    Thanks felgall,
    i am new to this UNION.
    let me read some examples on how to do this

  4. #4
    SitePoint Guru afridy's Avatar
    Join Date
    Mar 2007
    Posts
    960
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by felgall View Post
    When you join tables together like that every record in the first table is combined with every record in the second table and those entries are then combined with every record from the third table giving you potentially millions of results.

    What you need to get the results you are after is to UNION the results of three SELECTs each of which gets the records from only one table.
    worked charm felgall!
    Thanks so much for your valuable time!!!

    Code:
    	$query="SELECT * FROM candidates_project_a
    	             UNION
    		     SELECT * FROM candidates_project_b
    		     UNION 
    		     SELECT * FROM candidates_project_c";

  5. #5
    Utopia, Inc. silver trophy
    ScallioXTX's Avatar
    Join Date
    Aug 2008
    Location
    The Netherlands
    Posts
    8,892
    Mentioned
    138 Post(s)
    Tagged
    2 Thread(s)
    Even better would be to create a single projects table that describes all the projects and then a separate candidates table that points back to this projects table. That way you can query just the candidates table with a WHERE clause specifying which projects you want and it allows you application to scale easily. i.e., you don't need to create a table for a new project anymore.
    Rémon - Hosting Advisor

    Minimal Bookmarks Tree
    My Google Chrome extension: browsing bookmarks made easy

  6. #6
    SitePoint Guru afridy's Avatar
    Join Date
    Mar 2007
    Posts
    960
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by ScallioXTX View Post
    Even better would be to create a single projects table that describes all the projects and then a separate candidates table that points back to this projects table. That way you can query just the candidates table with a WHERE clause specifying which projects you want and it allows you application to scale easily. i.e., you don't need to create a table for a new project anymore.
    Thanks Scallio, undestood!!


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
  •