[RESOLVED] A Join, Or Union to select from two tables - using prepared statement

[we5inelgr]

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.

//**** 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:

//**** 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.

[traq]

we5inelgr;11034503 wrote:

SELECT Table1.Col2, Table1.Col3, Table1.Col4, Table1.Col5 (UNION SELECT Table2.Col6) COMBINED FROM Table1 WHERE Table1.Col1  = ?";

What is "[font=monospace]COMBINED[/font]"?

we5inelgr;11034503 wrote:

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... Basically, I'm trying to get the info from Col6 Table2 to be added to the 2 results from Table1.

A [font=monospace]JOIN[/font] is what you need. Since Col1 is common, use it to join the tables.

SELECT t1.Col2,t1.Col3,t1.Col4,t1.Col5,t2.Col6 
FROM Table1 t1 JOIN Table2 t2 USING( Col1  )
WHERE t1.Col1 = ?;

= = = = = = = = = = edit = = = = = = = = = =
using [font=monospace]USING[/font] only works if Col1 really does have the same name in both tables. If not, you'll have to use an [font=monospace]ON[/font] expression:
[font=monospace]FROM Table1 t1 JOIN Table2 t2 ON t1.Col1 = t2.Col1[/font]

[we5inelgr]

Thanks for the reply!

It appears that I've asked the question incorrectly, because when I try your suggestion, I get 8 results. It looks like the query is returning the 2 rows from table one, and each of the 4 rows from table2 that have Col1 & Col2 in common (with table 1) and then multiplying them.

2x4

Each result from table1 is repeated, and each of the results from table2 are repeated. so like

result1 table1 result1 table2
result1 table1 result2 table2
result1 table1 result3 table2
result1 table1 result4 table2

result2 table1 result1 table2
result2 table1 result2 table2
result2 table1 result3 table2
result2 table1 result4 table4

The expected results should be:

result1 table1 result1 table2
result2 table1 result1 table2

Perhaps this is explained better:

//**** 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].

[traq]

we5inelgr;11034507 wrote:

Thanks for the reply!

It appears that I've asked the question incorrectly, because when I try your suggestion, I get 8 results. It looks like the query is returning the 2 rows from table one, and each of the 4 rows from table2 that have Col1 & Col2 in common (with table 1) and then multiplying them.

2x4

Each result from table1 is repeated, and each of the results from table2 are repeated. so like

result1 table1 result1 table2
result1 table1 result2 table2
result1 table1 result3 table2
result1 table1 result4 table2

result2 table1 result1 table2
result2 table1 result2 table2
result2 table1 result3 table2
result2 table1 result4 table4

The expected results should be:

result1 table1 result1 table2
result2 table1 result1 table2

Dunno. works for me. Can you share the actual statement you're using?

Also,

we5inelgr;11034507 wrote:

SELECT Table2.Col6 FROM Table2 WHERE Col2 = $var2;

Are you trying to join on Col1 or Col2?

[we5inelgr]

Citrus Table (the table with only 2 recs, these 2 recs should be the only 2 results + the 1 column from the Meat_Table).
Group_ID, Group_ID, Limes, Oranges, Grapefruit, Lemons

Meat_Table
Group_ID, Group_ID, Steak, Ham, Chicken, Fish

//**** Build the main query statement. ****//
$qSTMT = "SELECT t1.Limes, t1.Oranges, t1.Grapefruit, t1.Lemons, t2.Steak FROM Citrus_Table t1 JOIN Meat_Table t2 USING (Group_ID) WHERE t1.Product_ID  = ?";

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

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

$totalRecs = 0;

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

    $limes = $row ['Limes'];  //**** From Citrus_Table ****//
    $oranges = $row ['Oranges']; //**** From Citrus_Table ****//
    $grapefruit = $row ['Grapefruit']; //**** From Citrus_Table ****//
    $lemons = $row ['Lemons']; //**** From Citrus_Table ****//
    $steak = $row ['Steak']; //**** Steak from Meat table. ****//
    $totalRecs++;
    echo "Limes: ".$limes.", & Oranges: ".$oranges.", & Steak: ".$steak."<br>"; //**** echo testing ****//
} //**** End while results. ****//

echo "<p>Total Recs: ".$totalRecs."<br>"; //**** testing ****//

The echo'd results are:
Limes: Key Limes, & Oranges: Clementines, & Steak: Porterhouse
Limes: Key Limes, & Oranges: Clementines, & Steak: T-Bone
Limes: Key Limes, & Oranges: Clementines, & Steak: Filet
Limes: Key Limes, & Oranges: Clementines, & Steak: New York

Limes: Tahitian Limes, & Oranges: Tangerines, & Steak: Porterhouse
Limes: Tahitian Limes, & Oranges: Tangerines, & Steak: T-Bone
Limes: Tahitian Limes, & Oranges: Tangerines, & Steak: Filet
Limes: Tahitian Limes, & Oranges: Tangerines, & Steak: New York

The echo'd results should be like:

Limes: Key Limes, & Oranges: Clementines, & Steak: Porterhouse
Limes: Tahitian Limes, & Oranges: Tangerines, & Steak: Porterhouse

[traq]

One thing that is obviously different is that you're joining on one column, and filtering on another (whereas, when you initially described it, you were using the same column for both). Beyond that, I couldn't say. Perhaps you could create your own SQL fiddle?

[we5inelgr]

Yeah, I totally botched the question. Guess I not only needed help with the solution, but how to phrase the question too. haha.

Turns out, this is what I needed (it works as expected):

SELECT Table1.Col1
     , Table1.Col2
     , Table1.Col3
     , Table1.Col4
     , Table1.Col5
     , Table2.Col6
  FROM Table1 
INNER
  JOIN Table2
    ON Table2.Col1 = Table1.Col1

Thank you for your help, and for suggesting sqlfiddle (didn't know about that tool). 🙂

[traq]

cool, glad you figured it out