#1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2012
    Posts
    150
    Rep Power
    3

    Question A Join, Or Union Or other to select from two tables - using prepared statement


    Hi all,

    I've got two tables, which have "Col1" in common.

    Table1
    Col1, Col2, Col3, Col4, Col5

    Table2
    Col1, Col6

    I'm trying to extract Col's 2 thru 5 from the first table, as well as Col 6 from Table2.

    Table1 has 2 records in it.

    Table2 has many.

    My expected results should be the 2 records from Table1 with the selected columns along with the Col6 data (for those two Table1 records) from Table2. Basically, I'm trying to get the info from Col6 Table2 to be added to the 2 results from Table1.

    PHP Code:
    //**** Build the query statement. ****//
    $qSTMT "SELECT Table1.Col2, Table1.Col3, Table1.Col4, Table1.Col5 (UNION SELECT Table2.Col6) COMBINED FROM Table1 WHERE Table1.Col1  = ?";

    //**** Prepare the main statement. ****//
    $qSTMT $connection->prepare($qSTMT);

    //**** Now, run the main query. ****//
    $qSTMT->execute(array( $var1 ));


     while(
    $row $qSTMT->fetch()) { 

    .... 
    No records are found.

    I've also tried:

    PHP Code:
    //**** Build the query statement. ****//
    $qSTMT "SELECT Table1.Col2, Table1.Col3, Table1.Col4, Table1.Col5, Table2.Col6 FROM Table1, Table2 WHERE Table1.Col1  = ? AND Table2.Col1 = ?";

    //**** Prepare the main statement. ****//
    $qSTMT $connection->prepare($qSTMT);

    //**** Now, run the main query. ****//
    $qSTMT->execute(array( $var1$var1 ));


     while(
    $row $qSTMT->fetch()) { 

    .... 
    This results in 8 records.

    Should I be using a Join, or Union to grab that Col6 Table2 and integrate it with the results I'd expect from Table1?

    Thanks.
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2012
    Posts
    150
    Rep Power
    3
    The above is not explained quite correctly. This (below) is what I'm trying to do

    PHP Code:
    //**** Build the query statement. ****//
    $qSTMT "SELECT Table1.Col2, Table1.Col3, Table1.Col4, Table1.Col5 FROM Table1 WHERE Table1.Col1  = ?";

    //**** Prepare the statement. ****//
    $qSTMT $connection->prepare($qSTMT);

    //**** Run the query. ****//
    $qSTMT->execute(array( $var1 ));


    while(
    $row $qSTMT->fetch()) {   
        
    $var2 $row ['Col2'];
        
    $var3 $row ['Col3'];
        
    $var4 $row ['Col4'];
        
    $var5 $row ['Col5'];
        
        
    SELECT Table2.Col6 FROM Table2 WHERE Col2 $var2//Pseudo Code
        
        
    while results //Pseudo Code
        
    $var6 result Table2.Col6 //Pseudo Code
        
    //**** End while results. ****//

    //**** Reults has $var2, $var3, $var4, $var5, (from table 1) and $var6 (from table 2) 
    The above will do what I'm looking for, but clearly isn't the best way to do this. I know I said Col1 (only) is shared, but actually Col2 is shared as well. Sorry for any confusion.

    How would I incorporate the above pseudo code into the Join?

    [And yes, Col1 and Col2 are named the same in both tables].
  4. #3
  5. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,172
    Rep Power
    4274
    Originally Posted by we5inelgr
    Basically, I'm trying to get the info from Col6 Table2 to be added to the 2 results from Table1.
    that's a simple inner join
    Code:
    SELECT Table1.Col1
         , Table1.Col2
         , Table1.Col3
         , Table1.Col4
         , Table1.Col5
         , Table2.Col6
      FROM Table1 
    INNER
      JOIN Table2
        ON Table2.Col1 = Table1.Col1
    if Table1 has only 2 rows, i don't understand why you want to make a prepared statement out of this

    (disclaimer: i don't do php)

    if this is really more of a php question, let me know, i can move this thread to the php forum if you wish

    Comments on this post

    • we5inelgr agrees : Excellent suggestion, spot on solution! Thanks!
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  6. #4
  7. No Profile Picture
    Contributing User
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Mar 2006
    Posts
    2,447
    Rep Power
    1751
    If I am following you:

    ... just do what r937 has posted!

    Comments on this post

    • we5inelgr agrees : Yes, r937's suggestion was what I needed. Thank you, also for taking the time to look and respond!
    The moon on the one hand, the dawn on the other:
    The moon is my sister, the dawn is my brother.
    The moon on my left and the dawn on my right.
    My brother, good morning: my sister, good night.
    -- Hilaire Belloc
  8. #5
  9. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2012
    Posts
    150
    Rep Power
    3

    Thumbs up


    Originally Posted by r937
    that's a simple inner join
    Code:
    SELECT Table1.Col1
         , Table1.Col2
         , Table1.Col3
         , Table1.Col4
         , Table1.Col5
         , Table2.Col6
      FROM Table1 
    INNER
      JOIN Table2
        ON Table2.Col1 = Table1.Col1
    if Table1 has only 2 rows, i don't understand why you want to make a prepared statement out of this

    (disclaimer: i don't do php)

    if this is really more of a php question, let me know, i can move this thread to the php forum if you wish
    AWESOME! Many, many thanks. This is exactly what I was trying to do.

    As for the table with only 2 recs, it's only testing data in a development environment. The production table has many more recs than that.

    Thanks again!

IMN logo majestic logo threadwatch logo seochat tools logo