2 tables, 1 solution :)

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
romeo
Forum Contributor
Posts: 138
Joined: Sun Apr 21, 2002 12:50 pm

2 tables, 1 solution :)

Post 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)
Rob the R
Forum Contributor
Posts: 128
Joined: Wed Nov 06, 2002 2:25 pm
Location: Houston

Post 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.
romeo
Forum Contributor
Posts: 138
Joined: Sun Apr 21, 2002 12:50 pm

Post 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
User avatar
cactus
Forum Regular
Posts: 343
Joined: Tue Jun 10, 2003 4:16 am
Location: UK

Post 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,
User avatar
releasedj
Forum Contributor
Posts: 105
Joined: Tue Jun 17, 2003 6:35 am

Post by releasedj »

Code: Select all

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