MYSQL :: Avoid Self-joins

Count(*) To Avoid Duplicates
This is for (2) seperate sites that share the same login table (same username/passwords will access both sites).

Users may register at either site, if they choose, then they should login at the other site to "complete" the last phase of registration at the other site to insert remaining needed values.

Problem is with duplicates. When some users won't login, as they should, "complete" the process at the other site. They may bypass and register anew like anyone else at the site for the first time. (Even though there is a pre-registration page at both sites -- asking if users have already registered at the other site prior to arriving at the second -- directing a login to complete the process.)

Reason this is a concern is that one site has a (6) page registration process, inserting to (8) tables.

My idea (aside from aleady checking for unique inserts on usernames/passwords/emails) is to check on the complete phone number.
To be used on a conditional show region on the second page of registration -- if the latest registrant entered the same phone number asmay aleady exist in the table, (previously enterered while registering on the other site and forgot about it) -- this 2nd registration page will not show due to the: count(*)

Starting with 'Area' code, is this practical/how to combine all three?
Anyone have better suggestions or experience on handling two site duiplicates?

Would there be any benefit in having ALL USERS (both new and prior from the "other" site) fill out the the 1st registration form (on the (6) page registration site) where they are then directed to login to complete the process?

'AREA' 'PRE' 'PHONE'

SELECT *
FROM `Members`
WHERE `Area`
IN (
SELECT `Area`
FROM 'Members`
GROUP BY `Area`
HAVING count(*)>1
)

note: some registrants have same company names fir satellite/regional offices

Avoid Zero In Interger Datatype
Is there any way to avoid zeros which automatically entered in database in those column those have integer data type.

or i want to enter - instead of those zero

How To Avoid Previous Results
I want to omit all results(id) from the first query in 2nd query , without using sub query...

How To Avoid Duplicate Records
I need to filter out duplicates for every 30 seconds. say i have two duplicate records within the 30 seconds limit. I need to show up only one. If there are identical records but with a different time settings(say above 30 seconds) then i need to display it. I need to restrict duplicate records within 30 seconds.

How To Avoid NOT EXISTS In MySQL 4.0.26
I tried to use NOT EXISTS but recently found out that it is supported since 4.1. Now I'm trying to avoid it but I can't figure out how to do this. The Problem is that I have a table with (among outhers) a column SessionId and action.

Action may be opened and closed. Now I want to get all those sessions which are in state open, i.e. which have no line with action = closed. My first attempt was:

select sessionId
from audit AS a
where action = 'opened' AND
NOT EXISTS (SELECT * FROM audit AS b WHERE b.sessionId = a.sessionId AND b.action = 'closed')

Could anybody give me a hint how to get an equivalient query without a subquery?

How To Avoid Repeat Typing
I would like to know how to avoid repeating
typing an SQL statement when an error occurs after execution. That is,
if an error occurs I should be able to retrieve the statement that I
had written and correct the mistake. It is agonizing to keep on
repeating a statement that can take five minutes to write just because
one mispelled a word or missed a comma. I use windows 98.

Avoid Repeat Typing
I am a newbie in MySQL. I would like to know how to avoid repeating
typing an SQL statement when an error occurs after execution. That is,
if an error occurs I should be able to retrieve the statement that I
had written and correct the mistake. It is agonizing to keep on
repeating a statement that can take five minutes to write just because
one mispelled a word or missed a comma.

Command Used In Avoid Retyping
I don't want to retype a command once I wrote it. Does anyone knowa about an option that would just that like in a dos shell where you can use arrows to get back any command you have entered.

Avoid Couples In The Resultset
I use a query like this:
select t1.id_topics, i1.id_indices , t2.id_topics from indices i1, indices i2, topics t1, topics t2
where (i1.ind ='test' and i1.id_urls=t1.id_urls and i2.id_urls=t2.id_urls and i1.ind=i2.ind and t1.id_topics<>t2.id_topics)

it gives results like those:
"id_topic1","id_indices","id_topic2"
36,682,34
37,682,36
36,682,37
37,682,34

I would like to eliminate the "inverted" mates of couples in the resultset. That means in the example
"36,682,37" should be eliminated since "37,682,36" is already part of the resultset. Is it possoble to express this request in the query?

Avoid Duplicate Records In Within 30 Seconds
I'm working with php. I have a auction site, more or less. I want to create all-time rankings. The idea is to display where a seller ranks (all time) in the number of sales.

