MYSQL :: Problem With Left Join And Count, Returning More Rows Than What It Should

Agregate Count Return All Rows, Left Join
I have 2 tables on a lyric discussion site

Titles
   title_id
   title_name
   title_lyricist
   title_lyrics
   title_artist
   title_entered

And POSTS
   post_id
   title_id
   post_author
   post_text

I want to be able to list all the titles, and count the # of posts for each title. The problem is some titles have zero posts so my query ignores thos titles, butI syill want them returnd where Ill add a ')' for count.

This is the best i could come up with, but still only those that have posts are returned. I still want the titles that don't have a match in the posts table to be returned with a total_count of zero.

LEFT JOIN Returning NULL Values
I am trying to perform a LEFT JOIN on a table which may or may not have
matching rows.

If there are no matching rows, it returns NULL's for all the missing fields.

Is there anyway of returning the default values for that table instead of
NULL's, in a portable way?

My query currently looks like this:

SELECT i_product.name, i_product.price, `i_tax-rate`.rate
FROM i_product
LEFT JOIN `i_tax-rate` ON (`i_tax-rate`.`tax-rate-id` =
i_product.`tax-band-id`)
WHERE i_product.id = 123';

Returning Rows In Left Table Based On Mutliple Criteria In Right
This is starting to get to me. I'm sure there's a simple way of handling what i'm trying to do, but someone might be able to help out quicker than spending another hour searching and testing for this. Here's the problem:
Simplified tables:
ARCADES
=======
ID,
name
GAMES
======
ID,
name
ARCADES_GAMES
=============
ID,
arcade_iD
Games_ID
Straightforward enough so far right? I'm trying to get all arcades that have ALL games in a passed in set of game_id's. So for instance I might want all arcades that have the games 11 and 14, but it must have both those.
I can do soemthing like...

SELECT a.name
FROM arcades a
WHERE EXISTS(SELECT 1 FROM arcades_games
WHERE arcade_id=a.id AND game_id IN (11,14))
But that'll return all rows that match ANY of (11,14) rather than ALL of 11,14.

It all comes down to the simple thing of getting rows in a table where all criteria from a list is met, but any advice on how i would do something like this?

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.

Left Join + Count
I have a categories table and a tutorials table, I would like to left join the tutorials to count them, I must check to see if the tutorial is in cat1 & cat2, I do realise i need a GROUP by however I still can't get it to work with a group by...

sql = "Select cat.cid, cat.cname, cat.ctype, tut.tcat, COUNT(tut.tsubcat) as CategoryCount "_
& "FROM categories cat LEFT join tutorials tut on tut.tcat=" & cid & " AND tut.tsubcat=cat.cid WHERE cat.ctype=3 ORDER BY cat.cname ASC"

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?

LEFT JOIN Produces Extra Rows
The `suggestions` table contains 2,265 rows. The `ALL_PHS_SCHEDULES` table contains over 22,000 rows. I have done my homework and I read that the Left Join return ALL rows from the first table and the matching ones from the other table. I am expecting exactly 2,265 but the query is returning 2,734 rows.

Quote: SELECT *
FROM `suggestions`
LEFT JOIN `ALL_PHS_SCHEDULES` ON `suggestions`.course_number = `ALL_PHS_SCHEDULES`.course_number
AND `suggestions`.section = `ALL_PHS_SCHEDULES`.section AND `suggestions`.id = `ALL_PHS_SCHEDULES`.id
ORDER BY `ALL_PHS_SCHEDULES`.`course_number` , `ALL_PHS_SCHEDULES`.section ASC

Trying To Pull Id, Count And Title But Lose Rows When I Add Extra Join
i'm trying to extract some information from my database, the query being

PHP

SELECT grps_c.catid, grps_c.title, COUNT(grps.groupid) AS COUNT
            FROM grps
            RIGHT JOIN grps_category grps_c ON (grps_c.catid = grps.catid)
            GROUP BY grps_c.catid
            ORDER BY grps_c.title

which works fine. however some of the groups (grps.groupid) are hidden and i don't want to count them, so my thinking was add

PHP

LEFT JOIN grps_setting grps_s ON (grps_s.groupid = grps.groupid AND grps_s.hidden_group != Ƈ')

however adding that removes the rows that have a 'count' or NULL or Zero.

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.

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.

How Returning Zero With COUNT()?
I have this table:

CREATE TABLE `people` (
`id` int(11) NOT NULL auto_increment,
`name` varchar(100) NOT NULL default '',
`sex` char(1) NOT NULL default 'M',
`age` tinyint(4) NOT NULL default '0',
PRIMARY KEY (`id`)
) TYPE=MyISAM;

And I want knon how many Females and Males there are in three ranges of ages:
from 0 to 20, from 21 to 30 and over 31.
I've used COUNT() GROUP BY(sex) but the problem is that COUNT() doesn't return zero
but I want see the exact count of peoples of every ranges of age (for sex) including zero.

This is my query:

SELECT sex, ' < 20' AS range, COUNT(*) AS Num FROM people WHERE age between 0 and 20 GROUP BY(sex)
UNION
SELECT sex, '21-30' AS range, COUNT(*) AS Num FROM people WHERE age between 21 and 30 GROUP BY(sex)
UNION
SELECT sex, ' > 30' AS range, COUNT(*) AS Num FROM people WHERE age > 30 GROUP BY(sex)

How can I see zeros?

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

Returning Of Delete Rows
Is there any way to return deleted rows?
I mean I've delete a rows in mysql5 with " delete from ... " query is there any way to restore those rows?

Sql Returning Too Many Results On JOIN
I have three tables which I'm joining...

AGENTS
========
agent_id
commission
promo_code

AGENT_PAYMENTS
==============
payment_id
agent_id
amount

SALES
========
sale_id
promo_code
status

I need to select all the sales info where an agent's promo code has been used (and the status of the sale is "C" (complete)). THe trouble is, the SQL I've used is doubling, trebling etc the sum of the payments made to an agent - this depends on how many results are returned for the number of sales... eg: 4 sales means the SUM is being multiplied by 4.

here's the SQL Code:

Returning Limited Sequential Rows
I have a query that returns to me all the seats that are available,
which is great but the ideal solution would be to return X number of
seats based on a seatnum (Integer).

For example, if seats 10, 11, 12 and 15-20 are available and I need 4
of them, I should get back seats 15,16,17,18.

I'm pretty sure that some sort of IF statement with a variable in the
query is the answer, but I can't seem to push through on it.

NOTE: seatnum is NOT a primary key and is not autoincrement. An
autoincrement col wouldn't work for this project as the sections, rows
and seats might get edited as things change in the hall. So I'm doing
the seat numbering myself.

If anyone could give me shove in the right direction, I'd appreciate
it. After many Google searches for "sequential" rows or records and
the like, all I get are people wanting to return record counts.

Returning Rows Older Than 6 Months.
I have a DB packed with orders, and am trying to do a query that returns all the results that are older than 6 months, I wrote a query to find all results between now and six months ago but don't know how to return results older than six months and nothing more..

the date part of the query i had was...

DATE_SUB(CURDATE(),INTERVAL 6 MONTH) <= orderDat

Simple Select Returning More Than One Rows
I would like to ask if it's possible to do a select 1,2,3 and to be returned 3 rows and 1 column with values 1 for the first row,2 for the second etc. select 1,2,3 returns me a row with 3 columns.

Returning Rows Where Field's Value Starts With A, B Or C
I'm trying to pull all the last names that start with A-C, D-F, G-H, etc.  I've tried to use

   select * from mytable where LastName like '[ABC]%'

...but while it makes perfect sense to me, MySQL is having none of it.  I'm sure the syntax is off, but the manual isn't offering anything clear-cut as an alternative.

Anyone have any ideas?

MySQL Returning Redundant Rows
I have 36 records in three tables, each row is related to one row in each table. Up until I add the last where condition (staff_descr.certs LIKE ...), it's fine. But with that last condition, it suddenly returns 108 rows, each row repeated 3 or 4 times.

PHP Code:

SELECT staff.obj_id , staff.timestamp , staff.full_name , staff.city , staff.state FROM `staff` , `staff_descr` LEFT JOIN `user` ON user.username = staff.username WHERE user.is_confirmed = 1 && staff_descr.certs LIKE '%Basic Lifeguarding%'

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)

Returning Multiple Rows From A Stored Procedure..
I have a country table with code and name columns and create a stored
procedure 'get_countries()' but have no idea what is the syntax to
return multiple rows.

I have searched the newsgroups and understand that the solution is to
use cursors. Searching for the mySQL solution is to no avail.

Multi-table Queries Returning 0 Rows
I'm working on a Windows client program in C++, using Qt and OleDbPro (for
database access). Everything was working great until I had to use a query
for the first time in the code that pulled data from two tables. The query
executes, but returns 0 rows. I verified that the query should return rows
by running the query both through another client using an ODBC DSN, and
using phpMyAdmin; both times, I got the result I expected.
I'm using the following connection string:
Driver={MySQL ODBC 3.51
Driver};server=2ksvr;database=mydb;uid=user;pwd=pa sswd;option=16386
I tried with and without using the JOIN format, someone suggested some ODBC
drivers don't work well with JOINs, but didn't make any difference
So, the question I have is, is there something special I have to do for a
DSN-less connection using an ODBC provider?

Subquery Result Returning Many Rows As Single String
Is it possible to return multiple rows from a select statement as a single string?

Example:

Prepared Statement In Stored Procedure Not Returning Rows To App
I have a stored procedure that I'm using to allow users to perform a search on the database. I'm compiling the select statement in the stored procedure and then using PREPARE, EXECUTE, DEALLOCATE.

When I run just the stored procedure it works. When I call it from the app it returns 0 rows. If I just put the SQL statement without using PREPARE it returns rows. Here is the DDL for my stored procedure:

Multi-table Queries Returning 0 Rows With DSN-less Connection ViaOLEDB
I'm working on a Windows client program in C++, using Qt and OleDbPro (for
database access). Everything was working great until I had to use a query
for the first time in the code that pulled data from two tables. The query
executes, but returns 0 rows. I verified that the query should return rows
by running the query both through another client using an ODBC DSN, and
using phpMyAdmin; both times, I got the result I expected.

I'm using the following connection string:
Driver={MySQL ODBC 3.51
Driver};server=2ksvr;database=mydb;uid=user;pwd=pa sswd;option=16386

I tried with and without using the JOIN format, someone suggested some ODBC
drivers don't work well with JOINs, but didn't make any difference

So, the question I have is, is there something special I have to do for a
DSN-less connection using an ODBC provider?

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?