Fetch rows from 2 tables based on row of another

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2012 Forums

Transact-SQL (2012)

Fetch rows from 2 tables based on row of another

Author

Topic

Prashanth.Ashok
Starting Member

1 Post

Posted - 2014-05-29 : 02:22:04

Hi,

I have two tables A & B.

A has the following columns: Col A1, Col A2, Col A3, Col A4
B has the following columns: Col B1, Col B2, Col B3

Here's some sample data. This is the initial state of the database.

Table A:
A1 A2 A3 A4
-- -- -- --
10_0 10 TESTED Test
10_1 10 TESTED Test
10_2 10 ASSIGNED Test

Table B:
B1 B2 B3
-- -- --
Test 10 A001
null 10 A002

After some processing, the state of the DB changes to:

Table A:
A1 A2 A3 A4
-- -- -- --
10_0 10 TESTED Test
10_1 10 TESTED Test
10_2 10 TESTED Test
10_3 10 ASSIGNED Test

Table B:
B1 B2 B3
-- -- --
Test 10 A001
Test1 10 A002

What I want to achieve is:

1. When the DB is in its original state (before processing), I need a query which fetches only the row with status as ASSIGNED along with the column B1 from table B (which has a null).
2. When the DB state changes, when I run the same query, no rows should be returned (because NULL has changed as Test1)

Is this possible? Please let me know if I should provide more info

sunder.bugatha
Yak Posting Veteran

66 Posts

Posted - 2014-05-29 : 07:07:07

You can use the below query for 1st question :

select *,(select B1 from B where B1=null) as B1 from A where A3='Assigned'

But i didn't understand the requirement for 2nd question.

Hema Sunder

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources