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 |
|
TStewartFan
Starting Member
4 Posts |
 Posted - 2011-08-22 : 20:22:54
|
| I am having difficulty getting a simple select to work under every isntance and wanted to see if someone could point me in the right direction. Essientially I have 3 tables that can possibly contain identical data but only one table contains the entire family. If an item is found in table one then is that itm found in table two? If so then find the parent of that child amd list all the children of that parent. Are any of those children listed in table three then exclude these children from the original results.See below:Table_OneChildTable_TwoParent, ChildTable_ThreeChildselect * from table_one where child in (select child from table_two where parent in(select parent from table_three))Essientially once the value from 3 is found then the value of two should be the child value of three and all borthers and sisters of that child and then the select * should look for any children of that parent and if found then exclude all other children of that parent from the results. Currently it's feast or famine. It either provides all the children or no children but I want it to search through all the children but only list the children it found and exclude the other children from subsequent searches. Hope that makes sense but if not please let me know but in the mean time I will continue to pick away at it. |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2011-08-22 : 23:55:09
|
| [code]SELECT t3.ChildFROM Table_One t1INNER JOIN Table_Two t2ON t2.Child = t1.ChildINNER JOIN Table_Two t3ON t3.Parent = t2.ParentLEFT JOIN Table_Three t4ON t4.Child = t3.ChildWHERE t4.Child IS NULL[/code]------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
 |
|
|
TStewartFan
Starting Member
4 Posts |
Posted - 2011-08-23 : 08:15:09
|
| Sorry for the delay. I have integrated the joins into my query and it is still producing feast or famine results. I am still receiving all the children of the parent from the child that was a matching result but I was planning on tickering with left and left outer join so this has me going in the right direction with my morning cup of mojo :D Thanks again for the assistance. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2011-08-23 : 09:28:06
|
| welcome------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
TStewartFan
Starting Member
4 Posts |
Posted - 2011-08-25 : 07:58:35
|
| I was able to get it to work by focusing on the parent as the result rather then the child but I am curious why the where is null is needed because 0 results are produced if this is left in so wanted to know what I was loosing by removing it? Thanks. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2011-08-25 : 08:01:52
|
| that left join is to find records which doesnt have match in Table_Three based on child value. it will return all values from left part of query and will have null values for right part columns if it doesnt find match. so is null will ensure we return only records with no match in Table_Three on Child which is what your original requirement said. Are any of those children listed in table three then exclude these children from the original results.------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
TStewartFan
Starting Member
4 Posts |
Posted - 2011-08-25 : 08:17:04
|
| Gotcha. Thanks for the claification. |
|
|
|
|
|
|
Select In Statement