[thelist] MySQL table joins

[Jay Greenspan]

>  <snip code>
> $db->do("INSERT INTO tblEmails (emailName,Email) VALUES('".
> sqlEncode($form{'toname'}) . "','". sqlEncode($form{'email'}) . "')")
> unless$db->selectrow_array("SELECT Email FROM tblDockets, tblEmails, 
> tblAds,
> tblAdvise WHERE Email='". sqlEncode($form{'email'}) . "' OR '".
> sqlEncode($form{'email2'}) . "'");
> </snip code>
> I have a sneaking suspicion that I cant do what I want to do.  I think I
> have the right syntax for the table references before the WHERE 
> statement
> but it all turns bad after that.  Matter of fact Im sure Im not doing it
> right.
> The MySQL docs give this example
> select * from table1,table2 where table1.id=table2.id;
> which I think is totally different to what I want.
> Your thoughts and comments would be appreciated (even more so if you 
> can do
> it with pictures)


If I read this right, you may be mistaking a "join" for a "union". A 
join brings together two tables on a primary key-foreign key 
relationship. And a union brings together rows from tables with similar 
columns. In the result of a union query you would see the results from 
one table and the results for a second table appended to those. A sample 
union query might look like:

select fname, lname, email from my_users
union
select fname, lname, email from your_users

However, MySQL 3.23.x doesn't support unions. There a couple of 
workarounds available. Try using the non-standard CREATE TEMPORARY TABLE 
syntax along with a select statement:

CREATE TEMPORARY table my_table
select fname, lname, email from my_users;

  You can then insert results from additional tables by coupling an 
insert and a select statement:

insert into my_table
select fname, lname, email, from your_users;

You can then check to see if my_table contains any rows.

select count(*) from my_table;