MYSQL :: Slow Join On Large Tables

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.

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 ?

Large Table, Slow Query Question
I have a table with ~800,000 records. I need to grab random rows from the table based on certain criteria. The problem is that average lowest subset to grab the random row is around 200k. Here is what I'm trying to do:

There are 4 columns: data,n1,n2, and n3. I need to get the value of the data column based on criteria using the n1-n3 columns.

The most common query is SELECT data FROM table WHERE n1 = ?

The problem is that n1 can be only 1 of 5 possiblities. When the table is finished being populated there will be roughly 1.5 million records and 250k for each value of n1. Of course, I have an index on each n column.

Right now with just the 800k records it can take over a second, sometimes multiple seconds to run the following in order to get a random row from that subset:

SELECT COUNT(1) AS total FROM table WHERE n1 = 3;
index = random number from 1 to total
SELECT data FROM table WHERE n1 = 3 LIMIT index,1;

How can I speed this up? I need it to take less than half a second if possible. Thank you.

Join Too Slow
I'm creating a query that use Join clause. I tested it in MySQL 4.0.24 and with MS-ACCESS. . . . in MySQL is slow!!! any suggestion ?

Slow Left Join
I've never come across this before, a very straight ahead query running extremely slowly...

Table1
Id | Field1 | Field2

Table2
Id | Table1Id | Field1 | Field2

Query....

SQL
SELECT Table1. * FROM Table1 LEFT JOIN Table2 ON Table2.Table1Id = Table1.IdWHERE Table2.Table1Id IS NULL

NOTE: An existing record in Table2.Table1Id is never null, it will always have a record of some description. We're just looking for missing records in Table2.

This query is taking anywhere from 6-20 seconds to run. It's really got me baffled as I thought it should run extremely quickly.

There's about 10,000 records in each table and the query appears to be producing the correct result, just extremely slowly.

Slow Left Join
Well I'm trying to do a left join on a couple tables "USERS" and "EVENT_LOG", event_log has a considerable number of rows (1.7mil). I'm trying to do a query to find out which USERS have no entries in the EVENT_LOG of a specific EVENT_TYPE.

Here's the query I'm attempting:

select USERS.USER_USERNAME, 0 as eventCount from USERS left join event_log on USERS.USER_ID=event_log.USER_ID and EVENT_TYPE='AUTHENTICATION' where event_log.USER_ID is null and USERS.CUSTOMER_ID=33700077 group by USERS.USER_ID

EXPLAIN output:
SIMPLE USERS ref CUSTOMER_ID CUSTOMER_ID 4 const 155 Using where; Using temporary; Using filesort
SIMPLE event_log ref USER_ID USER_ID 32 const 12139 Using where; Using index; Not exists

This takes 1.5sec, which is quite long considering the right join takes 31ms which is doing alot more (i.e. counting).

Right Join:

select USERS.USER_USERNAME, count(event_type) as eventCount from USERS right join event_log on USERS.USER_ID=event_log.USER_ID and EVENT_TYPE='AUTHENTICATION' where USERS.CUSTOMER_ID=33700077 group by USERS.USER_ID

I have indexes on USER_ID in the USERS table, and USER_ID/EVENT_TYPE ("USER_ID" index) in the EVENT_LOG table.

So anyone have an idea as to why this is so slow? Shouldn't it be querying the "USER_ID" index in the EVENT_LOG table and simply requesting the event type "AUTHENTICATION" and adding it to the resultset if it comes back null?

Slow Query W/ Join & Ordering
I am trying to figure out why I have a hugely slow query (~2 seconds in my testing environment). Details are below:

It involves two tables, products and vendors.

Products is a huge table, so I will only include the (ostensibly!) relevant fields in its description:

CREATE TABLE `products` (
`id` int(11) NOT NULL auto_increment,
`vendor_id` smallint(6) NOT NULL default Ɔ',
`product_code` varchar(255) NOT NULL default '',
`internal_name` varchar(255) NOT NULL default '',
`lastmodified` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,

PRIMARY KEY (`id`),
UNIQUE KEY `product_code` (`product_code`),
KEY `vendor_id` (`vendor_id`)
) ENGINE=MyISAM;
Vendors are much more straightforward:

CREATE TABLE `vendors` (
`id` smallint(6) NOT NULL auto_increment,
`name` varchar(255) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM;
The following query executes in no MORE than 0.01 seconds:

SELECT DISTINCT p.id
, p.product_code
, unix_timestamp(p.lastmodified) as lastmodified
, p.internal_name
FROM products as p
ORDER BY p.product_code ASC
LIMIT 0, 30;
And has the following attributes:

+----+-------------+-------+-------+---------------+--------------+---------+------+-------+-----------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+---------------+--------------+---------+------+-------+-----------------+
| 1 | SIMPLE | p | index | NULL | product_code | 257 | NULL | 25124 | Using temporary |
+----+-------------+-------+-------+---------------+--------------+---------+------+-------+-----------------+
When I join with the vendors table, so that I can fetch the vendor's name for each product, I use the following query, which takes about 1.88 seconds:

SELECT DISTINCT p.id
, p.product_code
, unix_timestamp(p.lastmodified) as lastmodified
, p.internal_name
, v.name as vendor_name
FROM products as p
LEFT JOIN vendors as v ON v.id=p.vendor_id
ORDER BY p.product_code ASC
LIMIT 0, 30;
It has the following characteristics:

+----+-------------+-------+--------+---------------+---------+---------+--------------------------+-------+---------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+--------+---------------+---------+---------+--------------------------+-------+---------------------------------+
| 1 | SIMPLE | p | ALL | NULL | NULL | NULL | NULL | 25124 | Using temporary; Using filesort |
| 1 | SIMPLE | v | eq_ref | PRIMARY | PRIMARY | 2 | te_inventory.p.vendor_id | 1 | |
+----+-------------+-------+--------+---------------+---------+---------+--------------------------+-------+---------------------------------+
Note the addition of the filesort. I'm unhappy enough about the temporary, which I don't really understand, but the filesort is, I'm fairly sure, killing me.

Closer investigation (or maybe just common sense if you aren't a MySQL newbie like me) shows that the ORDER BY clause is responsible, for when I join without the ORDER BY, my query time goes back down to 0.01 seconds or so:

mysql> explain SELECT DISTINCT p.id
-> , p.product_code
-> , unix_timestamp(p.lastmodified) as lastmodified
-> , p.internal_name
-> , v.name as vendor_name
-> FROM products as p
-> LEFT JOIN vendors as v ON v.id=p.vendor_id
-> LIMIT 0,30;
+----+-------------+-------+--------+---------------+---------+---------+--------------------------+-------+-----------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+--------+---------------+---------+---------+--------------------------+-------+-----------------+
| 1 | SIMPLE | p | ALL | NULL | NULL | NULL | NULL | 25124 | Using temporary |
| 1 | SIMPLE | v | eq_ref | PRIMARY | PRIMARY | 2 | te_inventory.p.vendor_id | 1 | |
+----+-------------+-------+--------+---------------+---------+---------+--------------------------+-------+-----------------+
Any clues on how I can get the execution time to go down when I am sorting? I'm also curious why MySQL is using a temporary table,

Large Join Query
I am at a loss. Right now I have a PHP script that needs to run a huge join query. They query joins two tables of approximately 200,000 records. I'm running mysql 3.x so I can't use a view... at this point indexes don't seem to help. I am thinking the only way to do this is with script processing

Slow Execution For A Left Outer Join Query
Whats likely to be the cause of slow execution for a left outer join query?

The original query joins three tables but even if I narrow it down to one it still takes a long time to execute.

$query = "select distinct materials.* from materials";
$query .= " left outer join materials_products on materials.material_id = materials_products.material_id";

There's 914 rows in the materials table and 1348 row in the materials_products table

Is it likely to take a long time for this amount of data or is there likely to be a problem in the table(s) set up or query?

Help Me Refine This Large Join Query
$sql = "SELECT f.*, p.post_time, p.post_username, u.post_subject,u.post_text,v.username
FROM (( " . phpbb_forums . " f
LEFT JOIN " . phpbb_posts . " p ON p.post_id = f.forum_last_post_id )
LEFT JOIN " . phpbb_posts_text . " u, phpbb_users v ON u.post_id = p.post_id and ON v.user_id=p.poster_id)
ORDER BY f.cat_id, f.forum_order";

Right now its select all the posts from every forum, I just want it to select the last post from every forum and the second join on phpbb_users don't seem to be working as the user_id doesn't seem to be matching on the poster_id, any suggestions?

The query also doesn't work, I want phpbb_posts to left join to two tables, posts_text and posts_topics, how to I do that?

Optimizing Join W/ Large Table
Here is a high level description of my tables

I have a table called Items
items are grouped by an ID, but it is NOT unique
I have an index on ID for the Items table

and a table called CompletedItems
completed items consolidates information about the items, and here the ID is unique.
I have unique index on ID for the CompletedItems table

What i need is a query that will return all item id's which have not been completed.

I have this query:

select distinct(items.id) from items
left join (completeditems) on
(items.id = completeditems.id)
WHERE completeditems.state is null;

this gives me the data I want except there are two problems

a) this query takes a long time to run.
while it is running, the completed items table is locked, which causes several other processes to block while this is happening.

to give you an idea of scale, the items table has about 20 million records in it, and the completed items has about 4 million.

Does Too Many Tables Slow Down Websites
Just wondering if having too many tables in one database slows down a website and would it be better to have seperate databases rather than one big one?

How Do I Rewrite A Slow Subquery Into A Fast Join? (Prolly Real Easy For A Non-noob To Answer)
I used subquerys because they made more sense to me, until the table got "a lot" of data in it (not really... just 1000 entries) - then all querys including subquerys slowed down to 4-5 secs EACH!! :) This is insanely slow.

What I am doing here below is looking into what messages a user has already read in the subquery, so that none of the ones he already has read will EVER again show up for him.

So, how do I rewrite this subquery:

NOT IN (SELECT reffen FROM $readt WHERE sender = $nr)

From this entire SELECT:

SELECT halfref, brokensms, DATE_FORMAT(arrived, '%y'), DATE_FORMAT(arrived, '%m'), DATE_FORMAT(arrived, '%d'), DATE_FORMAT(arrived, '%H'), DATE_FORMAT(arrived, '%i'), priv FROM $halft WHERE sender = $nr and halfref NOT IN (SELECT reffen FROM $readt WHERE sender = $nr) order by id desc limit 1

Into a faster join of some sort?

I'd aprichiate if you told me what each part of the rewrite actually does, because I been reading about joins for a day now and still don't get them at all!

Federated Tables Slow? (like 4.5 Hrs For A Query)
I've got a problem with federated tables. I'm using MySQL 5.1 (with InnoDB as the default table type) on a Win2K server, on which I've got four federated tables pointing at four MyISAM tables on a MySQL 4.1.11 server. Of the four tables, three of them run just fine, and I can retrieve data quickly with no problems. The fourth is a sheer pig. While they have different columns, all four tables are roughly as complex as each other, all having the same features and developed by the same team.

The most obvious difference, and what I suspect might be the problem, is that the first three tables have between 150 and 1,000 records, the fourth table has closer to 15,000. Still, there isn't that much lag when I'm pulling from the smaller tables, and the lag is really serious when I'm pulling from the larger one; I ran three queries last night to test, and I could pull data from the smaller tables in about 5 minutes, but the larger table took 4.5 hours- possibly because it was joined with two other tables, but the joins on the smaller tables didn't cause this kind of problem.

The second obvious difference is the fact that I'm pulling from a MyISAM table into a federated table... from which I would like to store into an InnoDB table, but it ends up timing out quite a bit.

Connecting to the database I've federated to isn't a problem. It responds to a PHP frontend lightning-fast. It's just my federated tables that suck so bad. I wouldn't even use federated tables, but I need to pull from the MyISAM database for storing historical records of inventory. What am I doing wrong, and what can I do to speed things up?

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

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

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?

Connecting Three Tables With Left Join And Ordinary Join
I have 3 Mysql tables:

Week (with columns day and hour)
Activity (with columns day, hour, activityid and ac_text)
Person (with columns name and activityid)

I would like to create a scheme showing the activities during a week sorted on days and hours. If I ignore the person table I can fix it with the statement:
Select …. From week left join activity on (week.day = activity.day) and (week.hour = activity.hour) order by day, hour

I can then make a loop (I am usin asp.net) that writes the activities.
My problem is when I try to combine the persons to the activtities in an given hour. How do I do that ? (activity.activityid = person.activityid).

I have a little extra question. When I make the join above and print the result (day, time and activity) there isn’t any output if no activity matches a given day and hour. How do I do when I always want to print day and hour and add activity where such exist.

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?

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

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?

Cannot Alias Locked Tables / Join To Unlocked Tables ?
If you manually issue a table lock then query that table, aliasing the table generates an error. If you try to join the table to another table that is not locked, you will receive an error. What is the reason for this?

How To Get The SUM Value From Two Tables? Join Tables? Subquery?
Hi,
I have two tables:

table A

Id. | id_result | value
1 | 1| 10
2 | 1| 11
3 | 2| 7
4 | 2| 13

table B

Id. | id_result | value
1 | 1| 4
2 | 1| 1
3 | 2| 5
4 | 2| 6

How can I get sum of unique keys from table A and B (id_result) like this?:

id_result | sum_table_A | sum_table_B
1 | 21| 5
2 | 20| 11

I can do it with UNION or 2 separate SQL statement, but how to make it in 1 query or using subquery?

How To Join Tables?
I would like to select info from just two tables. I've tried two seperate select calls but it gives me some of the same info from each table twice.

Can someone please post a "join for dummies" example on how I would do this.

I've tried using "Union" but I'm still just a newbie and was even more confused.

Join Tables
How would I join these 2 tables?

$query = "SELECT DATE_FORMAT(g.gdate, '%m/%d/%Y') as date, DATE_FORMAT(g.gdate, '%W') AS day, g.gainloss, g.tradingday, sum(t.amount) as amount FROM gain g left join transactions t on t.tdate = g.gdate WHERE CID='$loginuser'";

the problem is i need to display all records in the gain table, but only display data for the transactions table where cid=loginuser.

Join Of Two Tables
I have two tables that I am trying to join together to create a particular view, but I am have a difficult time coming up with the correct query.

One table looks like...

Uid | Login
1 | John
2 | Paul
3 | Sally

The second table looks like
Uid | Fid | Value
1 | 1 | john@sw.com
1 | 2 | San Francisco
1 | 3 | 2007
2 | 1 | Paul@sw.com
2 | 2 | Atlanta
2 | 3 | 1997
3 | 1 | sally@sw.com
3 | 2 | New York
3 | 3 | 2006

I need the resulting data to look like.....

Join 2 Tables
table1 = a1, a2 and a3
table2 = b1, b2 and b3
when join table1 and table2
table1 + table2 = a1, a2, a3, b1, b2 and b3

question:
Instead of having 6 collums after join, can I have 3??? (table1 + table2 = c1, c2 and c3)

Join Three Tables
I have a problem about joining three tables A, B, C:

table A has the serial_number as its primary key,
table B has the serial_number as its primary key but serial_number is not unique in this table (combined with category, group etc. other columns as primary key together )
table C has some common columns with table B

Now I want to select the serial_number from table B with certain conditions from Table C, so I could use the following query:
select distinct tB.serial_number from table_B as tB join table_C as tC Where Conditions;

And then, I would like to add the serial_number in table_A but not in table B to the serial_number I just selected, so I would use something like:

select distinct tB.serial_number from table_B as tB join table_C as tC Where Conditions
UNION
select serial_number from table_A not in table_B;

is it right? I think it's the right way to do that.

Another question, with the same task, can I do the query like this:

select tA.serial_number from table_A as tA LEFT JOIN table_B as tB ON tA.serial_number = tB.serial_number JOIN tableC as tC WHERE Conditions..

That's what I understand about "LEFT JOIN", SQL will select serial_number from table_B and table_C with the conditions satisfied first and then join table_A with serial_number only in table_A but not in table_B. Am I right?

How To Join Two Tables
I have two tables, dtbl and dltbl

I want to retrieve dtbl.name when dtbl.id is the same as dltbl.id

This is giving me a syntax error:

SELECT t1.id, t2.name FROM dltbl AS t1 WHERE did = $id AND aid = {$a->id} INNER JOIN dtbl AS t2 ON t1.id = t2.id

Join Tables
i have two tables:

table one ("users") contains two columns, "user_id", and "user_name"
table two ("spam") contains three columns "msg_id","user_id", and "msg"

basically, i want to retrieve all the "spam" entries, but instead of
displaying the user_id, i want it to show the user_name, from table
one. (using php)

i'm assuming i need to use this JOIN thing, but i've never used it
before.

