MYSQL :: Selecting From Multiple Large Tables Quickly

Large DB Should Use Multiple Tables?
I have a social networking site similar to myspace, currently my table which links who is who's friend on my site is 2 weeks old and is at 300,000 rows but is only at 15mb in size, this is the most accessed table of all my tables.

My question for performance, as this table grows should it eventually make a new table? If so here is my current select code

select userid,friendid,status from friend_friend where userid='$temp[auto_id]' and status='1'

Could someone give me an example of how o select this from multiple tables if I add more for this table in the future?

Selecting Across Multiple Tables
I currently have 3 tables that I want to select data from: categories, calendars, pages. Each table has a parent field (its parent category) and an order field (its order in the menu). What I want is a query that will search all 3 tables and return one ordered resultset of categories, calendars, and pages.

I tried playing around with some queries, but couldn't really figure it out.

Selecting From Multiple Tables
I am a beginner when it comes to mysql and have really gone out of my own depth! I am sure it isnt too hard I just cant seem to figure it out!

OK here is what I am trying to achieve.
lets say I am on a page where the category_id of the current page is 2.
I want to select product_id, product_model, product_image, product_price from the Products table where the product is in the same category as the current page, and the product must be featured.
I have come up with this, but it brings back nothing.

SELECT products.products_id, products.products_model, products.products_image, products.products_price FROM products, featured, products_to_categories WHERE products_to_categories.categories_id=2 and products.products_id=featured.featured_id

Here is the structure of the tables I am using.

featured

featured_id
products_id
featured_date_added
featured_last_modified
expires_date
date_status_change
status
products_to_categories

products_id
categories_id
Products

products_id
products_quantity
products_model
products_image
products_price
products_date_added
products_last_modified
products_date_available
products_weight
products_status
products_tax_class_id
manufacturers_id
products_ordered

Selecting From Multiple Tables
I am trying to select from three different tables. I can select from two of the tables, but once I add another table into the WHERE clause, there are no results.

here is my query:

SELECT k.keyword, k.keyword_id, l.label, uk.searchable, uk.displayable, uk.profile, uk.comment

FROM keywords as k, user_keywords as uk, labels as l

WHERE uk.user_id = $userid AND uk.keyword_id = $id AND k.keyword_id = $id AND l.label_id = uk.label_id";

What I am trying to do is get the name of the label (l.label) from the labels table where the label_id in the labels table is the same as the label_id that is in the user_keywords table. I have done this before successfully. This time, all of the other ids are provided (user id and keyword id), but we must distinguish what the label id is in the scope of the MySQL statement.

Selecting From Multiple Tables
I am running MySQL 4.1.20, but use a PHP front end mostly, so I'm not too familiar with the mysql command line options.

I have a database with ~20 tables, which are data stamped. I'd like to do a select or delete statement that uses multiple tables.

I know I can do
select * from t1, t2, t3 where field= 'value'

But since the table names change, is there a way to do some kind of wildcard expression for table names? Something like..

select * from t* where field= 'value'

Selecting From Multiple Tables
I want to have a better faster query to select rows from multiple tables and sorting them by date and time limit 20, currently im using the following query, but it takes LONG time and lots of resources and sometimes mysql crashes :
Code:

SELECT id, name, type, date, time from table1 Union select id, name, type, date, time from table2 union select id, name, type, date, time from table3 union select id, name, type, date, time from table4 union select id, name, type, date, time from table5 union select id, name, type, date, time from table6 union select id, name, type, date, time from table7 union select id, name, type, date, time from table8 union select id, name, type, date, time from table9 union select id, name, type, date, time from table10 union select id, name, type, date, time from table11 order by date DESC, time DESC LIMIT 0,20.

Selecting Two Values From Multiple Tables
My apologies for making this post, i'm sure that this question has been answered before, but for the life of me I just don't know what keywords to use.

Two tables, sold_items (buyer_id, seller_id) and users (id, nick).

My objective is to loop through the sold items for a particular buyer_id, and retrieve the buyer and seller username (nick).

The problem is that I don't get how I can select both nicknames when as far as I know, I can only join the buyer_id OR seller_id to the user table.

This is how I would do it normally

PHP

$query = mysql_query("SELECT s.buyer_id, s.seller_id, u.nick FROM sold_items s, users u WHERE s.buyer_id = u.id");

while ($row = mysql_fetch_array($query))
{
$buyer_nick = $row['nick'];
$seller_nick = mysql_result(mysql_query("SELECT nick FROM users WHERE id = ".$row['seller_id']),0);
}

As you can see this creates a query inside the loop, but how could I select both nicks in a single query?

Selecting Data From Multiple Tables
I have three related tables. tv_observersation, which contains stationID with the start time and end time for a tv station program viewed (the stationID isn't a key field as the same station can be viewed different times). A stations table, which contains the stationID and the station_Name, and an advertisement table, which contains a list of advertiserID and which stationID they are subscribed too (a one to many table).

I am trying to generate a query, that would take out info i.e, the info from only stations that a specific advertiser has subscribed to in the advertisment table, but I am getting duplicate info in the query results from the observations table for a specific table. I think it may be a problem with how I am using the select statement. Do I want to select from two tables observations, stations like I am below, or should I use the other two (stations, and advertisments) with the inner join statement.

The objective is to retrieve only the stations that a advertiser is subscribed to from the observations table. Code:

Selecting Rows From Multiple Tables
If i have 2 tables with a column 'users'
is there a way to select table1.users from table1 where table1.users is not in table2.users?

Selecting From A Large Table In Perl
I have a large table with 4 million rows. I need to do an operation on each of the rows. I am using Perl.

When I use the following code on a small table, it works just fine but on the large table, it gets stuck on line 3. As soon as it executes line 3, my hard drive starts going and keeps on going with no end. It never reaches line 4. My diagnosis follows the code fragment below:

-------------------------------------code fragment begin
1 my $sql = 'select myColumn from myTable';
2 my $sth = $dbh->prepare($sql) || die("dbh prep failed");
3 $sth->execute() || die("execute sth failed");
4 my $colValue;
5 $sth->bind_columns($colValue);
6 while ($sth->fetch()) {
7 do something to $colValue;
8 }
-------------------------------------code fragment end

I think this is because mySQL is trying to load all 4 million rows into memory. Is there a way, perhaps a command line switch, to delay this so that it processes one row at a time. I have tried adding SQL_NO_CACHE to my SELECT statement but that did not make any difference at all.

On a related note, when I was trying to export the four million rows using mysql command line, I had a similar problem but I found the -q command line switch for mysql command line (From the manual: -q or --quick means "Don't cache result, print it row by row.")

I hope there is something similar for my Perl problem above too. Or am I doing something completely wrong in the way I am doing this? I am a newbie to mySQL.

Selecting Random Rows In A VERY Large Table?
I would like to select random rows in a very large mysql table but I don't want to use something like that:

"SELECT * FROM tablename
ORDER BY RAND() LIMIT 1000";
Because it's horribly slow when you have a large table like mine (6 million rows+).

Question On Selecting From A Large Table In Perl
I have a large table with 4 million rows. I need to do an operation on each of the rows. I am using Perl.

When I use the following code on a small table, it works just fine but on the large table, it gets stuck on line 3. As soon as it executes line 3, my hard drive starts going and keeps on going with no end. It never reaches line 4. My diagnosis follows the code fragment below:

-------------------------------------code fragment begin
1 my $sql = 'select myColumn from myTable';
2 my $sth = $dbh->prepare($sql) || die ("dbh prepare failed ");
3 $sth->execute() || die ("execute sth failed");
4 my $colValue;
5 $sth->bind_columns($colValue);
6 while ($sth->fetch()) {
7 do something to $colValue;
8 }
-------------------------------------code fragment end

I think this is because mySQL is trying to load all 4 million rows into memory. Is there a way, perhaps a command line switch, to delay this so that it processes one row at a time. I have tried adding SQL_NO_CACHE to my SELECT statement but that did not make any difference at all.

On a related note, when I was trying to export the four million rows using mysql command line, I had a similar problem but I found the -q command line switch for mysql command line (From the manual: -q or --quick means "Don't cache result, print it row by row.")

I hope there is something similar for my Perl problem above too. Or am I doing something completely wrong in the way I am doing this? I am a newbie to mySQL.

Selecting Multiple
using SELECT with LIMIT with a query I can get a single row of a table to display but is there a way to get multiple rows non consecutive to display. For example i need rows 0, 85, 170, and 255 the multiples of 85 starting with 0. I am trying to make a new table from this data

TABLE 1 -------------Table 2

A1 A2 A3 A4
B1 B2 B3 B4 ---> A1 A2 A3 A4
C1 C2 C3 C4 ---> C1 C2 C3 C4
D1 D2 D3 D4 ---> G1 G2 G3 G4
E1 E2 E3 E4
F1 F2 F3 F4
G1 G2 G3 G4
example using multiples of 3

The webpage is being created with php and database is MySQL4

How To Quickly Find Random Records
I've got a table of 117,000 records and I want to return 5 random records. I've tried:

"select * from table_name order by rand() limit 5;"

but it's too slow, requiring perhaps 2 seconds to execute. Is there a faster way to do this?

Quickly Search Full Rows
I'm building a search thingy and some of my SQL looks like this right now:
Code:

SELECT title,description,link FROM table WHERE title LIKE '%foo%' OR description LIKE '%foo%' or link LIKE '%foo%'

I am thinking it might be faster to use a join or a view or something to search a whole row, but I am fairly new to SQL. How to quickly search full rows?

Very Large Tables
I am looking at using mysql to store some particle physics data. So the idea is (for example) I would have a table with a list of events, then a table with a list of particle types (one event can have many particles) and so on. The problem is that quite often I want to calculate something for every single event, but this table might run into 10s of millions of events and many GB. Obviously it would be mad to load the whole result set into memory then iterate over it. My question is: is there any way to have the db push results to the users one (or a few) at a time in an effiecent way? I was thinking of something like an iterator that pulls the data from the disk one (or few) at a time. An alternative (but much less useful) method might be to hand the function I want to calculate to mysql, for example:
select myfunc(x,y,z,a,b,c) from events where energy>3;
But as far as I know there is no way of defining your own functions in SQL

Adodb - Selecting Multiple Ids Via IN ( ... )
i am new to using adodb, but it is quite a good solution...

so, what i am used to do, if i have to select more rows by their id's i write

"SELECT * FROM table1 WHERE id IN (1,2,3,4,5)"

how can i do this via Execution in adodb (http://phplens.com/adodb/) ?

if i pass array, adodb starts complaining, if i implode it, it adds quotes around the string... any help?

$ado -> Execute("select * from table1 where id IN (?)", array(array(1,2,3,4,5)) ) //this does not work:(

Selecting From Multiple Rows??
i have made a voting script... every time someone votes it goes into a table called 'voting'... since people can vote on multiple things in the site there are multiple instances of each user in the 'voting' database...

if each entry has a 'userID' a 'ratingGiven' and a 'objectBeingVotedOn' field
how would i go about grabbing say the "ratingGiven" field from all of one particular user's entries .

Selecting Min,max,avg Of Multiple Columns
I have to select "min, max, avg, sum( if( somecolumn = 123, 1, 0 ) )" from 5 to 15 columns so far, each query is for different table. Is there any other way of doing that except writing all the columns to query ? Of course that job does the script, but still, query looks miles long.

Example of table:

CREATE TABLE `example` (
`ID` int(255) NOT NULL auto_increment,
`some_int` int(255) NOT NULL default '0',
`some_int2` int(4) NOT NULL default '0',
`create_time` date NOT NULL default '0000-00-00',
`some_int3` decimal(65,2) default NULL,
`some_int4` int(255) NOT NULL,
`some_int5` int(255) NOT NULL,
`a_somecolumn` decimal(65,2) default NULL,
`a_somecolumn2` decimal(65,2) default NULL,
`a_somecolumn3` decimal(65,2) default NULL,
`a_somecolumn4` decimal(65,2) default NULL,
PRIMARY KEY (`ID`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

So, I have to select these "min,max,avg,sum" from all the a_somecolumn.

How To Update/Delete Bunch Of Rows Quickly?
I have a textarea that allow user to edit a bunch of rows of data quickly. It either add/delete/update rows. How do I do it quickly? The db table has rows of names.

e.g.
the db table has:
car |Susan
dog |John
monkey |Chris

John enters:
plane
boat
house

After processing, the db table should say
car |Susan
plane |John
boat |John
house |John
monkey |Chris

How do I update the db table quickly? If I use update query, it will update John's rows but there is no rows to update? There are only rows to add and delete at this time. I don't want to delete all records of John every time John enters the data, because a user can potentially have a lot of rows in the table.

Managing Large Tables
I recently inherited a database (version 5.0.18) that has 1 table constantly growing out of hand between 10GB -30GB, therefore making is difficult/impossible to query. Also, the Archive Storage Engine is not installed. Currently, the table is manually renamed (i.e. tablename_daterange) and a new table created. ....

Large Number Of Tables
I would like to ask whether it is feasible (or whether it makes sense) to run a PHP script that creates a number of tables for each user.
Basically, this script allows users to sign up for a free service, and for each user that signs up, the script creates 9 tables in the database to contain that user's details.
Is this a sensible way of containing user data, and what are the inherent problems this may cause especially when say are 1000 users signup?

Selecting Totals For Multiple Dates
I have a form where a user can input two dates and I want to get a sum of the day's data for each of the days separately.

So far the closest I've come is:

// to display the total for one day
SELECT sum( hplmnmoc )
FROM `inRtccCallType`
WHERE host='wilsle03'
AND date='2007-05-25'

OR

// to display the total for all days
SELECT sum( hplmnmoc )
FROM `inRtccCallType`
WHERE host='wilsle03'
AND date BETWEEN '2007-05-24' AND '2007-05-31'

With what I have so far I can either display one day's total or else a total for the whole period. Can anyone tell me how to get the totals for each day individually without having to perform multiple queries.

Selecting Record From Multiple Computers
multiple client computers and 1 server computer - server holds a 'jobs' database with records that need to be processed

The client computer connects to server, and requests the next record. After it does the select statement to get the next record it does an update to mark that record as 'in progress' and no other client computer are supposed to take that record.

Before that can happen the second client also requests a job and gets the same one (the update has not happened yet or too slow and each client gets the same record) and each client is supposed to get a unique record.

Does anyone know if it's possible to prevent this? mysql table locking or row locking?

Selecting Records Containing Multiple Values
I want to query a database for records with which the field 'network_letter' contains G,R,M, and Q and display those results back. Is there an easier way than doing the following:

$query1 = mysql_query("SELECT * FROM global_pops WHERE network_letter='G'");
$query2 = mysql_query("SELECT * FROM global_pops WHERE network_letter='R'");
$query3 = mysql_query("SELECT * FROM global_pops WHERE network_letter='M'");
$query4 = mysql_query("SELECT * FROM global_pops WHERE network_letter='Q'");

and then merging those results into one variable and printing them?

Selecting Only The First Message From Each Of Multiple Users
I have a table of cellphone text messages from users. Columns include (among others) TimeStamp (DATETIME), MessageText (VARCHAR(140)), and PhoneNumber (CHAR(10)). I want to select only the first message received from each number in a given week. What query would I use to do this? I tried SELECT DISTINCT, but that seem to only apply to an entire row, not individual columns.

Slow Join On Large Tables
I have two tables:
D (500,000 recs), and DL (2,500,000 recs)

D has a PK and an index on HLQ. DL has a PK and an index on ID.

The following SQL:
SELECT
HLQ as "HLQ",
count(*)
FROM
D, DL
WHERE
D.DLID=DL.ID
GROUP BY HLQ

produces the following explain:
tabletypepossible_keyskeykey_lenrefrowsExtra
DALL500000Using where; Using temporary; Using filesort

DLeq_refIDID4D.DLID1Using index

The query takes ~ 3:30 on a Athlon xp2200; 1GB RAM; default bufer
settings.
Adding the following buffer settings only slightly decrerased the time
(~3:00).
key_buffer=512M
table_cache=256
sort_buffer=16M
read_buffer_size=16M

It appeasrs that the 'Using filesort' on table D is due to the Group
By clause and is the problem. I have an index on HLQ. Is there any
way to get MySQL to use it?

Large Tables, Very Slow Deletes.
I've been using MySQL for a while now and are starting to run into limitations. Either my own, or something else.

I have this really large table, it stores images, it has a mediumblob field, an unsigned id integer field and a timestamp field. This table contains up to a few million rows and is constantly filled real time. It grows up to sizes between 50 and 100 gb and in the future probably even larger.

This data is not supposed to be stored for all time, therefor when a certain treshhold is reached, the oldest 5000 rows are deleted to make room for newer rows. This is where my problem kicks in. Whenever I try to delete those rows, it can take for ever to complete. 500 rows I can delete without problem, 2500 becomes slow, 5000 rows takes several seconds and 10.000 rows or more makes it looks like things are frozen.

Now the temporary solution I have is to delete 500 rows at time, but this means I must do that every 10 seconds just to maintain status quo. I would much rather check every 5 minutes or so, and if needed delete 15.000 rows in one go.

I use MyISAM tables and tried setting the key size to 128M, no luck. I run this on a dual Opteron system with a GB of memory and WinXP-SP2 Proffesional.

One Large Table Or Many Small Tables?
I'm trying to decide whether to use one large table or many small tables.
I need to gather information from various devices (about 500). Each device
has its own Id and some data.

Should I use only one table with an indexed column for the ID and another
column for the data, or should I use 500 tables each with only one column
for the data?

Slow Join On Large Tables
I have two tables:

D (500,000 recs), and DL (2,500,000 recs)

D has a PK and an index on HLQ. DL has a PK and an index on ID.

The following SQL:

SELECT
HLQ as "HLQ",
count(*)
FROM
D, DL
WHERE
D.DLID=DL.ID
GROUP BY HLQ

produces the following explain:
tabletypepossible_keyskeykey_lenrefrowsExtra
DALL500000Using where; Using temporary; Using filesort

DLeq_refIDID4D.DLID1Using index

The query takes ~ 3:30 on a Athlon xp2200; 1GB RAM; default bufer settings. Adding the following buffer settings only slightly decrerased the time (~3:00).

key_buffer=512M
table_cache=256
sort_buffer=16M
read_buffer_size=16M

It appeasrs that the 'Using filesort' on table D is due to the Group
By clause and is the problem. I have an index on HLQ. Is there any
way to get MySQL to use it?

Join Tables On A Large Database 200 Meg
i am trying to work out the most efficient way to list say multipl=
e categories of entries, the database is quite large about 200 meg.=20

I would like to know if using join tables is more efficient than storing th=
e keys in a varchar field then within the second loop doing a where in (1,2=
,3,4) where the 1,2,3,4 are the stored category keys in the varchar field =
rather than a where in (1), where 1 is the pirmaryID of the entry for insta=
nce ?

Optimizing Mysql For Large Tables
I have been working with Mysql for about 5 years - mainly in LAMP shops. The tables have been between 20-100 thousand records size. Now I have a project where the tables are in the millions of records.

This is very new to me and I am noticing that my queries are really slowwwwww!

What are the options that I have to speed my queries on the mysql side with regards to the my.cnf file. I have a fair understanding of sql optimization and I understand explain. I just want to see if there is something that I can do with the server also.

Optimizing Mysql For Large Tables
I have been working with Mysql for about 5 years - mainly in LAMP shops. The
tables have been between 20-100 thousand records size. Now I have a project
where the tables are in the millions of records.

This is very new to me and I am noticing that my queries are really
slowwwwww!

What are the options that I have to speed my queries on the mysql side with
regards to the my.cnf file. I have a fair understanding of
sql optimization and I understand explain. I just want to see if
there is something that I can do with the server also.

Thanks to all.

#Joseph Norris (Perl - what else is here?/Linux/CGI/Mysql)
print @c=map chr $_+100,(6,17,15,16,-68,-3,10,11,16,4,1,14,-68,12,1,14,8,
-68,4,-3,-1,7,1,14,-68,-26,11,15,1,12,4,-68,-22,11,14,14,5,15,-90);

One Large Table Vs. Many Small Tables
I'm working on a design using PHP & MySQL and I'd like to get some opinions on this.

My design has several tables that will be referenced but I'm wondering if those tables should be broken down even more and referenced more dynamically. The reason that I wonder about this is for long term goals. I hope that eventually there will be two or three thousand records that will be used on a regular basis. These records will need to be separated into groups, but I'm not sure if I should use a field in the database table or create a new table for each group.

If a few hundred records could be in each group, do you think it's better to use one large table with a field for the group ID, or a new table for each group?

Can't Populate Large Tables Through PhpMyAdmin
I'm having trouble woth Yahoo php hosting.
My client wants to move his websites from H-Sphere reseller company where (everything works fine) to YAHOO (he thinks that service and reliability will be much better?).
The problem is with 'products' table where are 15000 records.
Yahoo's phpMyAdmin times out if I try to upload sql/txt file (3.5 mg). I can insert smaller tables pasting script into SQL window - no problem with 4000 records)
I can't do anything with products table (15000 records).
I did not have this problem with our current hosting company.
Under the upload window it says that txt file can be up to 10 mb but mine is only 3.5 mb.

One Large Table Or Several Smaller Tables
From a speed of access standpoint, is it better to have one large table or several smaller tables? I'm in the early stages of development, so I can go either direction with this.

SELECTing Distinct Values Over Multiple Columns
I have a table setup for populating a drop down menu.

CREATE TABLE catagory
( ID char(10) PRIMARY KEY NOT NULL,
cat1 varchar(50),
cat2 varchar(50),
cat3 varchar(50) );

Now each row is for one client to populate so that he can be listed under multple catagorys.

The problem comes in that I need DISTINCT catagories but say the client with ID 00000-0001 has a cat1 of greek, and a cat2 of pizza, where on the next, a client with a different ID has a cat1 of italian, but a cat2 of pizza. How would I word the query so that I can return distinct results from both rows AND columns or is this a problem on the PHP side of things (how i'm coding the page)?

as it stands right now, my query of:
SELECT DISTINCT cat1, cat2, cat3 FROM catagory;
isn't working very well (I get all the results, as if the DISTINCT wasn't there)

Time Out Message When Query Large Tables
I'm trying to get data from 6 large tables but the volume of data in each table is too large and even select * from one of them make the system stop. I have afew questions:

1-what can I do to avoid the system stop or time out message?

2- To use several tabels infomation should I use 'View' command or can I use other methods?

3-I need to create a new table and insert the result from query in it. If I use the "view" can I insert the result of the view in a table?

(I use postgresql).

Speeding Up Large MySQL MyISAM Tables
I'm somewhat new to MySql. I've been using it for a while, but pretty much
out of the box setup, and am starting to suffer heavily with my larger
tables. I have a table with 5,000,000+ rows that I have to search and do
joins on. Although I have an index set up for it, the joined select will
still take some 400+ seconds to return, which is obviously unacceptable.
This is due to enormous HD access.

Perhaps someone can help me with indexes here; I was under
the impression that the index for the tables are cached in memory, and
therefore permitted "instant" searchability, without having to retrieve data
from each of the rows of the DB. Is there a startup parameter, or
something in the mysql.ini file, that must be set to allow for this? I have
the index configured properly, and have made sure that the query uses there
parameters in the where clause in the same order that they appear in the
index. Code:

Loading Large Tables From Files (wikipedia)
I'm an Oracle DBA and new to MySQL.
I was trying to load a 5.6 GB xml file onto mysql database using mediawiki tools.
The performance decreased gradually and crashed at ~60% completion.
Now I'm planning to use xml2sql tools to convert the dumps to txt format.
Then I'm planning to load this using mysqlimport.
I would like to run this load faster, and on Oracle, I would use the IMP parameters
such as INDEXES=n and so on. What are the recommended steps when using mysql?
Should I target any of the system variables?
I have a 4G ram on the server and can use all of it for the load.
Any pointers?

Selecting Multiple Columns Including Encrypted Data
Is there a way of selecting all columns from a table, including (aes)encrypted columns without having to individually name each column name->value. e.g.

SELECT * FROM table;

instead of

SELECT column1, column2, column3, AES_DECRYPT(column3, 'salt') FROM table;

I only ask because I have tables with dozens of columns and specifying every individual column will be very time consuming.

Selecting Language Depended Fields Through Multiple Table References
For a long time i think i need your instructions dear people on sitepoint. I'm making a sql query for getting user info, but i dont know how to handle this situation:

SELECT
user.name, user.firstname, user.lastname, ___ as user.city, ___ as user.province
FROM
user, languagefield, city, province
WHERE
user.id = %i
AND languagefield.language = %i
AND city.id = user.city
#tricky part
AND city.name = languagefield.id
AND city.province = province.id
AND province.name = languagefield.id
So i'm trying to get users first and last names plus city and province names from language tables. Is it possible with one query? How would you make SELECT and WHERE clauses?

Im using this db tbl scheme:

table : city
id (int4) primary key
name (int6) ref languagefield.id
province (int4) ref province.id

table : province
id (int4) primary key
name (int6) ref languagefield.id

table : user
id (int4) primary key
firstname (varchar32)
lastname (varchar32)
city (int4) ref city.id

table : language
id (int3) primary key
char2name (varchar2)
name (int6) ref languagefield.id

table : languagefield
id (int6)
language (int3) ref language.id
value (varchar255)

id + language unique

*****

I'm also thinking and planning to set more current language depended content to my application, so this is very important part of my design, 'cause it could be reused when dealing with other tables and fields.

Selecting Multiple Columnn Values Into 1 Distinct/unique List
I have a table with several fields for email (primary email, alternative email, contact email)

Some times, some of these fields will be blank and sometimes the same address will be enter for both primary and contact.

I use this table and these fields to do a mailout but I don't want to mail people twice because there address occurs in more than 1 column.

I need to end up with a list containing all email values from those 3 columns that is unique (no duplicates).

I can do this by checking each one and putting it into a php array but thought there may be a nicer way to do it in the SQL?

Extract Multiple Columns From Multiple Tables
For hours and hours I've been trying to work out how to write the sql query to extract some data from some tables, but with no luck.....

Joins With Multiple Tables And Multiple Rows
I'm making a good ol' forum, and i have three tables, users, threads
and posts. when i query my threads table with a join, i need to access
the users table twice to get the username of the first poster and last
poster. But how? I can only figure out how to get one or the other. Is
my design bad? eg

SELECT TopicID, FirstPostID, LastPostID, Replies, Views, Topic,
username FROM DiscussionThreads, users WHERE
DiscussionThreads.FirstPostID=users.ID ORDER BY FirstPostDT DESC LIMIT
10 .

Selecting From Two Tables
i got two tables, let's say

tbl1
-----
id
title
id2
----

tbl2
----
id
title
id2
----

Now i need to know if there's data in either tables with id2 is let's say 5

SELECT * FROM `tbl1`, `tbl2` WHERE `tbl1`.`id2` = '5' OR `tbl2`.`id2` = '5';

won't work.

Selecting From The Tables
i have two tables table1 contain techid,techname and table2 contains userid,username,techid.

in my webpage i displayed two combo boxes.one contains the techname(s) if i click one of the techname i want to get the usernames corresponding to the techname to display in the another combo box. by accessing the techid.