MYSQL :: Table Locking When Using Left Join

LEFT SELF Join -- LEFT Join On Same Table
Is it possible to LEFT JOIN a table to itself?

I want to see all records in a table where the year of the sales date is
2003 and where the salesman sold an item to a customer to which he did not
sell that item to in 2002.

The following is an example table (SALES):

+----+-----+------+------------+--------+------+-----+
| ID | SLM | CUST | DATE | ITEM | COST | QTY |
+----+-----+------+------------+--------+------+-----+
| 1 | 1 | AAA | 2002-01-01 | APPLE | 1.00 | 2 |
| 2 | 1 | BBB | 2002-01-01 | APPLE | 1.00 | 2 |
| 3 | 1 | CCC | 2002-01-01 | PEAR | 1.00 | 2 |
| 4 | 1 | AAA | 2002-01-01 | PEAR | 1.00 | 2 |
| 5 | 2 | AAA | 2002-01-01 | APPLE | 1.00 | 2 |
| 6 | 3 | CCC | 2002-01-01 | BANANA | 1.00 | 2 |
| 7 | 1 | AAA | 2003-01-01 | APPLE | 1.00 | 2 |
| 8 | 2 | AAA | 2003-01-01 | APPLE | 1.00 | 2 |
| 9 | 3 | CCC | 2003-01-01 | BANANA | 1.00 | 2 |
| 10 | 1 | AAA | 2003-01-01 | BANANA | 1.00 | 2 |
| 11 | 3 | CCC | 2003-01-01 | APPLE | 1.00 | 2 |
+----+-----+------+------------+--------+------+-----+

What I want to see in my result is that in 2003 Salesman 1 sold bananas to
customer AAA and salesman 3 sold apples to customer CCC. I want to see
these because these salesmen did not sell these customers these items in 2002.

Left Join On Table Aliases?
mysql>select emp.*, sup.fname as supF, sup.lname as supL
from employee as emp, employee as sup, emp left join sup on emp.supid=sup.empid
where emp.username="dgibb";

Error 1066: Not unique table/alias: 'emp'

The table structure is pretty easy, the employee table has a primary key on empid, and a foreign key on supid(the empid of this employee's supervisor).

The result should give all employee fields, and if there is a supervisor attached to this employee record, give the first and last name.

My question is:
Is it even possible to a left join on the same table using aliases?, and if so, how?

Getting Number Of Rows From Left Join Table
PHP

SELECT bulletin.id AS bid, bulletin.bulletinname AS bname, bulletin.bulletindesc AS bdesc, bulletin_post.id AS postid, DATE_FORMAT(bulletin_post.postingtime,'%M %d, %Y %l:%i%p') AS postdate, bulletin_post.bulletintitle AS ptitle, member.screenname AS mname, member.id AS posterid FROM bulletin LEFT OUTER JOIN bulletin_post ON bulletin.id = bulletin_post.bulletinid AND bulletin_post.postingtime = (SELECT MAX(postingtime) FROM bulletin_post WHERE bulletinid = bid) LEFT JOIN member ON member.id = bulletin_post.memberid WHERE bulletin.active = 1 ORDER BY bulletin.bulletinname ASC

My question is how would I get the count of rows if any from the bulletin_post table?

Bad Performance Left Join On Table Keys
Today I migrated an MS Access database to the latest MySQL containing interface files I have to compare, because Access is grinding to a halt as the DB is growing.

I am converting a legacy interfacing system and I have to make sure that the new system creates identical files. Hence the aforementioned DB.

I have two tables, both with the same lay-out and I need to find the records which are in table A but not in B. The table looks something like:

root_id longint
object_id longint primary key
assort_id longint
name varchar255
... (about 15 fields in total)

Yes, the second field is the key, as this is the lay-out of the file.

The query I made is quite simple, something like:

SELECT a.*
FROM legacy AS a LEFT JOIN new AS b ON a.object_id = b.object_id
WHERE b.object_id IS NULL;

In Access this query takes about two minutes; each table contains around 250k records and about 10% of the data is disjunct.

When I run the same query in MySQL, CPU-load shoots up to 100% and nothing happens for more than 15 minutes. When I use an inner join (to get the matching records) the query returns in less than a second.

Specify Conditions For The Left Side Of A Multiple Left Join
I'm doing a left join that looks like this standard example:

SELECT t1.c1, t2.c2, SUM(t3.c3)
FROM t1
LEFT JOIN t2 ON t1.c1=t2.c1
LEFT JOIN t3 ON t1.c1=t3.c1
GROUP BY t1.c1

The problem is that I also want to specify a condition for selecting records from t1:

WHERE t1.c1='x'

so that only the records with that value in c1 will be returned on the left side of the join. I don't know where to put this in the SQL.

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.

"left Join" In One Table And Not In Another
I have three tables joined on key fields: delivery is joined with invoice_detail on delivery_id and with location on loc_id. I want to return records of deliveries that have corresponding records in the location table, but have no corresponding records in the invoice table.

Here's the query I'm attempting to use:

LEFT JOIN? RIGHT JOIN? Multiple JOIN?
Simplifying this down to its basics, I'm using LEFT JOIN in a query but I'm not getting the results I want.

The tables are:
table services
service_id
service_name

table services_provided
service_id
service_date (date field)
cust_id
service_quantity

I need to select ALL services from the services table, and the number of services provided (by a specific customer, in a specific time frame) from the services_provided table, so that I can generate a list that shows services provided by that customer in the specified period of time

The query:

SELECT service_date, service_name, service_quantity
FROM services
LEFT JOIN services_provided ON services_provided.service_id = services.service_id
WHERE cust_id = $cust_id
AND MONTH(service_date) = 10
AND YEAR(service_date) = 2007
GROUP BY service_id
ORDER BY service_id
(Aside: The date to be selected varies - it may be the whole year, or may be a selection of months,such as 1, 2 or 3. This is determined dynamically in the script. The cust_id is determined by which customer is logged in.)

I'm pretty sure that the left join as I have it should return all services, even if there's no corresponding entry in the services_provided table.

But because of the WHERE clause, I don't get a complete list of all services -- if the customer doesn't have any entries for a particular service, that service doesn't come up in my results.

Do I need to change how I'm joining the tables, or join them twice? I'm sure I could do this with a nested query, but I'm trying to avoid that.

Using LEFT JOIN Instead Of A Equi-JOIN
I have a SQL statement in some code I'm trying to get my head around.. I havent used SQL that much so I assume this is a newbie question: Why would someone use LEFT JOIN if they can simply construct the statement with equi-JOIN? The first statement uses left joins and the 2nd is my reconstruction using equi-JOINs.. so far they produce the same results (however it could be I dont have the right kind of test data) So to summarize my questions: Why do it using LEFT JOINS which I personally find harder to read over the equi-JOIN, 2nd Do they acutally produce the same result everytime?
1st (LEFT JOIN)
-------------------------------------------------------------
SELECT action.action,
summary.gatekeepercl,
branch.branch
FROM summary
LEFT JOIN action ON summary.action=action.id
LEFT JOIN branch ON summary.branch=branch.id
WHERE summary.gatekeepercl IN (506100,506101)
2nd (equi-JOIN)
---------------------------------------------------------------

SELECT action.action,
summary.gatekeepercl,
branch.branch
FROM summary, action, branch
WHERE summary.action=action.id
AND summary.branch=branch.id
AND summary.gatekeepercl IN (506100,506101)

Right And Left Join
So I have Table named Global and one named Personal. i need to Get All Records from both tables that match a single userid. These tables may have a linking attribute (globalid to id) But it may not exist as well. The only way I have figured out how to get the correct data is to utilize 2 seperate queries. Can I get these to run a one?

CREATE TABLE `global` (
`id` int(11) unsigned NOT NULL auto_increment,
`userid` int(11) unsigned NOT NULL,
`value` int(11) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=8245;

CREATE TABLE `personal` (
`id` int(11) unsigned NOT NULL auto_increment,
`userid` int(10) unsigned NOT NULL,
`globalid` int(11) unsigned default NULL,
`value` int(11) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=379 ;
Now here is some Data

INSERT INTO `global` (`id`, `userid`, `value`) VALUES
(2442, 13, 630),
(3017, 13, 0),
(3108, 13, 78),
(3109, 16, 2220),
(3110, 16, 2220);

INSERT INTO `personal` (`id`, `userid`, `globalid`, `value`) VALUES
(324, 13, 2442, -5),
(367, 13, 3017, 10),
(378, 13, NULL, 25),
(336, 12, NULL, 2543);

And here is the current queries I am running.

SELECT g.value, p.value as PValue
FROM `global` g
LEFT JOIN personal p ON p.globalid = g.id
WHERE g.userid = 13

SELECT g.value, p.value as PValue
FROM `global` g
RIGHT JOIN personal p ON p.userid = g.userid AND p.globalid = g.id
WHERE p.userid = 13 AND g.id is null

Using A WHERE With LEFT JOIN
My problem seems pretty simple, but I'm having a tough time figuring out a SELECT statement that will do what I want.

Basically, I have two tables that look like this:

table1
userid
1
2
3
4
5
6
7

table2
myid addedid
2 4
2 5
What i need to do is find a way to list the users in table1 that are NOT listed in 'addedid' in table two.

This is the basic select statement that I'm using:

SELECT table1. * , table2. * FROM table1 LEFT JOIN table2 ON table1.userid = table2.addedid

Which lists the results like this:

userid myid addedid
1 null null
2 null null
3 null null
4 2 4
5 2 5
6 null null
7 null null
What I want to do is display only the results that have "myid" and "addedid" as null. I tried adding "WHERE users.userid!=adds.addedid" to the original SELECT statement's ending, but it doesn't work. I also tried adding "WHERE "myid=null", but that doesn't work either. ?

LEFT JOIN With MIN() - Please Help
I just can not get my head around this, so any help is appreciated.

I am building a real estate web site and need to look up properties to list them.

I need to get an image from the jos_mh_images table if one exists. If not - I still want to get the content from jos_mh_properties - thus the LEFT join.

Here is a simplified version of my query:

SELECT p.*, MIN(i.image_name_id), i.image_ext
FROM jos_mh_properties AS p
LEFT JOIN jos_mh_images AS i ON p.id = i.property_id
WHERE p.published = 1
GROUP BY p.id
LIMIT 0, 10
My issue is that the query returns the correct MIN i.image_name_id but the returned i.image_ext is not from the same row.

I would like to select the whole row from jos_mh_images where i.image_name_id is smallest and property_id = p.id

My original query has lots of other LEFT JOINS that works great - they have been removed to make the question easier to understand.

Left Join Doesn't Seem To Do It's Job?
First post to sitepoint - and it's probably the easiest solution (I just can't see it!).

I have a query between two tables (A and B). A lists all the types, and B has the occurences of these types. Example structures below:

Table A
======
type_id
type_desc

Table B
======
occurence_id
type_id
vaidator_id

MySQL
SELECT t.type_id, t.type_desc, COUNT(o.validator_id) AS validated, COUNT(o.occurence_id) AS total
FROM type AS t NATURAL LEFT JOIN occurence AS O
GROUP BY t.type_id
ORDER BY t.type_id ASC

What I would expect to happen is evey row in the type table to be returned with either nulls or the counts of the relevant values from the occurence table.

What I'm actually getting is the correct count, but only for the items that have that have a value (as if I was using a standard join). For example:

I'm getting this:

type_id type_desc validated total
================================
1 Type 1 5 7
3 Type 3 0 1

Where I'm expecting this:

type_id type_desc validated total
================================
1 Type 1 5 7
2 Type 2 0 0
3 Type 3 0 1

I get the same result with NATURAL LEFT JOIN as I do with LEFT JOIN... ON... .

Left Join Help
Using MYSQL 4.0.13, debian linux

create table members (
id unsigned int autoincrement,
name
)

create table activity (
id unsigned int autoincrement,
description
)

create table history (
id unsigned in autoincrement,
date date,
member_id unsigned int,
activity unsigned int
)

What I need:
1) only records for a particular date
2) there should be at least one record for each activity
3) there may be multiples of the same activity on a given date
4) there may be multiples of the same member on a given date
5) not all members will be listed
6) the members.name result field may be NULL

SELECT history.date, activity.description, members.name
???
WHERE history.date = 'YYYY-MM-DD'

LEFT JOIN
I have three tables: record, record_author, and author At the moment I have a search form that searches fields in the record table, but I want to extend that so people can search for records by author.

A record can be written by 0,1, or many authors, and 1 author can write many records.

I saw this example in the mysql docs:
mysql> SELECT * FROM table1 LEFT JOIN table2 ON table1.id=table2.id
-> LEFT JOIN table3 ON table2.id=table3.id;

This seems to do what I would like.. is it getting me all the authors connected to each record? but also getting me records that dont have an author?

Well I substituted my tables in the statement and mysql sort of froze, it took up 100% CPU and shut me out..

Do you know why this would happen (the tables contain around 20,000 rows each) or do you have an idea of what I could do to achieve this ?

Using LEFT JOIN
I have two tables which I am trying to perform a left join on.

table 1: master
columns | datatype
--------------------
masid | int(4)
maDesc | varchar(200)
maNote | varchar(200)

table 2: slave
columns | datatype
--------------------
asid | int(11)
masid | int(4)
uid | int(11)
cdate | datetime
title | text

my query is as follows

SELECT *
FROM `master`
LEFT JOIN `slave` ON master.masid = slave.masid
WHERE slave.uid = ? OR slave.uid IS NULL
ORDER BY master.masid DESC LIMIT 0, 30