How Do I Join These Two Tables?
I want to join two different queries into the same table. I have had a look at UNION and JOIN but they seem to be way over my head. Off peak time is Midnight to 7AM and Peak time is 7AM to Midnight. I can put the data into two separate tables (see below) but I don't know how to combine them. Code:

Join 3 Tables
i want to join three tables and i got it to do that but when want to see the results if there is not a record in all three tables the data for that person wont show up only the person who has a record in all three tables will.

SELECT * FROM users,user_info,user_contact where users.id = user_info.user_info_users_id and user_info.user_info_id = user_contact_id

how can i get it to show all the data and just show blank space if there is no record in one table. like the user_contact table.

Join Tables
Here is the breakdown of my logic on joining the tables (Please critique):

1) PC's can have many software titles and software titles can belong to many PC's but the PC can have only one copy of a particular software title.
2) Software titles can have many licenses but a license can belong to only one Software title.
3) A license to a particular software title can belong to only one PC.

The table layout I created, only relevant parts (Again, please critique):

1) tblPC has an autoincrement primary key, tblPcID.
2) tblSoftware has an autoincrement primary key, tblSoftwareID.
3) tblLicense has an autoincrement primary key, tblLicenseID. It also has a foriegn key, tblSoftwareID to tblSoftware.tblSoftwareID.
4) tbljnPcSoftware has a unique index that includes tblPcID and tblSoftwareID. tblSoftwareID is also separetly indexed. tblPcID is a FK to tblPcID.tblPcID and tblSoftwareID is a FK to tblSoftware.tblSoftwareID. I also have tblLicenseID as an index in this table.

Join Tables
I have 2 tables with the following schematic:

TABLE 1

ID Name Description
==============================
1 John Tall
2 Mike Small

TABLE 2

ID Element
==================
1 Red
1 Green
1 Yellow
1 Blue
2 Red
2 Green
2 Yellow
2 Pink

I want to create a view / or output that concatenates _all_ the elements from Table 2 into one single field that can be placed on the end of the Table 1 (regardless of how many elements).

Like the following:

OUTPUT / VIEW

ID Name Description Element
=================================================
1 John Tall Red Green Yellow Blue
2 Mike Small Red Green Yellow Pink

How To Join 2 Tables
Anyways Im a newbie using MYSql V5

I have 2 tables (called teams & results)

In teams I have 2 columns

teamsindex..........teamname
(Primary Key)
........1......................arsenal
........2......................man u
........3......................liverpool
........4......................chelsea
........5......................tottenham
........6......................accrington stanley (who are they :) )

In results I have 3 columns (amongst many) which contain primary key & fixture info (intiger)

e.g.

Recordnum..........hometeam.......awayteam
(primary key)

.........1......................1.......................2
.........2......................3.......................4
.........3......................5.......................6

I want to output the fixtures so instead of it displaying 1vs2, 3vs4, 4vs5 I want it to display arsenal vs man u, liverpool vs chelsea, tottenham vs accrington stanley etc

I know there is a really really simple answer to this but Im really really thick, I expect its based on the JOIN syntax but can someone show me the code that would produce the results I require in order for me to get my head around it, me is very odd, I can work things out going backwards.

Join Three Tables With SUM
I have three tables, a purchase order table, an items ordered table, and an invoice table. I want to select records from the purchase orders based on various criteria (eg vendor, or date, etc) and then get the SUM of the items ordered from the second table, and the SUM of the invoices (if any exist yet) from the third table. Is this possible with a single select statement?

Here is one of my trial queries that seems to be close to what I am looking for. Depending on how I apply (phrase) the 'group by' clause and the table being grouped, it sometimes produces corect results for one of the sums or the other:

SELECT po.ponum, po.poDate, po.vendor, po.status,
SUM(item.qty*item.cost) AS itemtotal, SUM(invoice.cost) AS invoicetotal
FROM po LEFT JOIN item ON po.ponum=item.ponum
LEFT JOIN invoice ON po.ponum=poinvoice.ponum
WHERE po.status='open' AND
((po.pon BETWEEN $poNumLo AND $poNumHi) OR (po.poDate BETWEEN '$poDateLo' AND '$poDateHi'))
GROUP BY item.pon

The actual application and tables are a bit more complex, but I simplified it here for clarity.