Home     |     .Net Programming    |     cSharp Home    |     Sql Server Home    |     Javascript / Client Side Development     |     Ajax Programming

Ruby on Rails Development     |     Perl Programming     |     C Programming Language     |     C++ Programming     |     IT Jobs

Python Programming Language     |     Laptop Suggestions?    |     TCL Scripting     |     Fortran Programming     |     Scheme Programming Language


 
 
Cervo Technologies
The Right Source to Outsource

MS Dynamics CRM 3.0

Sql Server Programming

PRoblem with sub query


HI ,
I have two tables
create table locations (loc_id int primary key,location_code char(20))
sample data
insert into locations values (1,'xxx')
insert into locations values(2,'yyy')
insert into locations values(3,'zzz')
insert into locations values(4,'www')

select * from locations

Table 2:
create table location_history(location1 int constraint fk_loc_id
references locations(loc_id),
location2 int constraint fk_loc_id2 references locations(loc_id),
location3 int constraint fk_loc_id3 references locations(loc_id))

sample data
insert into location_history values (1,3,2)
insert into location_history values (4,1,4)

select * from location_history
select * from locations

how can i display the result as
loc1  loc2 loc3
xxx   zzz  yyy
www   xxx  www

i have written query like this

select
(Select a.location_code from locations a join location_history b on
a.loc_id = b.location1) ,
(Select a.location_code from locations a join location_history e on
a.loc_id = e.location2),
(Select a.location_code from locations a join location_history f on
a.loc_id = f.location3)
 from location_history
i was getting error
Subquery returned more than 1 value. This is not permitted when the
subquery follows =, !=, <, <= , >, >= or when the subquery is used as
an expression.

Help needed
Thanks
sa

select
        a.location_code loc1,
        b.location_code loc2,
        c.location_code loc3
from
        location_history lh
        inner join locations a on lh.location1 = a.loc_id
        inner join locations b on lh.location2 = b.loc_id
        inner join locations c on lh.location3 = c.loc_id

Cheers,
Jason Lepack

On May 30, 7:12 am, satish <satishkumar.gourabath@gmail.com> wrote:

How about this:

create table #locations(loc_id int primary key,location_code char(20))
--sample data
insert into #locations values (1,'xxx')
insert into #locations values(2,'yyy')
insert into #locations values(3,'zzz')
insert into #locations values(4,'www')

select * from #locations

create table #location_history(location1 int constraint fk_loc_id
   references locations(loc_id),
   location2 int constraint fk_loc_id2 references #locations(loc_id),
   location3 int constraint fk_loc_id3 references #locations(loc_id))

--sample data
insert into #location_history values (1,3,2)
insert into #location_history values (4,1,4)

select * from #location_history
select * from #locations

how can i display the result as
loc1  loc2 loc3
xxx   zzz  yyy
www   xxx  www

select *
from #location_history h inner join #locations l
  on h.location1 = l.loc_id

select l1.location_code as loc1, l2.location_code as loc2, l3.location_code
as loc3
from #location_history h left join #locations l1
  on h.location1 = l1.loc_id
left join #locations l2
  on h.location2 = l2.loc_id
left join #locations l3
  on h.location3 = l3.loc_id

drop table #locations
drop table #location_history

--
TheSQLGuru
President
Indicium Resources, Inc.

"satish" <satishkumar.gourabath@gmail.com> wrote in message

news:1180523545.894015.266680@x35g2000prf.googlegroups.com...

Ah, left join, nice touch.

On May 30, 9:27 am, "TheSQLGuru" <kgbo@earthlink.net> wrote:

Add to del.icio.us | Digg this | Stumble it | Powered by Megasolutions Inc