I want to be able to display a row for each master.masid and then, depending
on whether a slave row exists that matches display a link to create on (this
is a php/mySQL project). Problem is it doesn't quite do what I want.

It only seems to return the one row with the highest master.masid if no
matching slave row exists for it, rather than listing all the masid rows for
which no matching slave row exists. It returns all the matching slave rows.

Left Join
I have gone through the database normalization process, and rewrote the queries in php to accomodate this, but now, in preparation for the next step, where the data can be retrieved, I need some help in understanding the LEFT JOIN.

I had been advised that in the many=>many format, I should create a third table, but am now wondering if that was ONLY if my host supported INNODB table types.

I have one table called workorder, where the basic information goes, and a second table where the part number and description of the parts required will go.

The WorkOrder table has the following columns - work_id(primary key, auto-increment), work(work order number (in mmhhddss format), name, site, serial, severity, issue and sched(date)

The Parts table has parts_id(primary key, autoincrement), number, description.

Do I need to add another column to the Parts Table that would have a field that is the same as the workorder table? Perhaps the workorder # or use the primary key through the use of the mysql_insert_id().

Originally I had created a third table called workorder_parts, but am thinking this is for tables that have INNODB type.

A LEFT JOIN With LIKE?
I would like to use a "LIKE" condition in my LEFT JOIN statement.

At the moment I have something like:
SELECT * FROM page AS p LEFT JOIN item AS i ON p.url=i.url

Now I want to include the subpages from i.url. So if p.url is
"www.here.com/page"
it should also match if i.url is
"www.here.com/page/subpage.html"

So the new command should look something like:
SELECT * FROM page AS p LEFT JOIN item AS i ON p.url=i.url%

Can this be done? How?

LEFT JOIN
I have three tables. Simplified, they look like this.

1. galleryData(fileID INT)
2. galleryViews(fileID INT, time DATETIME)
3. galleryFeedback(fileID INT, feedback TEXT, time DATETIME)

Now over to my problem. What I want to do is select all rows in galleryData that have a fileID that matches the fileID in galleryFeedback. Meaning, select all fileID's that have a feedback associated with it.

Now that's no problem.

But, I only want the feedback written AFTER the time the file was last viewed. There can be multible rows in galleryViews for each file (since a new row is inserted into the table every time the file is viewed). Hence, if galleryViews.time < galleryFeedback.time, then select the row.

Now, I'm no expert on LEFT JOIN, but this is what i made (that doesn't work):
SELECT galleryData.fileID, galleryFeedback.feedback FROM galleryData LEFT JOIN (galleryFeedback, galleryViews) ON (galleryViews.fileID = galleryViews.fileID AND galleryViews.time < galleryFeedback.time)

All I get from that is null in the feedback column. What I want to do ultimately is to put a COUNT on the SELECT to see how many feedbacks the user has not seen.

LEFT JOIN And WHERE
This question is about using left joins, and a where constrain on a field in the joined table. I I have a really simple query like so...

SELECT
a.field1a
,b.field1b
FROM tableA as a
LEFT JOIN tableB as b
ON a.id = b.a_id

WHERE b.this = 1

Left Join
If I do

select "a.id, b.id from table_a a left join table_b b on a.id = b.id";

it works. But when I try to add a third table,it is failing.