So for example, I'd display
John Doe All Time Sales Ranking: #138

I'm not exactly sure how to go about this.

$query = "SELECT count(*) as counter, SellerName FROM sales GROUP BY Sellername ORDER BY counter DESC";

This query would give me the data to list all of the Sellers in desc order by the number of sales.

In php, I could probably count until the Sellername was equal to $Sellername (already defined in their profile page), but I was hoping there would be a way to do this entirely in MySQL.

Trying To Avoid Using Query Inside While Loop
I've done a lot of reading on here and I learned from some of Rudy's posts that it's a bad idea to do a query inside a while loop. I don't know why this is but he seems like an expert so I'll listen

What I am trying to do is take the top ten points from a player and display them. First I will post the tables then the code.

CREATE TABLE `tournament` (
`gameid` int(11) unsigned NOT NULL auto_increment,
`gametype` tinyint(2) unsigned NOT NULL default Ɔ',
`gamedate` datetime NOT NULL default &#55612;&#57200;-00-00 00:00:00',
`leagueid` int(11) unsigned NOT NULL default Ɔ',
`seasonid` int(11) unsigned NOT NULL default Ɔ',
`roomid` int(11) NOT NULL default Ɔ',
`gamename` varchar(50) NOT NULL default '',
`cost` mediumint(4) NOT NULL default Ɔ',
`seats` mediumint(4) NOT NULL default Ɔ',
`notes` tinytext NOT NULL,
PRIMARY KEY (`gameid`)
) ;

CREATE TABLE `tournament_results` (
`id` bigint(20) unsigned NOT NULL auto_increment,
`gameid` int(11) unsigned NOT NULL default Ɔ',
`memberid` int(11) NOT NULL default Ɔ',
`place` smallint(4) NOT NULL default Ɔ',
`earnings` smallint(5) default Ɔ',
`bounties` float unsigned default Ɔ',
`points` float NOT NULL,
PRIMARY KEY (`id`)
);
Now the follow code would be what I would use if I just wanted to add all the points and not limit it

PHP

mysql_query("SELECT
members.firstname,
members.lastname,
SUM(tournament_results.points) AS tpoints,
COUNT(*)as numgames,
AVG(tournament_results.points) AS apoints,
members.memberid AS membersid
FROM
members,
tournament_results,
leaguemembers, tournament
WHERE members.memberid = tournament_results.memberid
AND leaguemembers.memberid = members.memberid
AND leaguemembers.leagueid = '$lid'
AND tournament_results.gameid = tournament.gameid
AND tournament.seasonid = '$sid'
GROUP BY members.memberid
ORDER BY tpoints DESC");

That will total all of their games but I want to limit it their top 10 scores how can I do this without introducing a query inside of the while loop?

How To Avoid Having Thousands Of Records In A Many-to-many Relation
I'm building a web administration system for my company. We keep all our contacts from other organizations in this system (stored in a MySQL database): name, addres, telephone etc.

One feature of the system is that you can select a number of contacts and collectively send them an email. This works fine.

But: some of my co-workers need to know which of the contacts has received a specific email from the system.
So I was considering a setup like the following:

Table one: contacts (name, addres etc.)
Tabel two: emails (subject, text, creation day, day of delivery etc.)
Table three: many-to-many table holding one row for each email that has been send to a specific contact (autonum ID, ID of email and ID of contact).

My problem is that I can predict that this table will have LOTS of records in no time, as my co-workers are sending out many emails to a lot of contacts.

So: are there any better ways?
I thought about storing all contact IDs that has received a specific email in a text field in that email's record in the database - but then I'm not sure of the performance when I have to find out if someone specific has gotten a specific email etc.

Cleaning Up MySQL Connections To Avoid 1040
Well, I'm trying to run
PHP

$result = mysql_query("SHOW FULL PROCESSLIST");
while ($row=mysql_fetch_array($result)) {
    $process_id=$row["Id"];
    if ($row["Time"] > 200 ) {
        $sql="KILL $process_id";
        mysql_query($sql);
    }
}

To clean up my connections, as I'm getting a 1040 error "too many connections". Of course, I can't run this until I can actually connect, unless there is a way around somehow. I don't have any admin rights, I just have a web-based "php my-admin" module to run the db.

Every page people access opens a mysql connection, and then it is closed with
PHP

mysql_close($connection);

. Would putting in
PHP

<?php mysql_close($connection); $NASI_connection = null;?>

Mysql Running All Queries Double ? How To Avoid ?
Sometimes, when my database server has been slow and building up hundreds of queries, I have the impression that mysql is running 'double' of triple . It's hard to explain, but when I look at mtop (a tool to see what queries are active and what time they take to finish) , I see a lot of queries that seem to hang and that are present more then once. Some of them have unique data in them that can not happen just by refreshing a form. When the peak moment is over, and I check my site, then I sometimes see for example the exact same forum post 3 or 4 times in the same thread. Needless to say, this causes a lot of extra load on my database.

I'm not sure what really happens, but it seems like mysql server is just piling up queries wich it can not process fast enough, and those queries seem to come in several times again. I use seperate webservers, but even if I reboot them all, the extra queries still come in as soon as the webserver is back up and php scripts start to work again. The only way to stop it all, is to restart mysql, but that is something that I can not just do very easily. I rely on several heap tables for my site and they need to be refilled with data from normal tables. So restarting mysql is something I only like to do at night, with very few visitors online.

I hope I'm explaining my problem well enough. And I hope there is a way for me to check what really is going on, and if there is a way to stop mysql from 'running double' ?

What Field Type To Use To Avoid Blank Spaces In Fields
Can you tell me the best field type to use here?

I've got a table in mysql with all 5 fields defined
as tinytext

Problem is when I export this to to a text file for notepad
each field is padded out by several blank spaces,
and i think my eamil program doesnt like this type of structue :
field1 , field2 , field3 , field4

Scheduling Replication To Avoid Bottle-neck Updates
We have a circular master-slave setup where any one of the 2 servers
can become master at any time (by human decision). The two servers are
placed at geographically different sites. The servers contain à number

of databases which are all replicated both ways.

When we have full usage of one master ~500 inserts/updates per second,
the bandwidth between our sites becomes a significant bottle-neck. This

we can accept at database level not on server level, ie
- if database A on site B has a lag of 30min because of important
activity on database A on site A, it is acceptable.
- if database B on site B has a lag of 30min because of important
activity on database A on site A, it is not acceptable.

Is there a work-around? We never have updates concerning 2 databases in
the same query.

Creating multiple mysql servers at each site could be one, but that
means some 10-50 servers on every physical computer. What side-effects
does that create?

Natural Joins And Joins With USING
I was wondering if there is a way to make MySQL 5.0.15 ( final release ) able to use natural joins and joins, using old code that worked with 5.0.11 and earlier.

Since I am new to starting mysql and modifying it, more information the better.

Joins
Im trying to show 1 image per product using a table join:

$sql = mysql_query("SELECT p.product_id, p.product_name, p.description, i.photo_filename
FROM products AS p
LEFT JOIN product_images as i ON p.product_id = i.product_id
WHERE filter = ".$_GET['filter']."
ORDER BY product_name ASC");
One product has 2 images linked to it .. with that join rather than displayin the record once with a single image .. it shows the record twice, once for each picture in the db then proceeds to the next record.

What kind of join do I need to have it display 1 image for each record in the loop?

Self Joins
I'm not new to mysql however i have an issue with something that im trying to do.
I store football games in a 'games' table, the table has [among other fields] game_id,game_hteam,game_ateam,game_hscore,game_ascore which relates to the gameid, the hometeam id, the away team id and the relevant scores for each side.....

JOINs
I may be trying to do too much in a single query here, but it would be nice if I could get it working! Apparently, the version of MySQL used by my work does not support the WITH ROLLUP feature, otherwise I think that would work.....

Help With Joins
I have 2 tables (users and comments), where the suers table has a bunch of details about each user, and the comments table has comments left by that user. Each user can have more than 1 comment. I am trying to write query that extracts all users alongwith all their comments.

I have something like:
$sql = "Select * from comments left outer join users on comments.userid=user.userid";

This basically gives me the result, but in an unelegant way. I get as many records for the same user as they have comments. Is there a better way to do this, where I get 1 reslt per user and all their comments are in the form of a list or array?

Joins
I have 2 tables (users and comments), where the suers table has a bunch of details about each user, and the comments table has comments left by that user. Each user can have more than 1 comment. I am trying to write query that extracts all users alongwith all their comments.

I have something like:
$sql = "Select * from comments left outer join users on comments.userid=user.userid";

This basically gives me the result, but in an unelegant way. I get as many records for the same user as they have comments. Is there a better way to do this, where I get 1 reslt per user and all their comments are in the form of a list or array?

Joins
I'm trying to modify a query to avoid using group by or distinct to reduce the amount of time to get results.

I want to get every record from table 1 that has a matching record in table 2
The tables are in a 1 to many relationship ie. One record in table 1 will have many matching records in table 2. I've tried right, left and inner and neither seem to give the desired results - any suggestions from the gurus?

Joins.
I'm using mysql to house a database of quotes, complete with ratings by users. So in one query, I need to get everything with a certain rating from the "quotes" table, get the submitter's username from the "users" table and finally check the "ratings" table to see if the user viewing the quotes has already rated it.

The "ratings" table consists of the user id and the quote id. So ideally, I'll be able to to get the first 25 quotes and end up with each in a row something like this:

submitter's id - quote id - quote text - user id - quote id 2

Joins
I have three tables named project, projecthistory, and billing for my project tracking database. My problems arises when a project doesn't have a projecthistory or any billing items entered for it yet. Then the project doesn't show up at all.

project: id, name
projecthistory: project_id, description, submitdate
billing: project_id, monthlyrate, quantity

SELECT MAX(ph.submitdate), SUM(b.quantity), p.id, p.name, ph.description,
FROM (project p LEFT JOIN billing b ON p.id = b.project_id) LEFT JOIN projecthistory ph ON p.id = ph.project_id;

Ultimately what I need is for ALL projects to show up. If any projecthistory exists, display ONLY the newest one (for that project_id). If any billing exists, display the SUM of the quantities (for that project_id).

Out Of Joins
I have this query:

SELECT c.id AS cid, c.cat_name, f.id AS fid, f.forum_name, f.forum_desc, f.redirect_url, f.moderators, f.num_topics, f.num_posts, f.last_post, f.last_post_id, f.last_poster, t.question, f.parent_forum_id FROM '.$db->prefix.'categories AS c INNER JOIN '.$db->prefix.'forums AS f ON c.id=f.cat_id LEFT JOIN '.$db->prefix.'forum_perms AS fp ON (fp.forum_id=f.id AND fp.group_id='.$forum_user['g_id'].') LEFT JOIN '.$db->prefix.'topics AS t ON f.last_post=t.last_post AND f.last_post_id=t.last_post_id WHERE (fp.read_forum IS NULL OR fp.read_forum=1) AND (f.parent_forum_id IS NULL OR f.parent_forum_id=0) ORDER BY c.disp_position, c.id, f.disp_position

and i want to add the field displayname from table: '.$db->prefix.'users where username = f.last_poster

I have used all of my joins i think (left, right, inner) and i need to add that in, can someone add this in.

Many Joins
I've this table structure:

t2(jb, B)
|
tj12(b, jb)
|
t1(A, b, c)
|
tj13(c, jc)
|
t3(jc, C)

How can I do an elegant "SELECT A, B, C FROM t1" ?

When I try
JOIN tj12 USING(b)
JOIN t2 USING(jb)
JOIN tj13 USING(c)
JOIN t3 USING(jc)
MySQL join tj13 with t2.
Must I use "JOIN ... ON ..." to force MySQL or thre's an other way?

Using Joins
I'm having trouble figuring out which join i should be using to attain the results I am looking for. here's a bit of background information.

I have 3 tables

Table 1 - hp_cats
- cat_id
- cat_name

Table 2 - hp_projects
- project_id
- various other fields that are irrelevant

Table 3 - hp_proj_cat
- pc_id
- cat_id
- project_id
- active

Basically, I have a table of projects, and a table of categories. each project has an entry in hp_proj_cat for each category. if the category is active for that project, active = 1 in hp_proj_cat.

I'm looking for a way to retrieve all projects that have categories say '85, 200, 13, 6' and are active (ie, set to 1).

I know this will involve a join, but am a tad bit confused. I greatly appreciate any help anyone can provide.

Sql / Joins
I have 4 tables: tblProducts (it has all the product names), tblInventory (it has all the products actually in inventory), tblMainIngredient (it has all the main ingredients ids associated with the products' ids), and tblProductIngredient (it has all the ingredients' ids).

I am trying to retrieve all the products and their main ingredient but also those that don't have one. I need to be able to display the product name (in tblproducts) as well as the name of its main ingredient (tblproductingredient). I wrote the following SQL but it doesn't seem to work as it returns only the products that have an ingredient associated with them:

SELECT * FROM (tblProduct INNER JOIN tblInventory ON tblProduct.ProductID = tblInventory.ProductID) INNER JOIN (tblMainIngredient INNER JOIN tblProductIngredient ON tblMainIngredient.IngID = tblProductIngredient.IngID) ON tblProduct.ProductID = tblProductIngredient.ProductID WHERE tblProduct.ProductID = " & productID ORDER BY Name, Price, Comments

Joins
I was wondering if someone cold explain the JOIN to me, I've looked over the forum but don't understand.
I know kind of that the INNER JOIN is the same as WHERE???
whats the difference between LEFT and RIGHT JOINS?
And INNER and OUTER JOINS???
Please help because I'm having major problems with my search sql which I'm trying to write for my uni coursework

Joins
Table Oelsls contains all products bought (history) table Invt contains
the products to sell (Inventory) I'm trying with no success to list all
products in Oelsls table (field name in both tables: part_id) that are not
in the Invt table.
My objective is a grid of all Special (non inventory) products.
Can anyone produce the correct sql statement?

Joins
an explanation of the different types of JOIN.
LEFT, RIGHT, INNER, OUTER etc.
I can find syntax for all the instances but feel a discussion which compares the different types will be very useful.

Self Joins
however, when I do this, I am only able to display the two parts that meet the ON statement, and not have the right side null when there is nothing that exists on a left join. that is how it should be.

for example

select this1.something, this2.something from sametable as this1 left join sametable as this2 on this1.column = this2.column
where this1.form = 'red' AND this2.form = 'blue'

I have used this, and it works. basically, I am joining the red columns with the blue columns and displaying the data on one area because the red columns are different rows and so are the blue columns. anyway, is there a way to make it so that if there is no blue column that exists, the red column will display its contents.

Joins
i have these two queries:Code:

SELECT totalscore FROM top_scores WHERE userid=3
SELECT MIN(score1),MIN(score2),MIN(score3) FROM scores WHERE userid=3

how can i use join to make that query into one, but still selecting from the two seperate tables?

Joins
I'm trying to join the equipment table whenever "nsidetails.equipmentid = equipment.itemid". I get correct data but the equipment table never joins.

SELECT equipmentid, COUNT( equipmentid ) AS numOfItems
FROM nsidetails
LEFT JOIN equipment ON nsidetails.equipmentid = equipment.itemid
WHERE nsidetails.itemstatus = 'SHORT' AND nsidetails.location = '".LOCATION."'
GROUP BY nsidetails.equipmentid
ORDER BY nsidetails.equipmentid

JOINs And UserNames...
i need a bit of help on the following problem.
Table I
Row_i. 1 2 _
Row_ii. 3 1 6
...
Row_j. 9 _ _
The numbers in each row identify users (User_A, User_B, User_C); not all rows have all three users filled in - in the example above, the first row has two users, the second three, and the last only one.
Table II
Row_i. 1 Fred Bloggs
Row_ii. 2 John Smith
Row_iii. 3 Pat Brown
...
Row_j. 6 Tom Adams
...
Row_k. 9 Vicky Brown
...
Row_l. n Name Surname

I can thus identify each user (if present) in Table I.

So far, so good. Now for the problem. I need to generate a list of ALL the names used in Table I, either as User_A, User_B, or User_C, without repetitions, ordered by Surname. Given the example above, the output should be
6. Tom Adams
1. Fred Bloggs
3. Pat Brown
9. Vicky Brown
2. John Smith
Simple, right?... But I guess the old brain is just not cooperating today, because I can't seem to get my query right.
Any simple and elegant solutions out there?
Many thaks in anticipation for your help.

Joins And Limit Used Together
I have a query, which reads all information from tables.

select se.section_name,pr.*,ph.code from prices pr left join pharmacy ph on pr.pharmacy_id=ph.id left join sections se on pr.section_id=se.id where medication_id=7 order by se.order_id asc, cast(`dosage` as SIGNED) asc, cast(`total_price` as SIGNED) asc

result can be viewed here:
http://www.foreign-drugstores-online...oduct.php?id=7

I need to read only top 5 (total price) for each `dosage`

somekind of: group by `dosage` order by total_price desc limit 5 per group

Limiting Inner Joins
This has been bothering me and the relation isn't typical of the other expressions I've had to build.

I have a table of 250 records that I'm returning ie.

SELECT B.ID, B.Title, B.Owner
FROM blogs AS B
WHERE B.Status = 0
I need to return not only the blog name but the latest post they've made on their account. So what I did was used:

SELECT B.ID, B.Title, B.Owner
FROM blogs AS B
INNER JOIN archives AS A ON B.ID = A.BlogID
WHERE B.Status = 0
But when I do this it returns each record in archive with the relevent blog information. What I would like is for it to determine the latest ID insert on the blog.

So if he has posts IDs such as 1010, 1050, 2063 and 2075 then it will return the blog information and the results of record 2075 in the archives table.

I've tried a couple ways so far but each fails, I think what I want to do is somewhat limit the return on the inner join...sort of like add a WHERE clause unto the clause.

I would have done this with a stored proc. or a view but I'm using MySQL 4 and I want to keep the querying function in my code for now.

Not sure if that makes sense to the experts, if my problem isn't clear let me know I'll explain it further.

Conditional Joins
This is similar to a previous thread of mine: http://www.sitepoint.com/forums/showthread.php?t=375169

I have two fields in my user table, 'lastentryid', and 'lastprivateentryid'. What I need to do is join the entry table using that entryid. Unfortunately, the results are skewed if their last entry is private, because lastentryid won't contain it. I am running into this problem in a few places, and I'd really like to solve it without using subqueries. I might even accept better organization on the db level.

Here is my query as it is now

SELECT
user.userid, user.options, user.displaygroupid, user.usergroupid, user.username,
ugroup.opentag as opentaga, ugroup.closetag as closetaga,
dgroup.opentag as opentagb, dgroup.closetag as closetagb,
entry.title, entry.dateline as entrydateline
FROM vb_user as user
LEFT JOIN vb_usergroup as ugroup ON(user.usergroupid = ugroup.usergroupid)
LEFT JOIN vb_usergroup as dgroup ON(user.displaygroupid = dgroup.usergroupid)
LEFT JOIN vb_blog_entry as entry on (entry.entryid = user.lastentryid)
WHERE entry.dateline > $cutoff
ORDER BY user.username ASC
Here is what I want... but this doesn't work.

SELECT
user.userid, user.options, user.displaygroupid, user.usergroupid, user.username,
ugroup.opentag as opentaga, ugroup.closetag as closetaga,
dgroup.opentag as opentagb, dgroup.closetag as closetagb,
entry.title, entry.dateline as entrydateline,
if (
user.lastentryid > user.lastprivateentryid,
user.lastentryid,
user.lastprivateentryid
) as last_entryid
FROM vb_user as user
LEFT JOIN vb_usergroup as ugroup ON(user.usergroupid = ugroup.usergroupid)
LEFT JOIN vb_usergroup as dgroup ON(user.displaygroupid = dgroup.usergroupid)
LEFT JOIN vb_blog_entry as entry on (entry.entryid = last_entryid)
WHERE entry.dateline > $cutoff
ORDER BY user.username ASC

Multiple Joins
My database has three tables with the following fields:

--tblIngredients--
IngredientID
Ingredient
IngredientInfo

--tblRecipeIngredients--
RecipeIngredientID
RecipeID
IngredientID
Quantity

--tblRecipe--
RecipeID
RecipeName
Directions

tblRecipeIngredients is a join table to link each recipe from tbRecipe with it's respective ingredient(s) from tblIngredients.

Now, I'm trying to write a query that will return all recipe names (RecipeName) that don't have each of the Ingredients specified. In other words, I'm going to have a form that allows the user to select their on-hand ingredients and I want the query to eliminate all the recipes that include ingredients that the user does not have on hand and return the rest. I hope I'm making sense here...

Anyhow, this is what I have so far, but it doesn't work the way I would like it to:

MySQL Joins
Table 1: messages
has the following columns:
ts: datetime stamp
username: username of who sent the message
message: Text of the message itself

Table 2: users
Has these columns:
username: obvious
joined: What time they joined
status: enumerated, either active, away, or kicked

I need to do the following:

Select all messages which were sent no earlier than when the user joined. So the timestamp of the message must be greater than or equal to the time at which the user joined.

I tried this, but it doesn't work at all:

SELECT TIME(ts), messages.username, message FROM messages LEFT JOIN users ON ts >= joined

Not sure why it doesn't work, but I'd like to know :)

Joins On Several Tables
A have a table "users" of users, for this matter let's say with two fields: id (int) and name (varchar).

I also have a table "replies" with various user replies.

Fields: user_id (int), input_id (int), reply (varchar)

This table works as one entry per reply â€“ if a user replies to a form with four input-fields this is recorded as four lines in this table. The inputs are identified by input_id.

OK. The task is, being presented a number of input field ids (lets still say four), to retrieve a result set of users having replied to these fields and their replies.

This would be a simple join, BUT i want a row even if the given user has replied to less than all of the given inputs. IE there may not be matches for all inputs. No matter which combination of LEFT and RIGHT JOINs i try I seem to leave out some. Code:

Outer Joins
I canÂ´t figure out how to create the following SQL

select a.folio, a.tipo, a.date
from solic a, order b
where a.id = b.id_sdc and b.tipo!=399 and b.tipo!=398

so far so good
besides, I want the rows from table a with no match in table b

select a.folio, a.tipo, a.date
from solic a, order b
where (a.id = b.id_sdc and b.tipo!=399 and b.tipo!=398)
or a.id not in (select id_sdc from b)

this select does not work with the version I have. I tried using the left join but couldnÂ´t get the right rows

Alternative To Self-joins?
I have a table that has values of variables for certain entities. The
columns of interest are targetID, variableID, and valueID. A row (1, 5,
9) means that target number 1 has a value of 9 for variable 5. Being
denormalized, target number one will have many possible rows in this
table, one for each variable for which it has a value.

My problem occurs when I want to find out what targets match a certain
set of variable values. For instance, I want to find out what targets
have a value of 9 for variable 5 and a value of 25 for variable 10. I'm
thinking that this can be a simple self-join:

SELECT mya.targetID from mytable as mya
LEFT JOIN mytable as myb
ON mya.targetID=myb.targetID
WHERE (mya.variableID=5 AND mya.valueID=9)
AND (myb.variableID=10 AND myb.valueID=25)

Does this make sense so far? The problem is that this doesn't scale.
When I have more than 31 variables and I need to evaluate them all,
MySQL breaks: I can't do more than 31 joins.

My design calls for perhaps 80-100 variables, so even 64-bit
architecture with a limit of 64 joins won't get me there.

I need another data structure that won't get me stuck on too many
joins. Any suggestions? If I have to scrap this approach in favor of
another,

Complex Joins
Here is the problem. Table_1 (gl_Train_KeyIdeas)

+----------------------+----------------+-------------------+---------------+
| KeyIdea_ID | Unit_ID | Group_ID | Title |
+----------------------+----------------+-------------------+---------------+
| 1 | 1 | 27 | yada 1 |
| | | | |
| 2 | 1 | 27 | yada 2 |
| | | | |
| 3 | 1 | 27 | yada 3 |
| | | | |
| 4 | 1 | 27 | yada 4 |
| | | | |
+----------------------+----------------+-------------------+----------------+

Table_2 (gl_Train_Progress)

+----------------------+----------------+----------------------+
| ID | User_ID | KeyIdea_ID |
+----------------------+----------------+----------------------+
| 12 | 5 | 3 |
| | | |
| 11 | 5 | 2 |
| | | |
| 10 | 5 | 1 |
| | | |
+----------------------+----------------+-----------------------+

The following sql returns field KeyIdea_ID = 4 which is the only
KeyIdea not in both tables.

SELECT gl_Train_KeyIdeas.KeyIdea_ID
FROM gl_Train_KeyIdeas LEFT JOIN gl_Train_Progress ON
gl_Train_KeyIdeas.KeyIdea_ID = gl_Train_Progress.KeyIdea_ID
WHERE (((gl_Train_Progress.KeyIdea_ID) Is Null) AND
((gl_Train_KeyIdeas.Unit_ID)=1));

What I seem to be having trouble with is specifying the User_ID in
table 2. I need to specify the current user for example: an sql with a
User_ID = 6 would return KeyIdea_ID of 1, 2, 3, and 4.