MYSQL :: Selecting From Multiple Rows??

Select / Insert Multiple Rows As A Single Row Of Multiple Columns
I have a nice database set up that contains information about orders and the items on those orders. If an order has 10 items on it, I can select the item data which returns 10 rows of data (let's say 5 colums each). Beautiful!

Now I find myself needing to satisfy a program that requires all of the data on a single row. I can do this in a higher level language, but if I could accomplish it all in mysql it would be better.

I don't need to sum or do any calculations. I just want to select those 5 columns of data about those 10 rows worth of items as a single row with 50 columns.

For example, I'd want this:
1-1,1-2,1-3,1-4,1-5
2-1,2-2,2-3,2-4,2-5

To become:
1-1,1-2,1-3,1-4,1-5,2-1,2-2,2-3,2-4,2-5

The first complication is that the number of items on an order is variable, but is always at least 1 and can not exceed 20. The closest I've been able to get is to do something like:

SELECT GROUP_CONCAT(item_number,",",qty,","",description,"",",price,",",location_number SEPARATOR ",") FROM items WHERE order_number=12345

This will give me a single text string containing the value content of the INSERT query (which will need to be manipuated outside of the SQL query to pad it with NULL values for the unused items' columns etc).

Selecting Rows 10-20
Is there a way to select a certain group of 10 rows? Say, I want to have multiple pages for a huge result. I want 10 rows on each page. How would I go about getting rows 11-20 on page 2?

Selecting Rows
In my MySQL table posts, I have a field called tags. In this field, there are a bunch of words seperated by spaces (each one is a 'tag' for the posts). What I am trying to do is return all posts that have a certain tag, that tag being one of the words in the tags field.

I know how to get all rows that have an exact value of something, like all posts by a certain author, but how do I get all the rows that contain a word in the long string of tags?

Selecting Rows
Hey guys, i've got a database with allot of rows. Each row has a timestamp field, wich indicated the time that that the row was added. The timestamp field is generated by time().

Now, my question, how can I get all the rows, for specific day? So I would like all the rows from yesterday, or 12 days back, or 3534 days back.

Selecting Rows
I have a database of addresses where I need to select out all those that fall under a particular road name, ie "Fake Street".
I have the SELECT * FROM MYDATABASE WHERE part, but I am unclear on how to do the next stage. The column which holds the addresses is called "ADDRESS".
In the ADDRESS column each row has the full address (1 Fake Street, 2 Fake Street etc).
What statement can I use to select those rows which contain the street name?

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 Distinct Rows
I have a mysql query which I am trying to select distinct information with.

Here is the query:

Selecting Rows Not In Array?
I'm trying to pull a single random field/row out of a mysql database, based on records that the user has not viewed already.

I can either store an array of IDs in a session var or a cookie, but then how do I get mysql to exclude those IDs when selecting a random number?

I'd like to 'SELECT ID FROM Table WHERE !in_array($array) ORDER BY RAND() LIMIT 1' but of course the "in_array" php code won't work in a mysql query.

Is there a way to do this in mysql only, rather than having to iterate through the array and build a query with possibly hundreds of elements in the array:

WHERE ID!=# AND ID!=# AND ID!=#,

Selecting Rows Having Repeated Value
Imagine we've got a MySQL table with the following structure:

id int
name varchar(255)

The table contains the following data (id, name):
1 name1
2 name2
3 name3
4 name1
5 name2
6 name9
7 name1

Please help me with the query to select only those rows, in which "name" value occurs in this table more than once.

So I would like to have the following result:
1 name1
2 name2
4 name1
5 name2
7 name1

Selecting Rows Not Within Another Table
I have 2 tables Stores and Store_Projects that im trying to pull all
stores not in certian projects but belong to a customer. Heres the
design of each

CREATE TABLE `Stores` (
`storeID` int(10) unsigned NOT NULL auto_increment,
`storeMetro` int(11) NOT NULL default '0',
`storeManager` int(10) unsigned NOT NULL default '0',
`storeState` int(10) unsigned NOT NULL default '0',
`storeAddress` varchar(100) default NULL,
`storeCity` varchar(50) default NULL,
`storeZipcode` varchar(20) default NULL,
`storePhone` varchar(20) default NULL,
`projectStatus` varchar(20) default NULL,
`storeSupervisorName` varchar(100) default NULL,
`storeSupervisorPhone` varchar(20) default NULL,
`storeSupervisorEmail` varchar(255) default NULL,
`storeOtherContactName` varchar(100) default NULL,
`storeOtherContactPhone` varchar(20) default NULL,
`storeOtherContactEmail` varchar(255) default NULL,
`storeNumber` varchar(50) default NULL,
PRIMARY KEY (`storeID`),
UNIQUE KEY `storeID` (`storeID`),
KEY `storeMetro` (`storeMetro`)
)

CREATE TABLE `Store_Projects` (
`projectID` int(6) unsigned zerofill NOT NULL default '000000',
`storeID` int(11) NOT NULL default '0',
`billRate` varchar(10) NOT NULL default '0.0',
`associatePayRate` varchar(10) NOT NULL default '',
`repsNeeded` smallint(6) default NULL,
`storeServiceDateDay` smallint(2) unsigned default NULL,
`storeServiceDateMonth` smallint(2) unsigned default NULL,
`storeServiceDateYear` int(4) default NULL,
`receivedPayment` enum('Y','N') NOT NULL default 'N',
`invoiceNumber` varchar(25) default NULL,
`invoiceDate` date default NULL,
PRIMARY KEY (`projectID`,`storeID`)
)

I had designed the query like this

SELECT st.*, s.stateInitials, m.metroName, sta.stateInitials AS
metroState
FROM Stores AS st
LEFT OUTER JOIN CustomerUsers AS cu
ON cu.userID = st.storeManager
INNER JOIN States AS s
ON st.storeState = s.stateID
INNER JOIN MetroAreas AS m
ON st.storeMetro = m.metroID
INNER JOIN States AS sta
ON m.stateID = sta.stateID
INNER JOIN Store_Projects AS sp
ON sp.storeID = st.storeID
WHERE st.customerID = 4
AND sp.projectID != 000002

But of course thats not going to work if the store is not in the
Store_Projects table which it wouldnt be if its not in a specific
project. So I need a way to pull all Stores with a specific customerID
but is NOT in the Store_Projects with a specific projectID.

Can anyone explain how this should be done within the query? I could
do it by creating an array and then processing out all the stores but
I would much rather let MySQL do the work for figuring this out.

Selecting Recursive Rows
computers(id_computer,description,id_location);
locations(id_location,description,father);

I have this two tables: computers and locations;
In locations I can insert a location and tell which one is his father, so I can have locations inside of locations.

------------------------------------------------
id_location, description, father
------------------------------------------------
1, Main Building, 0
2, HRs, 1
3, Director, 2
4, Warehouse, 0
5, Office, 4
6, Director, 5
-------------------------------------------

When I insert a new computer, I tell in which location is that computer

-------------------------------------------------------
id_computer, description, id_location
-------------------------------------------------------
1, Fujitsu-Siemens 4200, 2
2, Fujitsu-Siemens 5300, 3
3, Fujitsu-Siemens 4200, 5
4, Fujitsu-Siemens 5300, 6
------------------------------------------------

Now I would like to select all computers from main building for example.

I can not select * from computers where id_location=1 because that would not get the computers from HRs and Director. I would like to have all computers listed.

Only Selecting Specific Rows
Fields: studentID, reviewID, date

I want to get all rows in a certain date range where there are at least 3 rows for a given studentID. In other words...

Case 1: student has written 2 or fewer reviews during the date range. Don't retrieve these rows.

Case 2: student has written 3 or more reviews during the date range. Retrieve ALL of their reviews during that time. So if they wrote 5 reviews, the query will have 5 rows for their studentID.

Any ideas for this query? So far I got the date range to work using this. I'm struggling with the 3+ reviews part.
SELECT `studentID`, `reviewID`, `date` FROM `review` WHERE `date` BETWEEN '2007-10-21' AND '2007-11-09' ORDER by studentID, `date`

Any ideas?

Selecting Data In Rows
I am trying to select some data in a column but can't seem to figure it out.

the table name is "Parameters"
I want to be able to just select the "catch_addr" from the value column so all i get is the email address for the id of 33

mysql> select * from Parameters where id = 33;
+----+---------------+--------------------------------------+
| id | parameter | value |
+----+---------------+--------------------------------------+
| 33 | bounce_mess | This address no longer accepts mail. |
| 33 | catch_addr | some@email.com |
| 33 | nonexist_mail | catch |
+----+---------------+--------------------------------------+
3 rows in set (0.00 sec)

Selecting Rows In MySQL
I have a big database with about 500.000 rows. Each row consists of five columns with various information, e.g.

a,1,1,1,1
a,2,2,2,2
a,3,3,3,3
b,1,1,1,1
c,1,1,1,1

I want to select all identical values from column 1 (e.g. the a in the above example).
Then I want to copy the data in a new table, which is going to have as below:
a, (1,1,1,1),(2,2,2,2), (3,3,3,3)...

Selecting All Rows From One Table That Is Not In The Other
What I want to do is this:

select table1.id from table1,table2 where table1.id != table2.id   ....  I wish it was possible to write like that... but it doesn't I guess I can figure out the reason but anyway how should I do it? PLEAse help... example:

table1    table2
id 1      id2
id 2
id 3

select table1.id from table1,table2 where <what to write here?>

result:
id 1
id 3

Selecting Unique Rows
I have a query that checks a list of users against a list of items and returns for example the following.

SELECT users.username, ratings.recording_uid
FROM users
INNER JOIN
ratings ON (users.uid = ratings.user_uid)
WHERE ((users.username = 'a') OR (users.username = 'b'))

which returns...

username recording_uid
a 1
b 1
a 2
b 2
b 3
a 4
b 4
b 5

How can I add to that query so that I know what user a has that user b doesn't and vice versa? eg. so that it returns

username recording_uid
a 3
b 5

Selecting A Certain Rows From A Database
if i wanted to select a certain range of rows from a database,is it possible?For example,i have a column in my database which is also a primary key and i wanted to select rows from where the primary key column is 21 - 40,would this sql statement be valid?

SELECT * FROM tbl1 WHERE id=21 TO id=40;

Selecting Distint Rows
I have a load of images in a table, and these images are split into 2 different sections within my site. Some images may appear in both sections, but most only in one or the other.
I want to be able to search the images table, and have the search results page show a list of thumbnails (this i have done already).
If an image appears in both sections of the site, I do not want to show 2 thumbnails of the same image (this is what currently happens), instead I want to show a single thumbnail, and beneath it show that it is present in both sections.
The common factor between these images that appear in both sections, is a field called image_code, which is unique to each actual image. If an image appears in both sections, it will have the same image_code.
If I use DISTINCT I will only get a single image, but I am not able to show that it appears in the other section.

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

SELECTing Rows Where 1 Field Doesn't Repeat
Let's say I have a table something like this:

id a b c
1 1 2 3
2 1 3 4
3 2 1 1
4 2 8 4
5 3 1 4
6 3 8 1

And then I want to select the rows, where "a" field doesn't repeat, so the 1st, 3rd and 5th. How do I do that?

Selecting Rows From The Past 24 Hours
I have some data rows, and they contain date entries in a datetime formatted field - e.g.:

2006-07-05 01:30:03
2006-07-05 01:46:36
2006-07-05 01:20:41

I would like to select rows from the table for the past 24 hours

It would be something like:

SELECT fldID, fldName, fldDate
FROM
my_table
WHERE
.... fldDate is within the last 24 hours
But I'm not sure how to do it.

I've used the date_format function before, but that might not help.

Then I found the date_add function - so if I did this:

SELECT DATE_ADD(&#55614;&#57150;-01-02 23:59:59', INTERVAL -24 HOUR);
It would return:

1998-01-01 23:59:59

That looks good - but I'm not sure how I can build it into my statement to only return rows where the data_format field contains data less entered in the last 24 hours.

Not Selecting Rows Where A Specified Field Is Empty
What I am trying to do in a query, is only select rows where a certain field is not empty. For example, I have a visitor logs table, with the following fields:

id | date | pageVisited | referrer

Now, I only want to select the rows where there is a referrer. Something like:

SQL Selecting Rows, Group By Increment
Lets assume the follow database:

| ProductID | Cost |
+-----------+------+
| 1 | 100 |
| 2 | 10 |
| 3 | 250 |
| 4 | 900 |
| 5 | 50 |
| 6 | 60 |
| 7 | 340 |
| 8 | 100 |
+-----------+------+

Is there an SQL function to group those values by a certain increment? For example a predefined increment of 100 would return:

| COUNT | Cost |
+-----------+---------+
| 5 | 0-100 |
| 0 | 101-200 |
| 1 | 201-300 |
| 1 | 301-400 |
| 0 | 401-500 |
| 0 | 501-600 |
| 0 | 601-700 |
| 0 | 701-800 |
| 1 | 801-900 |
+-----------+---------+

Of course, I could loop through each increment with PHP and query each increment but that could get rather slow after a while.

Selecting Rows Where Foreign Key Is Null
I have this following query:

SELECT l.NAAM, l.VOORNAAM, l.ADRES, l.LAND, l.POSTCODE, l.GEMEENTE, r.titel
FROM westhoek.wtc_aansluitingen a, westhoek.wtc_leden l, westhoek.wtc_relatietitels r
WHERE l.ID = a.lid AND l.RELATIETITEL = r.id AND a.jaar=2003 AND l.RELATIE Is Null
ORDER BY l.NAAM, l.VOORNAAM

but I also want to get the rows where l.relatietitel is null,
so in Oracle SQL it would be ... and l.relatietitel = r.id (+)....

but the (+) doesn't seem to work in mysql...

Selecting Rows From One Table With A Key That Doesn't Exist In Another.
I'm trying to select rows from one table that does not have a key in another table.

For instance:

I have a hospital table with two columns, id and name.

I have a patients table with three columns, id, name, and locationid.

Now I want to find those hospitals that have no patients. So I want to get those hospitals whos id does not equal any of the locationid's in the patients table.

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

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 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 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.

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 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+).

Selecting Rows Whose Values Begin With A Certain Character
Let's say my table just contains just one column of words:

apple
bear
banana
water
salt
sugar
cat
zebra

How would I write a select statement that selects all rows where the word begins with the letter b, s or z?

SELECT word FROM mytable WHERE word (code goes here)

Would I need a case statement for this?

Selecting Previous And Next Rows Based On One Input
let me start by what i am doing:

SELECT msgid FROM messages WHERE uid=1;
output:
+----------------------------------+
| msgid |
+----------------------------------+
| 1588ad275a80f22e6206364abbba530a |
| 37ce77bea4481ab7ed2625055512467e |
| 4bf7e4cb13590da24625821c795eb8d7 |
| 57628792ba507161e3f73daff3c972b7 |
| 91e95b3fbe9f04467023cfe84e6ed6e2 |
| e710bb38be4f3cbd05b6db594f7e8805 |
+----------------------------------+

how would i get it so if i enter a value it will return the previous and next rows.

SOME_SQL WHERE msgid='57628792ba507161e3f73daff3c972b7' AND uid=1;
output:
+----------------------------------+
| msgid |
+----------------------------------+
| 4bf7e4cb13590da24625821c795eb8d7 |
| 57628792ba507161e3f73daff3c972b7 |
| 91e95b3fbe9f04467023cfe84e6ed6e2 |
+----------------------------------+

Selecting A MINIMUM And MAXIMUM Number Of Rows.
I have a problem where I want to get no more than 10 (LIMIT) of the newest records from a table that are within a month, but I want to get at MINIMUM 3 records, all using SQL.

I can do :

-------------------
SELECT *
FROM updates
WHERE date_of_update
BETWEEN DATE_SUB( CURDATE( ) , INTERVAL 1
MONTH ) AND CURDATE( )
ORDER BY date_of_update DESC
LIMIT 0 , 10
-------------------

To limit it to the 10 updates no older than a month, but if there are no updates within a month I get nothing.

So I would like to do something like :

-------------------
SELECT *
FROM updates
WHERE ROWNUM() < 3 OR ((date_of_update
BETWEEN DATE_SUB( CURDATE( ) , INTERVAL 1
MONTH ) AND CURDATE( )) AND update_id IN (
SELECT *
FROM updates
ORDER BY date_of_update DESC
LIMIT 0, 10
))
-------------------

This code will take the 10 most recent updates and then starts pulling them out as long as the number of rows is less less than 3 or the update is within the date range and one of the 10 recent updates. The problem is that mySQL appears not to have any kind of rownum function.

Limit is a nice substitute except it only works for setting an upper bound, not a lower bound number of records.

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 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 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 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.

Selecting Amount Of Rows With Distinct Values In Fields
how do i select the amount of rows in table that have a distinct field value.
example: in my table i have a column (column a) that sometimes has duplicate values (sometimes 2 duplicates, sometimes more, sometimes no duplicates).

how do i get the amount rows in the table that have a unique/distinct value in their column a (without the duplicates rows)?

Selecting From Multiple Large Tables Quickly
My skills with MySQL typically end at "SELECT * FROM table WHERE stuff", so I've had a lot of trouble with optimizing this query. I've tried doing multiple select statements for this, but that usually comes out with an average execution time of 99s. This method has a execution time of 6s still, so I'd like to get it down.

I have 4 important tables here (I'm only posting the columns which I use):

`auth`

UID mediumint(8) unsigned PRI NULL auto_increment
username varchar(32) MUL
clan_id mediumint(8) unsigned MUL 0
clan_abbrev varchar(7) MUL
`clans`

ID mediumint(8) unsigned PRI NULL auto_increment
Abbrev varchar(7) MUL
Name varchar(64)
icon_id smallint(5) unsigned 0
`stats`

UID int(10) unsigned PRI 0
kills mediumint(9) NULL
`usernames`

UID int(10) unsigned 0
ID int(10) unsigned PRI NULL auto_increment
Name varchar(32)
Uses mediumint(9) 0
`auth`, `stats`, and `usernames` are linked by `UID`. `clans` is linked to a row in `auth` based on it's `clan_id`.

I need to search the `usernames` table for a `Name` which matches a certain text (I use "storm" in my example below). I need to return a result with matches to that search, with each row containing:The `UID` which links the tables together for that user.The matching `Name` from `usernames`The matching `username` from `auth`The `Uses` from `usernames`.The `clan_id` from `auth`.The `Abbrev` from `clans`.The `Name` from `clans`.The `icon_id` from `clans`.The `kills` from `stats`.
The result should be ordered by `Uses` (from `usernames`) -- highest to lowest.

The tricky part is that some users may have Ɔ' as their `clan_id` in the `auth` table, in which case the clan id, abbrev, name, and icon_id should all be blank (0 or '' based on the type).

Here's what I managed to hammer out with my limited knowledge of SQL:

(
SELECT usernames.UID AS `UID` , usernames.Name AS `result` , auth.username AS `playername` , usernames.Uses AS `Uses` , auth.clan_id AS `clan_id` , clans.Abbrev AS `clan_abbrev` , clans.Name AS `clan_name` , clans.icon_id AS `clan_icon` , stats.kills AS `kills`
FROM `usernames` , `auth` , `clans` , `stats`
WHERE usernames.Name LIKE '%storm%'
AND auth.UID = usernames.UID
AND clans.ID = auth.clan_id
AND stats.UID = auth.UID
)
UNION (

SELECT usernames.UID AS `UID` , usernames.Name AS `result` , auth.username AS `playername` , usernames.Uses AS `Uses` , 0 AS `clan_id` , '' AS `clan_abbrev` , '' AS `clan_name` , 0 AS `clan_icon` , stats.kills AS `kills`
FROM `usernames` , `auth` , `stats`
WHERE usernames.Name LIKE '%storm%'
AND auth.UID = usernames.UID
AND auth.clan_id =0
AND stats.UID = auth.UID
)
ORDER BY `Uses` DESC
It works great, but the average query takes around 6s (these tables have several thousand entries in each).

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)

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.