select "a.id, b.id, c.id from table_a a left join table_b b on a.id = b.id left
join table_c c on a.id=c.id";

Is there something wrong with this second statement?Does the second left join combine table c with the first two or is what's joined determined by the on clause?

Why would I want to use left join as above instead of instead of the following?

select "a.id, b.id from table_a a, table_b b where a.id=b.id

Left Join
I've been struggling a with a sql statement for the last couple of hours.
Here follows the databasestructure:

CREATE TABLE `admins` (
`admins_group` int(11) NOT NULL default '0',
`admins_uid` int(11) NOT NULL default '0',
`admins_rights` int(11) NOT NULL default '0'
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

CREATE TABLE `groups` (
`groups_id` int(11) NOT NULL auto_increment,
`groups_name` varchar(255) NOT NULL default '',
`groups_desc` text NOT NULL,
UNIQUE KEY `groups_id` (`groups_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

So, in the admins-table users get assigned admin permissions and admins_group links to which group they can administrate.

An example:
INSERT INTO `admins` VALUES(2, 1, 3);
INSERT INTO `groups` VALUES(2, 'Members', Add and change members.');

The user with ID 1 has access level 3(read/edit/write) to group 2 which is "Members". That means that the user can read, edit and add new members.

Left Join: All From T1 Even When Not In T2
I have two tables, product and prod_report.  I want my query to return a list of product names, IDs, and URLs.  If there is an "admin" report for a product, I want it to return the values from that report.  Otherwise, I want null values for the report fields.

This seems to me like it should be easily done with a left join, but it's not working for me.  The results only include products that have admin reports.

What am I doing wrong?

CODEselect
    prod.prod_id,
    prod.name,
    prod.url,
    pr.rank,
    pr.comment,
    pr.report
from
    product prod
    left join prod_report pr on prod.prod_id = pr.prod_id

Left Join
I have two tables (say, A and B) that both have the same keyfield (say, theKey)
For each record in A there is either one or zero records in B. In my case,
almost all the A records have a corresponding B record.

I want a join that will give me A-B even if there are no records in B
(just fills B fields with nulls)

if I use join (eg 'select * from A join B on (theKey); ) it is
real fast, but leaves out those few A records with no corresponding
B records.

if I use left join (eg 'select * from A left join B on (theKey); )
it does exactly what I want, but at a 30X speed penalty ?!?!

Can someone show me how to optimize the left join statement
to run faster?

A Left Join
I am trying to hide the whole record when uw.waction != 2 but its just hiding the userwindow columns... what am i doing wrong?

Regards,
Jason

CODESelect th.tid, th.tname, th.tmain, th.tcolor,coalesce(c.postnumber,0) as pnumber, coalesce(c.ptype0number,0) as rnumber, th.tlastreply, th.treplyby, uw.wuid, uw.wwid, uw.waction
FROM thread AS th left JOIN ( SELECT ptid, sum(ptype=0) as postnumber, SUM(ptype=1) as ptype0number
FROM forumpost GROUP by ptid) as c on c.ptid = th.tid
LEFT JOIN userwindow uw ON uw.wwid=th.tid AND uw.wuid=2 AND uw.waction != 2 WHERE th.tmain=1 ORDER by th.tname;

Left Join
A website that I am working on deals with student data. The student ID number is the primary key in the student_year_independent table. My problem involves a report that attempts to find "potential" duplicates based off of varying fields.

LEFT JOIN
I am trying to join to tables and exclude the total rows with the rows in one of the tables. I can't get it to work correctly. I only want one row per language in the following sql:

SELECT l.id,l.longname
FROM languages l
LEFT JOIN project_langs pl ON l.id != pl.language_id
WHERE pl.project_id = '1'
ORDER BY l.longname

project_langs contains 4 rows right now. These should be withdrawn from the result. Right now I get 4 results for each language except the one included in "project_langs" where I get 3 results.

Left Join
I'm trying to write a SQL statement but I'm not getting the results I want..... I'm hoping someone can help me. Basically I'm trying to implement user-specific checklists.
4 tables.
Category (id, title)
Check_box (id, fk_cat_id, title)
User(id, username)
User_check_box(fk_user_id, fk_cb_id)

What I want is Category.title, check_box.title, and a checked (True/False) given a category id and username.

I have left joined each table on to check_box, but all as soon as I add the username to the where clause, it only returns the true rows.

Left Join
SELECT *
FROM wpo
LEFT JOIN company ON company.company_num = wpo.company_num
-----------------------------------------------
But this does not work -- I get the following error
#1054 - Unknown column 'wpo.company_num' in 'on clause'
------------------------------------------------
SELECT *
FROM wpo,wpo_dtl
LEFT JOIN company ON company.company_num = wpo.company_num
WHERE wpo_dtl.wpo_num = wpo.wpo_num
---------------------------------------------

How come wpo.company num exist in the first SQL but not the second?

Left Join With LIKE
hey I'm trying to join two tables on whether one text field is inside another text field, is there someway to do this?
something along the lines of
table1.field1 LIKE '%' & table2.field2 & '%'

Left Join
I have this SQL

SELECT monthname( quote_agent.thisdate ) as theMonth,
COUNT(quote_agent.quote_id) as QUANTITY,
SUM(quote_blinds.blind_price) as TOTAL
FROM quote_agent LEFT JOIN quote_blinds
ON quote_agent.quote_id = quote_blinds.quote_id
group by monthname( quote_agent.thisdate )

My problem is this:
The table quote agent has 270 agents.
I get
Month|QUANTITY|TOTAL
April 169 178529
March 375 331480
May 70 64388

Im not sure what kind of join I must use here?

Left Join
I am joining these two tables but I am getting many extra rows:

PHP Code:
SELECT DISTINCT `ALL_PHS_SCHEDULES` . `student_last_name` , `ALL_PHS_SCHEDULES` . `student_first_name` , `ALL_PHS_SCHEDULES` . `id`,
`guidance-phs`.`id`
FROM `ALL_PHS_SCHEDULES` LEFT JOIN `guidance-phs` ON `ALL_PHS_SCHEDULES` . `id` =`guidance-phs` . `id` AND `ALL_PHS_SCHEDULES` . `grade` >8 AND `ALL_PHS_SCHEDULES` . `grade` <12

I should be getting about 1,000 rows but I'm getting 27,000 instead and that is how many rows are in the `ALL_PHS_SCHEDULES` table.

Left Join
I need some help tweaking this query. I have a table of pictures, and then a seperate table of comments. I'm trying to sort a page based on the number of comment rows for that particular picture. So the most comments for one pic displays at the top. Here's what' I've tried. (The != '' isn't working either - I only want values that have a comment)

SELECT p.*, c.*
FROM pictures p
LEFT JOIN comments c ON p.picture_id = c.picture_id AND c.comment != ''
WHERE p.indicator != 'classified'
ORDER BY c.comment DESC

Left Join
mysql_query("SELECT DISTINCT U.id,U.username FROM users U LEFT JOIN ct_info T ON U.id=T.host WHERE U.id>0 AND T.status!=1 ORDER BY U.username");
However it's not outputting what I require. I understand why, just don't know how to correct it.
I want it to list all Users, except those that have a tournament listed in ct_info that has a status of '1'.
What it is currently doing is looking through the tournaments in ct_info, and for each one it checks the status.. and if it's not 1, it will list the user. Problem is, not every user has hosted a tournament. So I kind of require it to do the 'back-the-front' for what it is doing.

Left Join
SELECT table1.* table2.group
FROM table1
LEFT JOIN table2 ON table2.%family% = table1.last_name

I know the above query isn't correct, just trying to demonstrate my intention. I'm trying to join table 2 on table 1 by means of a comparison string. Table 1 has a column varchar(32), while table 2 has a column text which contains strings, comma delimited, each of while cooresponds to a varchar field in table 1.

LEFT JOIN
I have been using mySQL for some time, but am inexperienced at JOINs. I have 3 tables, Cats_Play,Cats_Age,and products. The Play Categories have multiple (but not all) Age Categories in a field (Ages).
When a user is "in" an Age Category (say Infant), they are offered a selection of Play Categories (0-3Mo, etc. for Infants, Active & Outdoor for older ages etc.) appropriate to that Age (infants don't have Art Supplies etc.). That part is done (by having the Ages field in Cats_Play).
NOW-I need to further filter the Play Categories by showing ONLY those Play Categories (links) that have products attached. Products have both Age and Play Categorie (products.CategoryPlay and products.CategoryAge). These fields have multiple values separated by spaces, making "LIKE" matches necessary, instead of "=".

A LEFT JOIN With LIKE
I would like to use a "LIKE" condition in my LEFT JOIN statement.

At the moment I have something like:
SELECT * FROM page AS p LEFT JOIN item AS i ON p.url=i.url

Now I want to include the subpages from i.url. So if p.url is
"www.here.com/page"
it should also match if i.url is
"www.here.com/page/subpage.html"

So the new command should look something like:
SELECT * FROM page AS p LEFT JOIN item AS i ON p.url=i.url%

Can this be done? How?

Left Join
I need to get those "objects", that have a "location_id" of a "location" -object, that has been deleted. So looking for orphans here...

Code:

SELECTo.id,
o.name
FROMobject o
LEFT OUTER JOIN location l
ON o.location_id = l.id

Left Join
I have a BOOKS table with the columns ISBN and TITLE. I have a list of
5 isbn-numbers and I would like to find out their titles if they are
in the database, or null if they are not. In other words, I would like
to do a LEFT JOIN (or is it RIGHT JOIN?) like this:

SELECT ISBN,TITLE FROM BOOKS ON
("123243434X","4352542555",4242343241")

the result could be

123243434X | "book1"
4352542555 | NULL
4242343241 | "book2"

I'm not sure of how to create my sql syntax. The above gives me an
error.

Locking Table?
I've got two tables that control my menu on my site (which is coded in ASP).
The first table (cam_menu) contains info about the sort order of the menu
and the second about my content. To insert a menuitem above a previous
menuitem I use these SQL-sentences: Code:

Table Locking
I just have a question about the table locking in 3.23.56. If I am inserting a
row into a table that is MyISAM type, is the entire table locked? And, if so,
what does this mean for concurrent selects?

About Table Locking
One thread insert data to a specific table continuously using 'LOAD DATA INFILE..' or 'INSERT INTO ...' query. Actually, this table maintains only for one hour's data, so the delete query is performed before every insert query.
At the same time, another thread issues select query on that table periodically.

What I want to know is, whether it is free from the table corruption or not. Table level locking is performed in MySQL server? Or, I have to do explicitly?

Left Join In V. 4 Not Compatible
I have the following left join that works fine in v. 5 of mysql but does not work in v. 4. can someone help me simplify the query to where it will run in both?

$sql = 'select * from partoption left join (select optionID, value as price from perfdataoption where perfdataID = 5) pricedata on pricedata.optionID = partoption.partID where groupID ='.$row['groupID'];

LEFT JOIN Logic
I have several database that I need to link with a left join. I thought and thought on this and I believe the method I am going to use is the best. Basically I have 3 Tables that need to use a LEFT JOIN. A Members Table, A Group Table and a RSVP table.

When a member RSVP it insert a record into the RSVP TAble with MemberID, EventID and Their Status.

I am setting up a form where the Event Planner Can Change Members RSVP status. I made it very simple with 2 Drop Down Boxes. One that has a list of everyone that has RSVPed Yes. The Planner can just select the name click go and the Member will automatically be set to NO on RSVP.

The other Drop Down will be a List of Members names that have RSVPed No,Like, Not Likely or Nothing at all. (If they have no reponse then there won't be a record for them in the RSVP table)

Here is a sample of my non-working code.

PHP

SELECT rsvp.attending, leaguemembers.nick, members.firstname, members.lastname, members.memberid FROM members, leaguemembers LEFT JOIN rsvp USING (memberid) WHERE rsvp.eventid = 'xxx' AND rsvp.memberid = members.memberid AND members.memberid = leaguemembers.memberid ORDER BY members.firstname

This Query Only Returns A Result for people that have RSVP something and not members that have not responsed.

Left Join With Count
I'm just getting a big mental block when trying to write this query.

DB SCHEMA

SUPPLIERS
supplierID, name, website

PRODUCTS
code, supplierID, name, photo
Products belong to suppliers, supplier may have none, one or many products. (1:M)

I need a query to produce ALL suppliers, and a count of products where the photo field is empty (it's varchar).
I can do the LEFT JOIN to list all suppliers and a product, but I don't know how to do the count WHERE p.photo = ''

Each supplier should appear exactly once in the result set.