Joining 2 tables...

Hi. I'm new to SQL, and need to join 2 tables... any hints?

table1:
id (int)
title(varchar(50))
body(text)

table2:
id (int)
title(varchar(50))
body(text)

somehow need to get the id, which table the record is from, and the title and body... so if the tables had the information:

table1:
id title body
1 "first title" "first body"
2 "second title" "second body"
3 "third title" "third body"

table2:
id title body
1 "first title" "first body"
2 "second title" "second body"
3 "third title" "third body"

I would like to get...

id table title body
3 1 "third title" "third body"
3 2 "third title" "third body"
2 1 "second title" "second body"
2 2 "second title" "second body"
1 1 "first title" "first body"
1 2 "first title" "first body"

Does anyone know how to get this? I am fairly flexible if i need to change things...

cheers, eh!

SELECT 'A', Id, Title, Body FROM TABLE1
UNION
SELECT 'B', Id, Title, Body FROM TABLE2
ORDER BY Id DESC, 1 ASC

Join one to many relationship query

I have 2 tables 'contacts' and 'history' I want to search 3 fields the body fields in the contacts table... and also the subject and body field in the history table all looking for like '%Winn%' and where contacts.address is not null.. I am having a heck of a time trying to do this... can anyone help please I want to return address and contactID.

GhornetI think that this should work... I am a little new at this though...

Select contact.contactId, contact.address from contacts
inner join contacts
where (history.body = contacts.body and contacts.address is NOT NULL) and history.body like '%Winn%'

Good luck.|||Sorry I meant:

inner join history|||Won't that join where the bodys are the same.. I don't want that...
Thanks

I have 2 tables 'contacts' and 'history'
I want to search 3 fields the body fields in the contacts table with like '%Winn%'
and also the subject and body field in the history table also looking for like '%Winn%'
I want to make sure that contacts.address and contacts.city is not null..
I want to return firstname, lastname, spouse, salutation, address, city, state, zip and contactID.

Ghornet|||something like this maybe:

select c.contactid, c.address from contacts c
where exists (select 1 from ((select contactid from history where subject like '%Winn%') union (select contactid from history where body like '%Winn%')) x where c.contactid = x.contactid)|||oh, and of course:

...and c.address is not null|||hmmm, I think I missed the body field in the contacts table. Anybody?|||How do they join? By contactID? If so, also, you mention the columns (there are no fields in relational theory, but we all know what you mean) you want to return but not from which table so I assume from the contacts table.

select a.firstname, a.lastname, a.spouse, a.salutation, a.address, a.city, a.state, a.zip a.contactID
from contacts a
join history b on a.contactid = b.contactid
where b.subject not '%Winn%' and
a.body like '%Winn%' and
b.body like '%Winn%' and
a.address is not null
and a.city is not null

Did I miss anything?|||i think that is what I want thanks a bunch for the help

select a.firstname, a.lastname, a.spouse, a.salutation, a.address, a.city, a.state, a.zip a.contactID
from contacts a
join history b on a.contactid = b.contactid
where b.subject like '%Winn%' or
a.body like '%Winn%' or
b.body like '%Winn%' and
a.address is not null
and a.city is not null|||I think it is not.

Better put some parenthesis around that complicated WHERE clause just to be sure:

select a.firstname, a.lastname, a.spouse, a.salutation, a.address, a.city,
a.state, a.zip, a.contactID
from contacts a
inner join history b on a.contactid = b.contactid
where (b.subject like '%Winn%' or a.body like '%Winn%' or b.body like '%Winn%')
and a.address is not null
and a.city is not null

blindman