Post reply

query to return rows with c2=c3

  • January 9, 2015 at 8:58 am

    #294183

    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

    #1769120

    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

    #1769122

    That does not work, I've tried it.

  • January 9, 2015 at 9:12 am

    #1769125

    I've tried that, too - only 'where c1=c2' doesn't work, either.

  • January 9, 2015 at 9:23 am

    #1769130

    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

    #1769135

    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

    #1769138

    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

    #1769141

    Yes, or you could use an OR. Try them both, and see which performs better.

    John

  • January 9, 2015 at 9:53 am

    #1769140

    ;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

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    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

    #1769143

    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