homepage Welcome to WebmasterWorld Guest from 54.161.191.154
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Become a Pro Member

Home / Forums Index / Code, Content, and Presentation / Databases
Forum Library, Charter, Moderator: open

Databases Forum

    
JOIN does not outputs data from only one table
jj23



 
Msg#: 4316517 posted 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?

 

redhatlab

5+ Year Member



 
Msg#: 4316517 posted 3:19 am on May 30, 2011 (gmt 0)

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.

rocknbil

WebmasterWorld Senior Member rocknbil us a WebmasterWorld Top Contributor of All Time 5+ Year Member



 
Msg#: 4316517 posted 5:46 pm on May 30, 2011 (gmt 0)

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.

jj23



 
Msg#: 4316517 posted 12:13 pm on May 31, 2011 (gmt 0)

Thanks for the help, I will try your suggestions!

Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Code, Content, and Presentation / Databases
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved