Page 1 of 1

2 tables, 1 solution :)

Posted: Mon Dec 30, 2002 11:30 am
by romeo
I am having major trouble doing something… I have 2 tables

Table 1 “DOMAINS” which has an ID number and the NAME (of the domain)
Table 2 “MAIL” which has MAIL_NAME (the users account name) and DOM_ID (the # referencing the NAME in the DOMAINS table)

What I think I want to do is concat MAILNAME@NAME to form a list of email addresses, make sense?

ANY help appreciated or do i need PHP to do this also (if i cant do it straight from mysql)

Posted: Mon Dec 30, 2002 1:02 pm
by Rob the R
You can certainly do this from within MySQL. Try this query:

Code: Select all

select concat_ws('@',m.mail_name,d.name) as email
from mail as m, domains as d
where m.dom_id = d.id
NULL values in M.MAIL_NAME, D.NAME, or M.DOM_ID may not give you the results you expect, but if NULLs are not a risk, this should work well.

Check out the MySQL documentation for concat_ws() and join syntax for more info.

Posted: Fri Jun 20, 2003 9:25 am
by romeo
help?

I have 2 tables, one has the domains_id, domain (the table is domains) and one has the emails and passwords and the domain id# (the mail table)

I thought this would work
select mail.mail_name, mail.password, domain.name from mail, domains where mail.dom_id=domains.id

Any help here is appreciated

Posted: Fri Jun 20, 2003 9:35 am
by cactus
You need to initalise your aliases (mail/domain) before you use them:

http://www.mysql.com/doc/en/JOIN.html
http://www.mysql.com/doc/en/SELECT.html

EG:

Code: Select all

SELECT t1.name, t2.salary FROM employee AS t1, info AS t2 WHERE t1.name = t2.name;
Regards,

Posted: Fri Jun 20, 2003 9:52 am
by releasedj

Code: Select all

select mail.mail_name, mail.password, domains.name from mail, domains where mail.dom_id=domains.id