MYSQL :: Using LEFT JOIN

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.

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.

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 ?

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

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

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.

Problems With JOIN LEFT
SELECT i.id AS id,i.nimi AS nimi,i.riik AS riik,o.hind AS hind,a.suhted AS suhted, FLOOR(o.hind-(a.suhted/100*(o.hind/2))) AS lopphind FROM import_firmad AS i LEFT JOIN import_kaubad AS o ON o.firma=i.id AND o.kaup={$_POST[materjal]} LEFT JOIN import_suhted AS a ON a.firma=i.id AND a.kasutaja={$usid} WHERE o.kaup={$_POST[materjal]} AND o.kaup = ANY (SELECT id FROM yld_esemed WHERE tyyp=2) ORDER BY lopphind DESC
If there isn't any matches in import_suhted table, then lopphind returns empty string, but it should just return o.hind.

LEFT JOIN Question...
I have a fairly simple question... I have a 'users' table that holds member info and a 'usergroup' table that assigns a specific usergroup(s) (like advertiser, best avatar, etc) with a specific user. For instance, most users do not have a record in the 'usergroup' table, but those who are members of a usergroup, have a record in the'usergroup' table. Sometimes they have more than one, if they are a member of more than one usergroup.

I have a forum-like application that outputs posts made by users and I want to also retrieve each users usergroup information (if they have some) with this query. The problem I am running into is that it is only returning one usergroup record if more than one usergroup record is present for a user. So if a user has say 5 usergroup records in the 'usergroup' table, I would like to have all 5 records within the data set so I can use it in my application. By using the LEFT JOINS below, it is only returning one of the records.

Is there a way to return all the records in the 'usergroup' table that belong to the specific user the while loop is on (mysql_fetch) in some sort of array or something? Thanks for your help!

PHP

SELECT `u`.*, `ug`.*, `p`.* FROM `users` AS `u` LEFT JOIN `usergroup` AS `ug` ON (`u`.`usergroupid`=`ug`.`usergroupid`) LEFT JOIN `posts` AS `p` ON (`u`.`userid`=`p`.`userid`);

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

Table1
Id | Field1 | Field2

Table2
Id | Table1Id | Field1 | Field2

Query....

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

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

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

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

Left Join W/WHERE Clause
I'm trying to impose a WHERE clause on a LEFT JOINED table without defeating the purpose of the LEFT join.
i.e the WHERE should filter the results of the right table without requiring them to exist.

I hope that makes sense. Using a normal WHERE condition on fields from the right table would mean that parent rows from the left table would not be selected if they had no right table counterpart.

Example:

SQL
SELECT a.ID, a.person, b.phoneFROM a LEFT JOIN b ON b.parentID = a.IDWHERE b.phoneType = 'home'

This would limit the result set to people with home phone numbers. I want to get ALL people, and IF they have a home phone number, get that as well.

LEFT JOIN Update
I am trying to update my ranking table with reference to its parent table 'tut_db'. The following query only updates ranking table row if a row already exists in ranking table with its parent id.

PHP

UPDATE tut_db LEFT JOIN ranking
    ON (tut_db.id=ranking.tut_id)
    SET
    ranking.uvote=ranking.uvote+1, ranking.votes=ranking.votes+1
    WHERE tut_db.id=$id

I also tried the following:

PHP

UPDATE ranking
    SET
    uvote=uvote+1, votes=votes+1
    WHERE tut_id=$id

If a row exist with tut_id it update. Is I need to check first that if tut_id=id exist then update else INSERT?

LEFT JOIN, Need To Get All Fields In A Row
HTML
SELECT * FROM table_1 LEFT JOIN table_2 ON table_1.type=table_2.t_id WHERE card_id (table_1)=5
a problem I have is the following:
Only the t_id from table_2 gets retrieved, whereas I actually have to retrieve all the fields from table_2...how could I retrieve all the fields? Do I need to add conditions or use a different kind of join?

Mysql 5 Left Join
I am trying to upgrade to mysql 5 and have run into a problem.

I have this query that runs perfectly on mysql4:

SELECT product.title, product.alias, product.descr, pop_marks.special_char as mark, categories.alias as category_alias FROM product , product_categories, categories, featured_products LEFT JOIN pop_marks ON pop_marks.markid = product.mark WHERE featured_products.productid=product.productid AND product_categories.productid=product.productid AND product_categories.categoryid = categories.categoryid GROUP BY product.productid ORDER BY RAND() LIMIT 1
WHen I run this same query on mysql it tells me "Unknown column 'product.mark' in 'on clause'". The database is an exact copy from the mysql 4 one (I uploaded from an exported .sql). The column is there I can see it.

Left Join Problem
TABLES:

setup_main
sid (primary)
sdata1
sdata2

output1
oid
sid
outdata
outdate_end

output2
oid
sid
outdate_end

output3
oid
sid
outdata
outdate_end

Query:

SELECT
s.sid, s.sdata1, s.sdata2
FROM
((setup_main AS s
LEFT JOIN
output1 AS o1 ON s.sid = o1.sid)
LEFT JOIN
output2 AS o2 ON s.sid = o2.sid)
LEFT JOIN
output3 AS o3 ON s.sid = o3.sid
WHERE
(
o1.outdate_end
BETWEEN
'PREVDATE' AND 'CURDATE'
OR
o2.outdate_end
BETWEEN
'PREVDATE' AND 'CURDATE'
OR
o3.outdate_end
BETWEEN
'PREVDATE' AND 'CURDATE'
)

The query returned results not according to date range (PREVDATE and CURDATE), may I ask for a help on how do I achieve having a results based on the given PREVDATE and CURDATE variables...

Optimizing A Left Join
SELECT a.x FROM a LEFT JOIN b ON a.fk = b.pk ORDER BY a.d DESC LIMIT 10;

If I were MySQL, I would perform the above query by first ordering table a by a.d, then limiting by 10, then joining with the table b. However it seems that MySQL is doing something much much more difficult, since running this query takes 2 minutes. Here is the EXPLAIN from it, but I'm not quite sure how to read it.

+--------------+------+---------------+------+---------+------+------+---------------------------------+
| table | type | possible_keys | key | key_len | ref | rows | Extra |
+--------------+------+---------------+------+---------+------+------+---------------------------------+
| a | ALL | NULL | NULL | NULL | NULL | 3913 | Using temporary; Using filesort |
| b | ALL | NULL | NULL | NULL | NULL | 9471 | |
+--------------+------+---------------+------+---------+------+------+---------------------------------+
Can you see from this EXPLAIN whether MySQL is doing 3913*9471 operations ? I would think that sorting the 3913 record table + doing 10 seeks on the 9471 record table should be enough. I could easily force MySQL to "do the right" thing by just emulating this join with PHP code,