Sorry in advanced for this brain dead question
-
This is probably easy...I am just brain dead...I am using Ms Sql 2008, and given the following two tables: Table1 --------------- Field1 Field2 3 55 3 55 4 55 5 55 3 66 3 66 3 77 4 77 3 88 Table2 --------------- Field1 Field2 3 9 4 10 5 11 Table1 and Table2 are linked together by the Field1's...I get a parameter passed in like "9" for Table2's Field2...how do I return all of the records from Table1 of just the 66's and the one record of 88?
-
This is probably easy...I am just brain dead...I am using Ms Sql 2008, and given the following two tables: Table1 --------------- Field1 Field2 3 55 3 55 4 55 5 55 3 66 3 66 3 77 4 77 3 88 Table2 --------------- Field1 Field2 3 9 4 10 5 11 Table1 and Table2 are linked together by the Field1's...I get a parameter passed in like "9" for Table2's Field2...how do I return all of the records from Table1 of just the 66's and the one record of 88?
SELECT Field2 FROM TABLE1 WHERE Field1 = (SELECT Field1 FROM Table2 WHERE Field2 = 9) AND Field2 IN(66,88)
I know the language. I've read a book. - _Madmatt
-
SELECT Field2 FROM TABLE1 WHERE Field1 = (SELECT Field1 FROM Table2 WHERE Field2 = 9) AND Field2 IN(66,88)
I know the language. I've read a book. - _Madmatt
Thank you, but I don't want to hard code the 66 and 88 as those values I constantly changing. I just wanted those records as they don't have other linking fields of the 4 and 5 for example.
-
This is probably easy...I am just brain dead...I am using Ms Sql 2008, and given the following two tables: Table1 --------------- Field1 Field2 3 55 3 55 4 55 5 55 3 66 3 66 3 77 4 77 3 88 Table2 --------------- Field1 Field2 3 9 4 10 5 11 Table1 and Table2 are linked together by the Field1's...I get a parameter passed in like "9" for Table2's Field2...how do I return all of the records from Table1 of just the 66's and the one record of 88?
Start with: select t1.* from t1 inner join t2 on t1.field1 = t2.field1 where t2.field2 = @passedvalue But, why is the (3,55) pair excluded? Tim
-
Start with: select t1.* from t1 inner join t2 on t1.field1 = t2.field1 where t2.field2 = @passedvalue But, why is the (3,55) pair excluded? Tim
55 is excluded because it has a link of 4 and 5...and 77 is excluded because it has a link of 4 in it. Table1 --------------- Field1 -Field2 3 -55 3 -55 4 -55 5 -55 3 -66 3 -66 3 -77 4 -77 3 -88 Table2 --------------- Field1 -Field2 3 -9 4 -10 5 -11
-
Thank you, but I don't want to hard code the 66 and 88 as those values I constantly changing. I just wanted those records as they don't have other linking fields of the 4 and 5 for example.
Of course you don't want them hard coded, this was just an example. :rolleyes:
I know the language. I've read a book. - _Madmatt
-
55 is excluded because it has a link of 4 and 5...and 77 is excluded because it has a link of 4 in it. Table1 --------------- Field1 -Field2 3 -55 3 -55 4 -55 5 -55 3 -66 3 -66 3 -77 4 -77 3 -88 Table2 --------------- Field1 -Field2 3 -9 4 -10 5 -11
Mike654321 wrote:
55 is excluded because it has a link of 4 and 5...and 77 is excluded because it has a link of 4 in it.
That was useful info. Is it something like this you need?
SELECT Table1.field2
FROM Table1 join Table2
ON Table1.field1 = Table2.field1
WHERE Table2.field2 = 9
AND Table1.field2 NOT IN
(
SELECT DISTINCT Table1.field2
FROM Table1 join Table2
ON Table1.field1 = Table2.field1
WHERE Table2.field2 <> 9
)
;This query might be very slow if the tables are big. which can be cured with composite indexes on both tables.
My postings are a natural product. The slight variations in spelling and grammar enhance their individual character and beauty and are in no way to be considered flaws or defects.