MYSQL :: Multi-tables LEFT JOIN

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.

How To Use Left Join For 3 Or More Tables.
I am using mysql 4.1

SelectCustomer NameCustomer POSalesOrder POProduct Name

1.CHARLES 270086 05480 BCSLS
2.CHARLES 270086 05481 BCSLS
3.
4.

4 tables totally connected.

Transaction tables - 2
salesordermaster
salesordertran

master tables - 2
customer
products

i ll fetch the customer name from customer master table with reference from salesordermaster.
i ll fetch the product name from the product master table with reference from salesordertran.

see , the customerpo are same for above 2 records, it is from salesordermaster table.
and salesorderpo are different for above 2 records, it is from salesordertran table.

if none of the records are in salesordertran table, i need the display atleast customername and customerpo which is from salesordermaster.

(so far, i had tried with left join between salesordermaster and salesordertran, but salesordertran reference the product master table for the productname, so the resultant query fetches some records with cross product. is there any iff conditions in mysql?? can be used it here?)

Left Join For Three Tables
I have three tables:

1-clients:
cli-no
cli-name

2-movies:
mov-no
mov-name

3-bills:
bill-no
cli-no ( fk from clients table)
mov-no ( fk from movies table)

I try to make a query to have this result:
cli-no | cli-name | bill-no | mov-name

For the clients either they has bills or not (null value for clients with no bills)

I tried this:

mysql>  select clients.*,bill-no,mov_name
    ->  from
    ->  clients left join bills on  clients.cli_no=bills.cli_no
    -> ,   movies where bills.mov_no=movies.mov_no;

It gives me data for clients that has bills and not for all the clients.

And this one

mysql>  select clients.*,bills.bil_no,movies.movie_name
    ->  from
    ->  clients left join bills on
    ->  clients.cli_no=bills.cli_no
    ->  movies left join bills on
    ->  movies.mov_no=bills.mov_no;

I have this error

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax

to use near 'movies left join bills on
movies.mov_no=bills.mov_no' at line 5

LEFT JOIN With Multiple Tables
I've got a query that does a LEFT JOIN.

Something like this:

SELECT a.id, myB.someField, c.someColumn
FROM c, a
LEFT JOIN b AS myB ON (myB.id = a.id)

However, if I reverse the two tables in the FROM clause, it doesn't work anymore. It seems like the table in the ON part has to be the last table mentioned in the FROM clause.

Curiously enough, it DOES work on MySQL 4 (4.1.16-nt), but NOT on MySQL 5 (5.0.45-community-nt).

This might not seem much of a problem, but sometimes I want to do more LEFT JOINs in one query, with different tables in the ON part. And I don't see how I can do that in MySQL 5.x, as all the tables would all have to be last in line in the FROM part.

SELECT From Two Tables + LEFT JOIN
Since MySQL v5 was released our script stoped working due to mysql query errors.
i.e:
SELECT a.id
FROM table1 a, table2 b
LEFT JOIN table3 c ON (c.id = a.id)

This will return error:
Unknown column 'a.id' in 'on clause'

To solve this we can do in two ways:

1) change tables order in FROM statement:
SELECT a.id, b.id, c.id
FROM table1 b, table2 a
LEFT JOIN table3 c ON (c.id = a.id)

Or
2) use brackets in FROM statement:
SELECT a.id, b.id, c.id
FROM (table1 a, table2 b)
LEFT JOIN table3 c ON (c.id = a.id)

Both ways works, but I have question to the experts: which way is the most optimized/better?

Using A Left Join With 2 Values Linked To The Same Tables
I have a table that has two values, author_id and approver_id. Both of these refer to the value id in my users table.
Is it possible to use a left join and have the query get the author and approver name (also in the users table)? Or will I have to use multiple queries? (Can't use nested selects, this particular server is still running MySQL3 -sigh-).

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.

Multi Row Join
Is there anyway to do a multirow join?

Example:

Table A has two columns:

B_ID_1 and B_ID_2

both these ID's refer to a row in Table B.

How can I do a join such that I know what data is from the first row and what is from the second row? I can't just refer to the columns by their name since there are now multiple colums with the same name, and they are from the same table...

Multi Table Join Assistance
I am trying to implement a user email alert
system which will allow a user to have emails sent to them daily, weekly or monthly and will send them jobs that are found to match their criterior.

I have a table of that stores details of a users alerts shown below.

tbl_alerts
-----------

alert_id
user_id
alert_frequency
alert_categories
alert_keywords

The categories field holds a comma seperated list which refers to the table "job_categories" and the keywords is a string that is used to search.

What i would like to know is the query that i can use to get the latest 10 jobs added in the past 24 hours that match the values in this table....

so in english an example would be something like

"I would like Alerts Daily

for any jobs added to Administration, finance

with the keywords 'heflkemf lkmf lkemf '"

so im guessing there is going to be a join on the jobs table, the categories table and the alerts table... these are shown below

tbl_jobs
----------
job_id
job_title
job_description

tbl_categories
----------
category_id
category_name

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.

Multi Table Join Angst And Book Recomendation
I cannot use a subquery.

Drivers table
driver_id
drivername

LapTimes Table
driver_id
laptime
track

So the info I want to Select is drivername, laptime, track where laptime = min(laptime) for each track

So I'm stuck. I tried something with a temp table but I get lost with joins on joins and aggregates and aliases. Which brings me to my second question on a good SQL book that has good explanation and samples of building advanced queries.

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.

Multi-tables And Pairing Category
I have one table that has category numbers and names and one with data that refereces the category numbers. I need to SELECT * from one table and match up the category numbers to the names that they equal from the other table.

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.