query

Question

query

nabajyoti.b

SSCrazy

Points: 2177
More actions
March 3, 2009 at 5:39 am

#128114

i have two tables
table 1
create table table1
(
emp_id char(4),
name char(20),
DOB datetime,
)
and on a later date i created another
create table table2
(
emp_id char(4),
name char(20),
emp_address char(20),
DOB datetime,
)
both of these gets populated by a form
however for all the records before the create of table2, teh records were not present in table2 as it was not created.
Also, table1 does not have the address column
i want to create a query which will give me emp_id, name, emp_address,DOB.
However, for records before the creation of table2 there will not be any address, which is acceptable as the data is not there.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Dugi SSCoach Points: 17998 More actions · Answer 1

Hmmm if you prepare more details about you problem it would be better that the other will choose your problem without spending time to create table then add some data in these tables ... check my signature and read the article how to post thing about your problem!

============================================================
SELECT YOUR PROBLEM FROM SSC.com WHERE PROBLEM DESCRIPTION =
http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

nabajyoti.b (3/3/2009)
i want to create a query which will give me emp_id, name, emp_address,DOB.

Join the two tables with a LEFT OUTER JOIN, instead of an INNER JOIN

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Dugi SSCoach Points: 17998 More actions · Answer 3

Yea Correct something like this:

SELECT T1.emp_id, T2.emp_id, T2.name, T2.DOB, T2.emp_address

FROM TABLE2 T2 LEFT OUTER JOIN TABLE1 T1 ON t1.emp_id = t2.emp_id

============================================================
SELECT YOUR PROBLEM FROM SSC.com WHERE PROBLEM DESCRIPTION =
http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]

JohnSmith001 Right there with Babe Points: 765 More actions · Answer 4

INSERT INTO Table1 SELECT

'A1', 'Mangal', '2009-01-01' UNION ALL SELECT

'A2', 'John', '2009-01-02'

INSERT INTO Table2 SELECT

'A2', 'John', 'Some Place','2009-01-02'

SELECT a.emp_id, a.[name], b.emp_address, a.dob

FROM Table1 A LEFT JOIN Table2 B

ON a.emp_id = b.emp_id

John Smith

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 5

If new rows were going into table1 until it was replaced by table2, then this might be more appropriate:

SELECT emp_id, name, CAST(NULL AS CHAR(20)) AS emp_address, DOB

FROM table1

UNION ALL

SELECT emp_id, name, emp_address, DOB

FROM table2

Note that ALL isn't required if there are no common rows between the two tables, it works faster because there's no dedupe step.

^{“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