QUERY in 2 TABLES without relationship

Forum Moderators: open

Message Too Old, No Replies

QUERY in 2 TABLES without relationship

camilord

3:16 pm on Jun 11, 2007 (gmt 0)

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

9:32 pm on Jun 11, 2007 (gmt 0)

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

Demaestro

9:45 pm on Jun 11, 2007 (gmt 0)

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

2:06 pm on Jun 13, 2007 (gmt 0)

THANKS Demaestro!

it helps a lot..

:bow:

physics

1:38 am on Jun 14, 2007 (gmt 0)

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.