|
|||||||||
|
|||||||||
| |||||||||
|
|||||||||
|
|||||||||
| |||||||||
|
|
|
| |||||||||
 |
|
|
«
Previous Thread
|
Next Thread
»
|
Thread Tools | Search this Thread | Rate Thread | Display Modes |
Dev Shed Forums Sponsor:
|
|
Be the architects of evolution and help create the mobile internet future. It’s your move---enter to win here! |
|
#1
June 10th, 2004, 11:45 AM
|
|||
|
|||
|
Joining 1 table?
Can you join 1 table to itself?
Say i have a column called Age in the Person table. I have another table called Location with a column: City. I want to join the two tables but for each row i want two columns of Age based on two different conditions, and one column with city. Can you do this? If so could you show me an example please?
|
|
#2
June 10th, 2004, 12:26 PM
|
|||
|
|||
|
Ya, just like any other join, meaning that you just need to decide on the criteria to join on...my example joins on the age...
Code:
select * from Person P join (select * from Person) as P1 on P.Age = P1.Age
|
|
#3
June 11th, 2004, 07:14 AM
|
||||
|
||||
|
null, that's a pretty weird way to write a self-join
 savemyself, you need to be more explicit in your question you have Age in the Person table, and City in the Location table, and you want to join them and show two columns of Age and one column with city the conditions that allow you to match rows should replace "foo" in the following Code:
select City
, p1.age as p1age
, p2.age as p2age
from Location
inner
join Person p1
on Location.foo1
= p1.foo
inner
join Person p2
on Location.foo2
= p2.foo
|
|
#4
June 11th, 2004, 02:50 PM
|
|||
|
|||
|
your gonna have to explain that one my freind, why is it weird?
|
|
#5
June 11th, 2004, 03:38 PM
|
||||
|
||||
|
because most people would write it like this --
Code:
select *
from Person P
inner
join Person as P1
on P.Age = P1.Age
or like this -- Code:
select *
from Person P
, Person as P1
where P.Age = P1.Age
your version won't work in mysql before 4.1 (not that this is relevant in this forum)
|
|
#6
June 13th, 2004, 02:16 PM
|
|||
|
|||
|
ahh ok, I see now...thx.
|
|
| Viewing: Dev Shed Forums > Databases > MS SQL Development > Joining 1 table? |
| Thread Tools | Search this Thread |
| Display Modes | Rate This Thread |
|
|
 |
|
|
Del.icio.us
Digg
Google
Spurl
Blink
Furl
Simpy
Y! MyWeb
Linear Mode
