"Select Multiple Tables" [SOLVED]

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

Old Forums

CLOSED - General SQL Server

"Select Multiple Tables" [SOLVED]

Author

Topic

cutepraba
Yak Posting Veteran

53 Posts

Posted - 2006-09-11 : 03:24:22

I have two tables named
Table 1
Id name Role
23 John student
24 Peter teacher
25 james Admin

Table 2
job tid sid aid remarks
2345 23 24 25 Three members

Now how to take the names in select command

I need to display their names like

Job tname sname remarks

2345 teacher name student name remark

____________
Praba

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-09-11 : 03:39:30

[code]
Select t1.Job,T2.TeachName,T3.StudentName,T4.AdminName,T1.Remarks From Table2 T1
Inner Join
(Select [ID],Name As TeachName From Table1 Where Role = 'teacher') As T2
On T1.[Tid] = T2.[ID]
(Select [ID],Name As StudentName From Table1 Where Role = 'student') As T3
On T1.[Sid] = T3.[ID]
(Select [ID],Name As AdminName From Table1 Where Role = 'Admin') As T4
On T1.[aid] = T4.[ID]
[/code]

Chirag

cutepraba
Yak Posting Veteran

53 Posts

Posted - 2006-09-11 : 06:28:13

I have another 37 columns of data after "Remarks column" in Table2

Can we use t1.* instead of t1.c1, t1.c2, t1.c3............

____________
Praba

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-09-11 : 06:30:30

yes you can do that...

Chirag

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-09-11 : 06:30:39

What happened (which result did you get) when you tried, before asking here?

Peter Larsson
Helsingborg, Sweden

cutepraba
Yak Posting Veteran

53 Posts

Posted - 2006-09-11 : 06:51:51

cname means clerkname
staff table containing staff details

i am getting error message

Incorrect syntax near the keyword 'select'
My query
select Q.*,t1.tname, t2.sname, t3.aname, t4.cname from Drama q Inner Join (Select staffid, staffname As tName From staff) As t1 on q.[tid] = t1.[staffID] (Select staffid, staffname As prName From staff) As t2 on q.[sid] = t2.[staffID] (Select staffid, staffname As qcName From staff) As t3 on q.[aid] = t3.[staffID] (Select staffid, staffname As fmName From staff) As t4 on q.[cid] = t4.[staffID]

Pleaseeeeeeeeeeeeeeeeeeeeee help me

____________
Praba

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-09-11 : 07:23:08

Where did all the JOINs go?

select		Q.*,
		t1.tname,
		t2.sname,
		t3.aname,
		t4.cname
from		Drama q
Inner Join	(
			Select	staffid,
				staffname As tName
			From	staff
		) As t1 on q.[tid] = t1.[staffID]
inner join	(
			Select	staffid,
				staffname As prName
			From	staff
		) As t2 on q.[sid] = t2.[staffID]
inner join	(
			Select	staffid,
				staffname As qcName
			From	staff
		) As t3 on q.[aid] = t3.[staffID]
inner join	(
			Select	staffid,
				staffname As fmName
			From	staff
		) As t4 on q.[cid] = t4.[staffID]

Peter Larsson
Helsingborg, Sweden

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-09-11 : 07:25:43

Praba, you will not get desired output from the query which you have wrote... there should be where cluase...


select		Q.*,
		t1.tname,
		t2.sname,
		t3.aname,
		t4.cname
from		Drama q
Inner Join	(
			Select	staffid,
				staffname As tName
			From	staff  Where Role = <Some Role> 
		) As t1 on q.[tid] = t1.[staffID]
inner join	(
			Select	staffid,
				staffname As prName
			From	staff  Where Role = <Some Role> 
		) As t2 on q.[sid] = t2.[staffID]
inner join	(
			Select	staffid,
				staffname As qcName
			From	staff  Where Role = <Some Role> 
		) As t3 on q.[aid] = t3.[staffID]
inner join	(
			Select	staffid,
				staffname As fmName
			From	staff  Where Role = <Some Role> 
		) As t4 on q.[cid] = t4.[staffID]

Chirag

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-09-11 : 07:25:45

If there is any change that either tid, sid, aid or cid can be NULL, use LEFT JOIN instead.

Peter Larsson
Helsingborg, Sweden

cutepraba
Yak Posting Veteran

53 Posts

Posted - 2006-09-11 : 09:35:39

How to set this topic as "Solved"

____________
Praba

cutepraba
Yak Posting Veteran

53 Posts

Posted - 2006-09-11 : 09:36:45

Thank you to all my Seniors

____________
Praba

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-09-11 : 09:42:17

Edit the TOPIC name in the "General SQL Server" section.
Add [SOLVED] to the heading.

It would be nice to know what did help you.

Peter Larsson
Helsingborg, Sweden

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-09-11 : 09:44:24

of course it will be your query who helps a lots of people ;)

Chirag

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-09-11 : 12:04:13

If your table1 has hundreds of roles, you may have problem in displaying the way you want

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources