QUERY in 2 TABLES without relationship

[camilord]

i have a table messages and sentmessages...

messages:
MsgID int(11),
Subject varchar(100),
Message text
MsgTrash varchar(10) default 'NO'

sentmessages:
MsgID int(11),
Subject varchar(100),
Message text
MsgTrash varchar(10) default 'NO'

now my prblem is.. when i mark MsgTrash to YES...

how do i query both table and i can identify that MsgID was from messages and the other one is from sentmessages...

please help... thanks adv...

[syber]

Can you show an example of what the result would look like?

[Demaestro]

If I understand what you are asking then this is one solution:

select
..MsgID ,
..Subject,
..Message,
..MsgTrash,
..'messages' as source_table
from
..messages

Union All

select
..MsgID ,
..Subject,
..Message,
..MsgTrash,
..'sentmessages' as source_table
from
..sentmessages

This will return something like this

MsgID ¦ Subject ¦ Message ¦ MsgTrash ¦ Source

1 ¦ 'test sub' ¦ 'test message' ¦ 'NO' ¦ 'messages'
1 ¦ 'test sub' ¦ 'test message' ¦ 'NO' ¦ 'sentmessages'
2 ¦ 'sub2' ¦ 'message2' ¦ 'YES' ¦ 'messages'
2 ¦ 'sub2' ¦ 'message2' ¦ 'NO' ¦ 'sentmessages'

Then you can just check the source to see what message table the message came from.

Hope this makes sense and is the solution you are looking for.

[camilord]

THANKS Demaestro!

it helps a lot..

:bow:

[physics]

SELECT messages.MsgID as m_MsgID, messages.Subject as m_Subject, ... , sentmessages.MsgID as s_MsgID, sentmessages.Subject as s_Subject FROM messages, sentmessages WHERE ...

Then the fields will show up like m_MsgID in the results.

I don't know if you have any control over this or not but it seems to me like the database should be redesigned ... i.e. just have a field called sent and if that field is 1 then it is a sent message.