combining two tables - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

combining two tables

Author

Topic

sqllover
Constraint Violating Yak Guru

338 Posts

Posted - 2007-02-05 : 00:46:13

hi

this is my query:
tabel1:
select userid,user_name,password,role_code,convert(varchar,expiry_date,101) as expiry_date,created_date,active from usermaster where userid='1' and active='1'
if i run this query i will get output like this:
userid user_name password role_code expiry_date created_date
1 karthik karthik AD 01/17/2008 2007-01-24
active
0:00:00.000 1

i have another table which has the following records

tabel 2:
select * from code master

codename codedescription
AD admin
sp supervisor

so in the first query i need the output as like tithe following:

userid user_name password role_code expiry_date created_date
1 karthik karthik admin 01/17/2008 2007-01-24
active
0:00:00.000 1

so how to combine this two tables inorder to get the codedescription as admin for the roll_code=ad

so please give me query for this

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-02-05 : 00:51:56

use INNER JOIN


select u.userid, 
       u.user_name, 
       u.password, 
       role_code c.codedescription, 
       convert(varchar, u.expiry_date,101) as expiry_date,
       u.created_date,
       u.active 
from   usermaster u inner join [code master] c
       on u.role_code = c.codename
where  u.userid = '1' 
and    u.active = '1'

sqllover
Constraint Violating Yak Guru

338 Posts

Posted - 2007-02-05 : 02:27:23

hi khtan ur query gives null balues but i have value in my database

select u.userid,
u.user_name,
u.password,
c.code_description,
convert(varchar, u.expiry_date,101) as expiry_date,
u.created_date,
u.active
from usermaster u inner join [codeMaster] codename
on u.role_code = c.code
where u.userid = '1'
and u.active = '1'
please help me do

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-02-05 : 02:37:49

Please post table DDL with some sample data and the expected result

sqllover
Constraint Violating Yak Guru

338 Posts

Posted - 2007-02-05 : 03:11:56

hi khtan,

this is my query:
select userid,user_name,password,role_code,convert(varchar,expiry_date,101) as expiry_date,created_date,active from usermaster where userid='1' and active='1'

and i am getting :
userid username password role_code expiry_date created_date
1 karthik karthik AD 01/17/2008 2007-01-24
active
00:00:00.000 1

i am having another table named as codeMaster
select * from codeMaster

code code_description
AD admin
sp supervisor

so then i need to combine this table for getting ans result this

expecting result:

userid username password code_description expiry_date created_date
1 karthik karthik admin 01/17/2008 2007-01-24
active
00:00:00.000 1

so now please help me

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-02-05 : 03:18:32

Where are the table DDLs? Right click on table name in the Enterprise Manager, Click on Generate Script and post that script for both the tables here. We need to know column names, data type of columns, size, constraints etc.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-02-05 : 03:27:48

quote:
ur query gives null balues but i have value in my database

With your sample data provided. NULL ? which column ?


declare @usermaster table
(
	userid 		int,
	username 	varchar(10),
	password 	varchar(10),
	role_code 	varchar(10),
	expiry_date 	datetime,
	created_date	datetime,
	active		int
)

insert into @usermaster
select	1, 'karthik', 'karthik', 'AD', '20080117', '20070124', 1

declare @codemaster table
(
	code			varchar(10),
	code_description	varchar(10)
)

insert into @codemaster
select	'AD', 'admin'	union all
select	'sp', 'supervisor'

select u.userid, 
       u.username, 
       u.password, 
       c.code_description, 
       convert(varchar, u.expiry_date,101) as expiry_date,
       u.created_date,
       u.active 
from   @usermaster u inner join @codemaster c
       on u.role_code = c.code
where  u.userid = 1
and    u.active = 1

/*
userid  username   password   code_description expiry_date  created_date   active      
------- ---------- ---------- ---------------- ------------ -------------- ----------- 
1       karthik    karthik    admin            01/17/2008   2007-01-24     1
*/

sqllover
Constraint Violating Yak Guru

338 Posts

Posted - 2007-02-05 : 03:28:36

hi harsh_athalye

This is usermaster table spec:

CREATE TABLE [dbo].[userMaster] (
[userid] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[user_name] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[password] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[role_code] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[effective_from] [datetime] NOT NULL ,
[expiry_date] [datetime] NOT NULL ,
[active] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[created_date] [datetime] NOT NULL
) ON [PRIMARY]
GO

The following is codeMater table spec:

CREATE TABLE [dbo].[codeMaster] (
[userid] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[creation_date] [datetime] NOT NULL ,
[active] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[category_code] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[code] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[code_description] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]

now please help me

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-02-05 : 03:31:17

Since all the table column are defined as NOT NULL, there cannot be NULL value return from the query.


select u.userid,
       u.user_name,
       u.password,
       c.code_description,
       convert(varchar, u.expiry_date,101) as expiry_date,
       u.created_date,
       u.active
from usermaster u inner join [codeMaster] codename
on   u.role_code = c.code
where u.userid = '1'
and   u.active = '1'

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-02-05 : 03:35:09

