January 23, 2010 at 8:41 pm
I have a need to develop a query that returns multiple rows from a table, based upon a single search term, with this search term to be applied to two different fields, with one filed to be applied to multiple records based upon an associated field in the initial record(s) retrieved.
Sorry for the messy description. An example is better suited for this description.
This is the example of what I need to accomplish. The table is set up with two fields, as follows:
Field A Field B
123 123
123 124
123 125
123 126
230 231
230 232
230 234
340 340
340 341
340 342
451 455
Field B is the Primary Key for the table. Field A in nonunique. The combination of Field A and Field B is unique.
If I attempt to do a WHERE BY search term of '124', I need the SELECT statement to retrieve the one record which matches the search terms to Field B [A=123 and B=124], but I also need it retrieve any and all records that also have the same '123' in Field A of this one record that was retrieved, based upon its Field A contents. In other words, a single search term of '124' (or '123' or '125' or '126') would retrieve the following record set:
Field A Field B
123 123
123 124
123 125
123 126
Likewise, a single search term of '340', '341' or '342' would retrieve the following record set:
Field A Field B
340 340
341 341
342 342
Another wrench in the works is that a Field A value does not necessary have to be in Field B. For example, in the above table data, '230' is in Field A, but it is not contained in Field B. If the single search value is not in Field B, Field A still needs to be searched. For example, a search on the value '230' needs to return the following record set:
Field A Field B
230 231
230 232
230 234
Likewise, a search with a single search term of either '231', '232' or '234' would retrieve the same record shown immediately above.
What is the best approach to accomplishing this search? Can this be accomplished with a single SELECT statement using subqueries?
Many thanks, in advance, for your help and assistance!
January 23, 2010 at 11:50 pm
Try this:
DECLARE @FieldB int
SET @FieldB = 124
SELECT * From Table1
WHERE FieldA = (SELECT FieldA from Table1 WHERE FieldB = @FieldB)
Cheers,
Prithiviraj Kulasingham
http://preethiviraj.blogspot.com/
January 24, 2010 at 3:48 am
mail 41752 (1/23/2010)
Can this be accomplished with a single SELECT statement...?
Yes.
DROP TABLE #Table1
CREATE TABLE #Table1 (FieldA INT, FieldB INT)
INSERT INTO #Table1 (FieldA, FieldB)
SELECT 123, 123 UNION ALL
SELECT 123, 124 UNION ALL
SELECT 123, 125 UNION ALL
SELECT 123, 126 UNION ALL
SELECT 230, 231 UNION ALL
SELECT 230, 232 UNION ALL
SELECT 230, 234 UNION ALL
SELECT 340, 340 UNION ALL
SELECT 340, 341 UNION ALL
SELECT 340, 342 UNION ALL
SELECT 451, 455
DECLARE @Needle INT
SET @Needle = 123
SELECT DISTINCT a.FieldA, a.FieldB
FROM #Table1 a
INNER JOIN #Table1 b ON b.FieldA = a.FieldA
WHERE b.FieldB = @Needle
OR a.FieldA = @Needle
ORDER BY a.FieldA, a.FieldB
Cheers
ChrisM@home
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply