[SOLVED] Select All Values From Two Tables

[The Little Guy]

How can I select all values from a database from two tables, where

 

they equal a value?

 

Here is what I have

SELECT * FROM email_emails, email_outbox WHERE email_emails.`to` = 'admin@dudeel.com' AND email_outbox.`from` = 'admin@dudeel.com'

 

It is returning not what I want, so... How can I fix it?

[JasonLewis]

Okay, my MySQL isn't to flash but here is how I would do it.

 

SELECT * FROM email_emails e, email_outbox o WHERE e.`to` = 'admin@dudeel.com' AND o.`from` = 'admin@dudeel.com'

 

Basically the same, see what happens though.

[toplay]

Ask yourself which is the common column in both tables that will tie the two tables together?

 

select

        *

from  email_emails e

join    email_outbox o

on    e.to = o.from      # specify the column link between the two tables

 

where e.to = 'admin@dudeel.com'

 

[The Little Guy]

email_emails.`to`

and

email_outbox.`from`

 

using your code from above, doesn't work.

[toplay]

The Little Guy, when posting please put more details than just "doesn't work". You're not really giving us enough info on what you do want or expect as output/result. Show us example of data, and example of the result you want/expect.

 

The query in your original post has two tables, but the "where" clause has no join criteria to connect/tie the two tables together. Only you know what the columns are in both table that can match rows together.

 

If you want ALL rows where the "to" column in the email_emails table matches the "from" column in the email_outbox table, then you can get rid of the "where" clause in the query in my previous post.

 

Using the "to" to match a "from" in the other table doesn't make much sense to me, but I'm using it because that's all you posted and the only thing I have to go on. Ask yourself, what is the common data from both tables that can connect them together?

 

 

[The Little Guy]

OK, I have two tables. One for outbox (email_outbox) message and one for inbox (email_emails) messages.

 

The ONLY two columns that match each other are the email_outbox.from and email_emails.to. Those two columns are the ONLY ones that match each other.

 

They both contain an email address in them. On the user interface side, if the user clicks on "All", it should show all emails that the user has send and/or received.

 

My current displays one row about 20 - 30 times.

 

What am expecting is:

10 inbox results and 8 outbox results.

 

I think I should be using a Union... but... I don't have the same number of columns in both tables.

[fenway]

I think I should be using a Union... but... I don't have the same number of columns in both tables.

You're right... so don't use "*", ask for the fields you want back so that they "line up".

[toplay]

Something like:

 

(select `to` as email_addr, `subject`, `body`, 'inbox' as type from email_emails where `to` = 'admin@dudeel.com')

union all

(select `from` as email_addr, `subject`, `body`, 'outbox' as type from email_outbox where `from` = 'admin@dudeel.com')

[The Little Guy]

But I need all the information from both tables...

 

Is there a way to make temporary columns on the fly so there is an even number of columns?

[fenway]

Is there a way to make temporary columns on the fly so there is an even number of columns?

Of course...

 

SELECT null as col1, realField as col2, null as col3, etc....

[toplay]

But I need all the information from both tables...

 

Is there a way to make temporary columns on the fly so there is an even number of columns?

Yes, what fenway said, but that's why I gave you "type" to show an example of how to add a new value (wherever you want it at).

 

 

[The Little Guy]

Yes, what fenway said, but that's why I gave you "type" to show an example of how to add a new value (wherever you want it at).

 

Oops didn't see that.

 

Anyways, one more question I think. With the new row(s), can I assign them a temporary value too?

 

I would also like to auto create a field that shows what table the result came from, so I can do something with it in my php according to if it was an inbox message or outbox message.

[fenway]

Anyways, one more question I think. With the new row(s), can I assign them a temporary value too?

 

I would also like to auto create a field that shows what table the result came from, so I can do something with it in my php according to if it was an inbox message or outbox message.

For sure!

 

SELECT sharedCol1, null as col2, sharedCol3, 'outbox' as source from table1

UNION ALL

SELECT sharedCol1, col2, sharedCol3, 'inbox' as source from table2

 

[toplay]

I would also like to auto create a field that shows what table the result came from, so I can do something with it in my php according to if it was an inbox message or outbox message.

LOL ... you're kiddin right?  Once again, that's what the "type" column is for in my example!