joining not related 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

joining not related tables

Author

Topic

cwtriguns2002
Constraint Violating Yak Guru

272 Posts

Posted - 2007-03-06 : 01:45:02

Hi all. how could i join unrelated tables?
for example:
table1: employee
dcno name address telno
1 sarah philippines 111
2 john india 222
3 joy usa 333

table2: hospital
hospname location owner
hosp1 loc1 billgates
hosp2 loc2 ann
hosp3 loc3 love
hosp4 loc4 vic

table3: student
studname load
kim 10

Is it possible to join unrelated tables?

Thanks
-Ron-

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-03-06 : 01:46:58

Yes. But you have to tell us what is your expected result

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-06 : 01:47:46

You most probably will create cartesian products, or CROSS JOINs.

Peter Larsson
Helsingborg, Sweden

cwtriguns2002
Constraint Violating Yak Guru

272 Posts

Posted - 2007-03-06 : 01:55:47

OK.

considering table1 and table3 only.

expected result:

name address telno studname load

sarah philippines 111 kim 10
john india 222 kim 10
joy usa 333 kim 10

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-03-06 : 01:58:37

Yup. As Peter said. A cross join


select name, address, telno, studname, load
from   employee cross join student

cwtriguns2002
Constraint Violating Yak Guru

272 Posts

Posted - 2007-03-06 : 02:00:34

Ohhh a simple as that?

thanks KH and Peter.
-Ron-

cwtriguns2002
Constraint Violating Yak Guru

272 Posts

Posted - 2007-03-06 : 02:04:26

I have a difficulty in cross joining.
how could i cross join this 2 separated queries?

select a.*, (a.HospStreetBldg1 + ', ' + a.HospStreetBldg2 + ', '+ a.HospTownCity + ', ' + a.HospProvince) as Address, getdate() as datenow from appsysconfiggeneral

select D.fullname, P.religion, E.empno from pspersonaldata as P
inner join hremployees as E on P.dcno = E.empdcno
inner join psdatacenter as D on D.dcno = E.empdcno
where P.religion =@RELIGION

thanks
-Ron-

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-03-06 : 02:10:57

[code]
select *
from
(
select a.*, (a.HospStreetBldg1 + ', ' + a.HospStreetBldg2 + ', '+ a.HospTownCity + ', ' + a.HospProvince) as Address, getdate() as datenow
from appsysconfiggeneral a
) a
cross join
(
select D.fullname, P.religion, E.empno from pspersonaldata as P
inner join hremployees as E on P.dcno = E.empdcno
inner join psdatacenter as D on D.dcno = E.empdcno
where P.religion =@RELIGION
) b
[/code]

cwtriguns2002
Constraint Violating Yak Guru

272 Posts

Posted - 2007-03-06 : 02:16:59

It has errors.
these are the errors:

Msg 107, Level 15, State 1, Line 1
The column prefix 'a' does not match with a table name or alias name used in the query.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "a.HospStreetBldg1" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "a.HospStreetBldg2" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "a.HospTownCity" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "a.HospProvince" could not be bound.
Msg 8155, Level 16, State 2, Line 1
No column was specified for column 1 of 'a'.

thanks.
-Ron-

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-03-06 : 02:21:35

i have edited my post. Your original query did not defined the alias 'a'

cwtriguns2002
Constraint Violating Yak Guru

272 Posts

Posted - 2007-03-06 : 02:22:29

Oh great! thanks KH.

-Ron-

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-06 : 02:27:35

Make use of derived tables.

SELECT *
FROM (query1) AS x
CROSS JOIN (query2) AS y

Peter Larsson
Helsingborg, Sweden