How Do I Write A Nested Query/Subquery In Sql Server

Question

1.00/5 (1 vote)

See more:

SQL-Server-2008R2

I have to fetch some data (e.g. Email, ClientName, DOB, CNIC, ClientId) of the clients who's birthday lies on current date for some purpose, and then log/insert it into a new table "Birthday_Client". But i am getting difficulty in making query because of DB structure it is like this:

DB-1 has tables Account and Client
Account table has Email, CNIC and ClientID
&
Client table has ClientName

DB-2 has table Client_Detail
Client_Detail has DOB(i.e. date of birth)

so i have to fetch the data of only those clients who's birthday is on current date and then insert this selected data into a new table viz Birthday_Client.
Please help!

P.S. : For making things easy for you ClientID column is present in all the tables.

Posted 30-Sep-14 14:30pm

Member 10536862

Add a Solution

Comments

Thomas Nielsen - getCore 1-Oct-14 8:56am

Does DB-1 and DB-2 refer to these data being in different databases?
Is it relevant if the query is performend in SQL server (TSQL) or using .net for instance?

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

RossMW · Answer 1 · 2014-09-30T17:12:00

Sounds like you need a join between the two client tables and then insert the information into a third table.

You can use something like
insert into Birthday_Client (fieldname 1, fieldname2 etc) select fieldname 1, fieldname2 etc from db-1 inner join db-2 on db-1.ClientID = db-2.ClientID where db-2.DOB = getdate()

You may need to be careful about the dob field. If it includes time then you may need to expand the query so that it is between the start and end of the day.

Santhosh Babu Mahimairaj · Answer 2 · 2014-10-02T20:54:00

Solution 2

SQL

You can use something like

insert into Clients_Birthday
(clientId) select db-1.ClientIDfrom db-1 inner join db-2 on db-1.ClientID = db-2.ClientID
 where  DATEPART(d, db-2.DOB= DATEPART(d, GETDATE())
    AND DATEPART(m, db-2.DOB= DATEPART(m, GETDATE())

Posted 2-Oct-14 20:54pm

Santhosh Babu Mahimairaj