A Simple SQL Query

Question

A Simple SQL Query

chuck DM 101

I have the following table:-

User's image

I want Col 1 and Col 2 where Col2 =1 and the corresponding all the values of Col 1 where Col 1 =2. But this is an example. I can find 1st part (i.e,, Select Col1, Col 2 from Table1 where Col 2 =1). But my problem is how do we know the value of Col 1 is 2 where Col 2 =1?

The result set would be :-

User's image

Any help please??

Olaf Helper 47,436 Reputation points

2025-01-21T14:15:42.1866667+00:00

That's really to less on information (and a bit confusing).

Please post table design as DDL, some sample data as DML statement and the expected result.
chuck DM 101 Reputation points

2025-01-21T14:30:02.3466667+00:00

Thanks for your message, I have two requirements. 1st one is easy. I need Col1 and Col2 where Col2 = 1. It would be Select Col1 and Col2 from table1 where Col2=1.

The result set would be :-

In my example, as the value of Col1 is 2 when Col2 =1, So my 2nd requirement is :-

I need to get Col1 and Col2 where Col1=2. I can't simply write , Select Col1 and Col2 from Table1 where Col1 =2 because I don't know the value of Col1 when Col2 is 1.

For the 2nd requirement, the output would be:-

Does it make sense now?
chuck DM 101 Reputation points

2025-01-21T14:38:01.85+00:00

Thanks for your message. I have two requirements. the 1st one is, I need Col1 and Col2 when Col2 is 1. So the query would be:-

Select Col1, Col2 from Table1 where Col2 =1.

The Output of the 1st requirement is:-

Now the 2nd requirement is, as the value of Col1 is 2 when Col2 =1, So I need Col1 and Col2 when Col1 is 2. But the problem is that I don't know the value of Col1 when Col2 is 2. I simply can't write

Select Col1, Col2 from Table1 where Col1 =2.

The final output would be
Olaf Helper 47,436 Reputation points

2025-01-22T07:23:58.6166667+00:00

Again: Please post table design as DDL, some sample data as DML statement and the expected result.

Accepted answer

0 additional answers

Your answer

Olaf Helper 47,436 Reputation points

2025-01-21T14:15:42.1866667+00:00

That's really to less on information (and a bit confusing).

Please post table design as DDL, some sample data as DML statement and the expected result.
chuck DM 101 Reputation points

2025-01-21T14:30:02.3466667+00:00

Thanks for your message, I have two requirements. 1st one is easy. I need Col1 and Col2 where Col2 = 1. It would be Select Col1 and Col2 from table1 where Col2=1.

The result set would be :-

In my example, as the value of Col1 is 2 when Col2 =1, So my 2nd requirement is :-

I need to get Col1 and Col2 where Col1=2. I can't simply write , Select Col1 and Col2 from Table1 where Col1 =2 because I don't know the value of Col1 when Col2 is 1.

For the 2nd requirement, the output would be:-

Does it make sense now?
chuck DM 101 Reputation points

2025-01-21T14:38:01.85+00:00

Thanks for your message. I have two requirements. the 1st one is, I need Col1 and Col2 when Col2 is 1. So the query would be:-

Select Col1, Col2 from Table1 where Col2 =1.

The Output of the 1st requirement is:-

Now the 2nd requirement is, as the value of Col1 is 2 when Col2 =1, So I need Col1 and Col2 when Col1 is 2. But the problem is that I don't know the value of Col1 when Col2 is 2. I simply can't write

Select Col1, Col2 from Table1 where Col1 =2.

The final output would be
Olaf Helper 47,436 Reputation points

2025-01-22T07:23:58.6166667+00:00

Again: Please post table design as DDL, some sample data as DML statement and the expected result.

Answer 1

Yitzhak Khabinsky 26,586

Hi @chuck DM,

Please try the following solution leveraging EXISTS.

-- DDL and sample data population, start
DECLARE @tbl TABLE (col1 INT, col2 INT);
INSERT INTO @tbl (col1, col2) VALUES
(2, 1),
(2, 3),
(2, 5),
(3, 4),
(5, 6);
-- DDL and sample data population, end

SELECT * FROM @tbl AS a
WHERE EXISTS (
SELECT 1 FROM @tbl AS b
WHERE b.col2 = 1
	AND b.col1 = a.col1);

Share via

A Simple SQL Query

0 additional answers

Your answer