Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
Author |
Topic |
Sprout Coder
Starting Member
2 Posts |
Posted - 2013-06-13 : 14:19:52
|
The following QUERY #1 returns the correct results when value "1010" is used:SELECT * FROM iterations $iterations2, experiments $experiments1, clients $clients1WHERE $experiments1.id = $iterations2.experiment_id AND $clients1.property_id = $experiments1.property_id AND $experiments1.id = "1010"now the following QUERY #2 returns the value "1010" that the previous Query was using:SELECT $iterations1.experiment_idFROM iterations $iterations1WHERE $iterations1.id IN (SELECT $variations1.iteration_id FROM variations $variations1 WHERE $variations1.id = "6039")more specifically, it returns :experiment_id-------------1010However, when I execute QUERY #1 having replaced value "1010" with QUERY #2as in the following query (QUERY #3) :SELECT * FROM iterations $iterations2, experiments $experiments1, clients $clients1WHERE $experiments1.id = $iterations2.experiment_id AND $clients1.property_id = $experiments1.property_id AND $experiments1.id IN (SELECT $iterations1.experiment_id FROM iterations $iterations1 WHERE $iterations1.id IN (SELECT $variations1.iteration_id FROM variations $variations1 WHERE $variations1.id = "6039"))I get no results. Why is that?I know I'm not providing you with any information as to how the tables are related.However, given that QUERY #1 & QUERY #2 work fine on their own, can you see why they do not work when combined into QUERY #3?Thank you for any help |
|
MuMu88
Aged Yak Warrior
549 Posts |
Posted - 2013-06-13 : 15:59:02
|
On the surface everthing looks fine.Would you be able to post your ddls and some example data. |
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2013-06-13 : 20:56:05
|
Is this MS Sql Server? I don't think you can use "$" as the start of your table aliases. Also you should enclose constant values in single quotes not double quotes.Be One with the OptimizerTG |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-06-14 : 00:56:07
|
whats the datatype of $iterations1.experiment_id? may its char? try this tooSELECT * FROM iterations $iterations2, experiments $experiments1, clients $clients1WHERE $experiments1.id = $iterations2.experiment_idAND $clients1.property_id = $experiments1.property_idAND $experiments1.id IN (SELECT RTRIM($iterations1.experiment_id)FROM iterations $iterations1WHERE $iterations1.id IN (SELECT $variations1.iteration_idFROM variations $variations1WHERE $variations1.id = "6039")) ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
Sprout Coder
Starting Member
2 Posts |
Posted - 2013-06-15 : 12:52:05
|
I tried that but I still get nothing. TO be clear, I don't get an error, just no results. |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-06-15 : 12:55:17
|
one more attemptSELECT * FROM iterations $iterations2, experiments $experiments1, clients $clients1WHERE $experiments1.id = $iterations2.experiment_idAND $clients1.property_id = $experiments1.property_idAND REPLACE(LTRIM(RTRIM($experiments1.id)),CHAR(160),'') IN (SELECT REPLACE(LTRIM(RTRIM($iterations1.experiment_id)),CHAR(160),'')FROM iterations $iterations1WHERE $iterations1.id IN (SELECT $variations1.iteration_idFROM variations $variations1WHERE $variations1.id = "6039")) ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
|
|
|
|
|