Okay, here is my attempt:

select u.userid, 
       u.user_name, 
       u.password, 
       c.code_description as role_code, 
       convert(varchar, u.expiry_date,101) as expiry_date,
       u.created_date,
       u.active 
from   usermaster u inner join codemaster c
       on u.role_code = c.code and
       u.active = c.active and
       u.userid = c.userid
where  u.userid = '1' 
and    u.active = '1'

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-02-05 : 03:39:13

Does the query still return NULL value ? If so, which column is it ? And post the sample data for that record that gives NULL value.

sqllover
Constraint Violating Yak Guru

338 Posts

Posted - 2007-02-05 : 03:43:08

hi khtan have you seen my codeMaster table in that also userid there.but i am not having userid '1' in codeMaster table.

the folowing is my codeMaster table values:

select * from codeMaster:

userid created_date active code code_description
4 1/27/2007 1 AD admin
4 1/25/2007 1 SP supervisor

the following is usermaster values:
select * from usermaster:
userid username password role_code expiry_date created_date active
1 karthik karthik AD 01/17/2008 2007-01-24 1

now u u can clear why i am getting null values.please help me to get

sqllover
Constraint Violating Yak Guru

338 Posts

Posted - 2007-02-05 : 03:51:05

hi harsh_athalye and khtan please give solution for me

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-02-05 : 03:53:18

Nothing wrong here. No NULL value returned


drop table [userMaster]
drop table [codeMaster]

CREATE TABLE [dbo].[userMaster] 
(
	[userid] [varchar] (15)  	NOT NULL ,
	[user_name] [varchar] (50)  	NOT NULL ,
	[password] [varchar] (50)  	NOT NULL ,
	[role_code] [varchar] (50)  	NOT NULL ,
	[effective_from] [datetime] 	NOT NULL ,
	[expiry_date] [datetime] 	NOT NULL ,
	[active] [varchar] (10)  	NOT NULL ,
	[created_date] [datetime] 	NOT NULL
)
GO

insert into [userMaster]
select	'1', 'karthik', 'karthik', 'AD', '01/17/2008', '2007-01-24', '1', getdate()

CREATE TABLE [dbo].[codeMaster] 
(
	[userid] [varchar] (15)  		NOT NULL ,
	[creation_date] [datetime] 		NOT NULL ,
	[active] [varchar] (10)  		NOT NULL ,
	--[category_code] [varchar] (20)  	NOT NULL ,
	code [varchar] (20)  			NOT NULL ,
	[code_description] [varchar] (50)  	NOT NULL
)

insert into [codeMaster]
select	'4', '1/27/2007', '1', 'AD', 'admin'		union all
select	'4', '1/25/2007', '1', 'SP', 'supervisor'


select 	u.userid,
       	u.user_name,
       	u.password,
       	c.code_description,
       	convert(varchar, u.expiry_date,101) as expiry_date,
       	u.created_date,
       	u.active
from 	[userMaster] u inner join [codeMaster] c
	on   	u.role_code = c.code
where 	u.userid = '1'
and   	u.active = '1'

/*
userid          user_name  password  code_description   expiry_date  created_date             active     
--------------- ---------- --------- ------------------ ------------ ------------------------ ---------- 
1               karthik    karthik   admin              01/24/2007   2007-02-05 16:52:03.653  1

(1 row(s) affected)
*/

sqllover
Constraint Violating Yak Guru

338 Posts

Posted - 2007-02-05 : 04:02:06

hi khtan i tried againg it show emty record,
please look at this the following lines:

select * from codeMaster:

userid created_date active code code_description
4 1/27/2007 1 AD admin
4 1/25/2007 1 SP supervisor

the following is usermaster values:
select * from usermaster:
userid username password role_code expiry_date created_date active
1 karthik karthik AD 01/17/2008 2007-01-24 1

so now can u guess what is my problem,sorry for giving more trouble

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-02-05 : 04:05:40

Are you using the query I posted ?

Or this ?

quote:
Originally posted by sqllover

hi khtan ur query gives null balues but i have value in my database

select u.userid,
u.user_name,
u.password,
c.code_description,
convert(varchar, u.expiry_date,101) as expiry_date,
u.created_date,
u.active
from usermaster u inner join [codeMaster] codename
on u.role_code = c.code
where u.userid = '1'
and u.active = '1'
please help me do

If you use the I posted, or the above you will not get empty record (NOTE : EMPTY RECORDS IS NOT NULL. You are confusing us with NULL VALUE). Anyway are you joining codeMaster.userid with userMaster.userid ? Please post your query here.

sqllover
Constraint Violating Yak Guru

338 Posts

Posted - 2007-02-05 : 04:06:07

hi khtan and harsh_athalye i got it sorry i mistake was in my side,sorry for the inconvenience

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-02-05 : 04:07:44

So, got your required result ?

sqllover
Constraint Violating Yak Guru

338 Posts

Posted - 2007-02-05 : 04:12:55

hi khtan yes i got my required result,very happy,thank tou very muchhh