Welcome to WebmasterWorld Guest from 54.145.166.247

Forum Moderators: open

Message Too Old, No Replies

JOIN does not outputs data from only one table

   
9:11 pm on May 23, 2011 (gmt 0)



I'm trying to pull data from two tables, Details and Variables. The SQL query below gives me data from Details, but nothing from Variables when I loop throught the results array.


$query = "SELECT Details.Description, Details.DetailValue, Variables.VariableName, Variables.Value
FROM Details
INNER JOIN SavedConfigurations
ON Details.OrderID=SavedConfigurations.OrderID
AND SavedConfigurations.Name='TEST123'

INNER JOIN Variables
ON Variables.OrderID = SavedConfigurations.OrderID
WHERE Variables.VariableName = 'URL1'


";

Can anyone please offer suggestions?
3:19 am on May 30, 2011 (gmt 0)

5+ Year Member



Just a note: you are connected to 3 tables

Have you try LEFT or RIGHT OUTTER JOIN? If I remember correctly the inner join will fail if any of the table don't have a value for the joining key.
5:46 pm on May 30, 2011 (gmt 0)

WebmasterWorld Senior Member rocknbil is a WebmasterWorld Top Contributor of All Time 10+ Year Member



It means there are no columns that match the criteria . . . when fuzzled with joins, break down the statements. Output the joining column and do a straight select . . .


SELECT Details.OrderID, Details.Description, Details.DetailValue Details

Now pick one of the order ID's and compare it with straight selects on the other two tables.

SELECT SavedConfigurations.OrderID from SavedConfigurations where SavedConfigurations.OrderID = 'one-of-the-order-ids' AND SavedConfigurations.Name='TEST123'

"SELECT Variables.VariableName, Variables.Value
FROM Variables where Variables.OrderID = 'one-of-the-order-ids' and Variables.VariableName = 'URL1'

You'll probably find an empty set for at least one of the two joins.
12:13 pm on May 31, 2011 (gmt 0)



Thanks for the help, I will try your suggestions!