January 9, 2015 at 8:58 am
Let's say I have a table with the following columns, ID, c1 and c2. I select all rows with ID= something (or c1=something), but I also need all rows added where c1=c2. How do you do that? I tried a self join, but what I did isn't working... Thanks!
January 9, 2015 at 9:07 am
Unless I've misunderstood...
WHERE ID = something
AND c1 = c2
Edit:
Hold on - I think I know what you're asking. If you want everything where ID = something as well as everything where c1 = c2, just change the AND to an OR.
John
January 9, 2015 at 9:11 am
That does not work, I've tried it.
January 9, 2015 at 9:12 am
I've tried that, too - only 'where c1=c2' doesn't work, either.
January 9, 2015 at 9:23 am
Can you post a simple DDL of the table, some sample data and expected results so people can see what you are trying to do.
_________________________________________________________________________
SSC Guide to Posting and Best Practices
January 9, 2015 at 9:44 am
As an example:
CREATE TABLE [dbo].[Test](
[id] [int] NULL,
[c1] [varchar](50) NULL,
[c2] [varchar](50) NULL
) ON [PRIMARY]
INSERT INTO Test
([id]
,[c1]
,[c2])
VALUES
(1,'Nick','Mark'),
(2,'Mark','Nick'),
(3,'Chris','Mark'),
(4,'Nick','Chris'),
(5,'Mark','Jim'),
(6,'dude','Nick')
Logically, this is what I want:
rows where id=3 but also all other rows where c1 = c2
for exampla, id = 3 returns row (3,Chris, Mark), but I also need row (4,Nick,Chris) because row 3 C1 (chris) = row 4 C2(chris again)
I hope I explained this right... Thanks!
January 9, 2015 at 9:52 am
a friend figured it out.. here is is:
select * from test
where id = 3
union all
select * from test
where c2 =
(select c1 from test where id = 3)
January 9, 2015 at 9:53 am
Yes, or you could use an OR. Try them both, and see which performs better.
John
January 9, 2015 at 9:53 am
;WITH Primaries AS (
SELECT *
FROM #Test
WHERE id = 3
)
SELECT *
FROM Primaries
UNION ALL
SELECT t.*
FROM #Test t INNER JOIN Primaries p ON p.c1 = t.c2
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
January 9, 2015 at 9:57 am
thank you, that works, too!
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply