Select from 3 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

Select from 3 tables

Author

Topic

nikoo56
Starting Member

26 Posts

Posted - 2012-04-09 : 13:56:50

I like to select a column name notes from 3 table for each person.

select * from tblStuNotes where stu_rec_num = 135573

and

Select notes from tblbase where stu_rec_num= 135573

and

select notes from tblstu where stu_rec_num= 135573

How I can so that?

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2012-04-09 : 14:09:12

Change your AND to UNION and it will work.

How to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx
For ultra basic questions, follow these links.
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2012-04-09 : 14:09:50

use an inner join

Jim

Everyday I learn something that somebody else already knew

nikoo56
Starting Member

26 Posts

Posted - 2012-04-09 : 14:13:19

it is not working with union it gave me this error. I would like to have all column from 1st select , 2nd and 3rd only notes or all would be ok but in any situation it gave me this error.

All queries combined using a UNION, INTERSECT or EXCEPT operator must have an equal number of expressions in their target lists.

How to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx
For ultra basic questions, follow these links.
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp
[/quote]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-04-09 : 14:53:42

you need to explicitly put column names


select notes,column1,column2,...
from tblStuNotes where stu_rec_num = 135573

union all

Select notes,null,null,null,... 
from tblbase where stu_rec_num= 135573

union all

select notes,null,null,.. from tblstu where stu_rec_num= 135573

if you want only distinct set use union instead of union all

also if you want other values also from second and third selects put actual columns there instead of null

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

nikoo56
Starting Member

26 Posts

Posted - 2012-04-09 : 17:05:24

I have this

elect
stu_rec_num,
id,
old_notes,
time_stamp,
host_name,
user_name,
is_new
from tblStuNotesBackup where stu_rec_num = 13557310

UNION All

select

notes,
null,
null,
null,
null,
null,
null
from tblbase_stu where stu_rec_num=13557310

queries are working separate but when I try them together it gave me this error.
Arithmetic overflow error converting varchar to data type numeric.

quote:
Originally posted by visakh16

you need to explicitly put column names

select notes,column1,column2,...
from tblStuNotes where stu_rec_num = 135573

union all

Select notes,null,null,null,...
from tblbase where stu_rec_num= 135573

union all

select notes,null,null,.. from tblstu where stu_rec_num= 135573

if you want only distinct set use union instead of union all

also if you want other values also from second and third selects put actual columns there instead of null

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2012-04-10 : 06:22:54

All of the columns should be of the same data type as well. Try doing an explicit conversion of the column.

How to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx
For ultra basic questions, follow these links.
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-04-10 : 15:29:21

the corresponding columns are not same


select 
stu_rec_num,
id,
old_notes,
time_stamp,
host_name,
user_name,
is_new
from tblStuNotesBackup where stu_rec_num = 13557310

UNION All

select 
null,
null,
notes,
null,
null,
null,
null
from tblbase_stu where stu_rec_num=13557310

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources