MYSQL :: Straight_join, Join Order & Join Conditions

Join Conditions And WHERE Clause Conditions
I'm trying to get a clearer understanding of the difference between Join conditions and the more standard WHERE-clause conditions.

I only discovered Join conditions a few months ago, and I really like them because they allow me to separate the conditions I wanted for joining tables out from the conditions I wanted to use to restrict what kinds of rows I wanted. This gives me queries which are much more readable.

Some of the queries I have written now involve joining as many as 6 tables together. In some of these, I only want to join as subset of one table to the other and one or two queries involve a NOT EXISTS subquery which I am rewriting as a LEFT JOIN (because, as I understand it, in general, MySQL is better at joins than subqueries, and I dont have the data yet to work with to see some performance statistics and compare them).

Because of this, there have been a few times where the natural course of action has appeared to be to put a condition that isn't equating two columns in different tables into my join condition. And MySQL doesn't appear to complain.

For example, if I have a table (Table1) whose rows refer to "jobs". A particular job can be referred to by many rows in Table1, but I only want the jobs which are active (Jobs.active = 'y'). Because I only want to link Table1 to active Jobs, I CAN put this condition in the join condition, like so:
FROM Table1 JOIN Jobs ON Table1.jobID = Jobs.jobID AND Jobs.active = 'y'

My question is, what are the differences between putting conditions in the FROM clause and the WHERE clause?

Are there any? Does MySQL treat them exactly the same?

I am asking, because I believe if I have a good understanding of the differences (if there are any), I will be more able to make a better judgement on which clause the condition should be put into.

=================================================================
On the other example, of the subquery, if I have a sort of messaging system that sends and receives messages with outside agents. For statistical purposes, when I broadcast a message to multiple people, I want to be able to count the number of people who responded to it.

So, when I receive a message from someone, I need to relate it to the last message I sent out to that person (and count it), BUT if the person sends the system more messages without having received one from the system, I dont want them to be counted.

BroadcastResponses is the table that records these statistics. A row is inserted into it when a broadcast is sent out.

ReceivedMsgs is the table that records messages I receive from users.
My first atmysqlt went like this: ....

View Replies !

Conditions On Left Join
I'm having trouble constructing this left join query. I want to retrieve all rows from tProducts, and relevant matches from pictures, however what I have got just jams up the server and I have to restart. Am I doing it wrong?

SELECT prodCode, prodName
FROM tProducts LEFT JOIN pictures
ON productID = relProdId
AND prodCode = 'SF2'

View Replies !

Left Join Conditions
Two tables.

table1, table2

Table 1 contains all unique items.
Table 2 contains x references to table 1, unique by customerId

I want to do a join onto table2 where table1.id = table2.table1Id AND table2.customerId = the user's ID

I can't quite figure out how to work in the idea of an AND into my left join. Unless both those conditions are two, I don't want any data from table2.

Only solution I can think of so far is to just left join on the id's, but select the customerId from table2, and as I loop through, check to see if it's supplied and the right id, and keep track of duplicate items from multiple customers myself.

Suggestions?

View Replies !

Join To Multiple Tables With Conditions
activities , projects, AND project_messages, project_writeboards, project_art_work ...etc

the projects_messages and project_writeboards belongs to projects, and the activities model have the following fields:

id, user_id, action, item_id, item_type, created_at, updated_at.

im trying to do some joins to get a specific project activities from the diferent project_*models*. so this is my sql:

SELECT `activities`.* FROM `activities`
join project_writeboards on item_id = project_writeboards.id and item_type = "ProjectWriteboard" AND project_writeboards.project_id = 1
join project_messages on item_id = project_messages.id and item_type = "ProjectMessage" AND project_messages.project_id = 1

but i can get it to work, but if i do only one join , like:

SELECT `activities`.* FROM `activities`
join project_writeboards on item_id = project_writeboards.id and item_type = "ProjectWriteboard" AND project_writeboards.project_id = 1

View Replies !

OUTER JOIN With Extra Conditions?
I need to get a list of products in a certain category, along with the quantity of each item already added to the shopping cart for a given session number. I'm having a heck of a time satisfying the latter condition.

shoppingCart table:
+--------+-----------+-----------+-----+
| cartID | sessionID | productID | qty |
+--------+-----------+-----------+-----+
product table:
+-----------+--------------+------+--------+
| ProductID | ProductCatID | name | Active |
+-----------+--------------+------+--------+
Here's my base query, which just gets all the products in a given category.

SELECT product.*
FROM product
WHERE `ProductCatID`='{$id}'
AND `Active`=Ƈ'
Here's the query I've got so far:

SELECT product.*, shoppingCart.qty
FROM product
LEFT OUTER JOIN shoppingCart ON shoppingCart.productID = product.ProductID
WHERE `ProductCatID` = Ɗ'
AND shoppingCart.sessionID = '{$sessionID}'
AND `Active` = Ƈ'
Obviously, this does NOT work, because it limits the query to ONLY products that have the specified sessionID. I need it to return ALL the products in the category, but give me the quanity for items in the shoppingCart table, ONLY IF the sessionID matches (otherwise it should return NULL)!

View Replies !

Outter Join, Inner Join, Left Join, Right Join
I don't see the point of making so many kinds of join, that drives me confusing reading the mysql document.

View Replies !

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.

View Replies !

Join And Order
PHP

$result = mysql_query("SELECT * FROM tabProducts WHERE (proSite='radiolinks' OR proSite='allsites') AND proLive='y' AND proSalesorhire='Sales' ORDER BY proManufacturerid ASC, proOrder ASC") or die (mysql_error());

The problem is i need to order the products by the manufacturer "name" but im only storing the "id" of the manufacturer in this table.

My thought is that I can use a join to compare the manufacturer "id" in table 1 to the manufacturer "name" in table 2.

The problem is that I have no experience with joins and am not "exactly" sure what it is i need to do for it. If anyone can point me in the right direction I would be most appreciative.

p.s I cant just add a Manufacturer name to the products table as the customer has added lots of products already and has asked for this a the last minute.

View Replies !

Order By Join Column
I have a query in which I want to order the join table before joining it.

It's something like this: ....

View Replies !

Can I Join Two ORDER BY Commands?
i want to arange the results, but to have a primary OREDR by, and a secondery one.

View Replies !

ORDER In A LEFT JOIN
Table A (id)
1
2
3

Table B (id, a_id, date)
1, 1, 2008-01-01
2, 2, 2008-01-05
3, 1, 2008-01-07
4, 3, 2008-01-11
5, 1, 2008-01-19

So basically in Table B, multile row can be associate with a Table A (b.a_id = a.id).

What I want is that, for every Table A rows, output the most recent Table B row.

So I should get:

5, 1, 2008-10-19
2, 2, 2008-01-05
4, 3, 2008-01-11

But right now .... it is not working!

Here is my query:

SELECT *
FROM tableA AS a
LEFT JOIN tableB AS b ON b.a_id = a.id
GROUP BY b.a_id
ORDER BY b.date DESC

View Replies !

Join Vs. Inner Join Vs. Implied Join = Different Results ??
I SUM() only on the order table in all queries below. Here's a set of queries that I thought would/should yield the exact same results:

QUERY 1:
SELECT COUNT( o.orderID )
FROM order o
WHERE DATE( o.orderDATE ) = &#55614;&#57159;-01-04'
AND o.orderSTATUS = 300

yields 161

QUERY 2:
SELECT COUNT( o.orderID )
FROM order o
LEFT OUTER JOIN credit_card cc ON o.orderID = cc.orderID
WHERE DATE( o.orderDATE ) = &#55614;&#57159;-01-04'
AND o.orderSTATUS = 300

yields 175

QUERY 3:
SELECT COUNT( o.orderID )
FROM order o, credit_card cc
WHERE o.orderID = cc.orderID
AND DATE( o.orderDATE ) = &#55614;&#57159;-01-04'
AND o.orderSTATUS = 300

yields 157

View Replies !

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.

View Replies !

Select, Join, Order Assistance
I have 2 tables similar to

T1
-------------------------------------------
ID | initial |

T2
------------------------------------------
initial | full_name |

I want output based on the T1.ID row which has a given ID=' X '
(If ID = 7 then pretend initial = initial3, as example)

The first or last row of output to be

initial | full_name expanding the T1.initial from T2 where T1.ID=' X '
then all subsequent rows from T2

In a rough sense would need to look like

T1.initial3 | T2.full_name3 where ID=' 7 '
T2.initial2 | T2.full_name2
T2.initial1 | T2.full_name1
T2.initial4 | T2.full_name4

View Replies !

DELETE, JOIN, ORDER BY And LIMIT
Hello, quick question. I have a MySQL 4.1 host and am trying to run the following query:

DELETE u.*, ug.* FROM phpbb_users AS u INNER JOIN phpbb_user_group AS ug ON u.user_id = ug.user_id WHERE ( (u.user_id != -1) & (u.user_id != 2) ) ORDER BY u.user_regdate ASC LIMIT 1

but it doesn't work. It seems to have a problem with the "ORDER BY u.user_regdate ASC LIMIT 1" part of the function. What gets me is that this function executes fine and returns exactly the rows I want to delete when I replace DELETE with SELECT, like this:

SELECT u.*, ug.* FROM phpbb_users AS u INNER JOIN phpbb_user_group AS ug ON u.user_id = ug.user_id WHERE ( (u.user_id != -1) & (u.user_id != 2) ) ORDER BY u.user_regdate ASC LIMIT 1

Anyone know what's going on here, because I'm stumped...

View Replies !

Conditional Join - 1 Of 3 Options, Order Maters
I'm confused and I'm not sure if this is even possible to do in one query -- I really want it to be, but it may be faster to throw some PHP in there (this is where I get proved wrong )

In an ideal query, if s01_MUS_DesignerLookup.id = s01_Products.designer_id IS NULL, then I'd JOIN ON s01_MUS_DesignerLookup.code = LEFT(s01_Products.code, 4). If that returns no results, then hopefully s01_MUS_DesignerLookup.code = LEFT(s01_Products.code, 2) would. There is still the possibility that it would return no results, but I have a static variable that will handle that rare occasion.

The query I was hoping would work (but isn't even correct syntax) is:

SELECT
s01_Products.code AS Products_code
, s01_Products.designer_id AS Products_designer_id
, s01_MUS_DesignerLookup.name AS MUS_DesignerLookup_name
FROM
s01_Products
LEFT JOIN
s01_MUS_DesignerLookup
ON
CASE WHEN
ISNULL(s01_Products.designer_id) || s01_Products.designer_id = 0
THEN
CASEWHEN
ISNULL(SELECT s01_MUS_DesignerLookup.name
FROM s01_MUS_DesignerLookup
WHERE s01_MUS_DesignerLookup.code = LEFT(s01_Products.code, 4)
)
THEN
s01_MUS_DesignerLookup.code = LEFT(s01_Products.code, 2)
ELSE
s01_MUS_DesignerLookup.code = LEFT(s01_Products.code, 4)
END
ELSE
s01_MUS_DesignerLookup.id = s01_Products.designer_id
END
WHERE
LEFT(s01_Products.code,2) = 'UO'
I tried something like:

SELECT
s01_Products.code AS Products_code
, s01_Products.designer_id AS Products_designer_id
, s01_MUS_DesignerLookup.name AS MUS_DesignerLookup_name
FROM
s01_Products
LEFT JOIN
s01_MUS_DesignerLookup
ON
CASE WHEN
ISNULL(s01_Products.designer_id) || s01_Products.designer_id = 0
THEN
s01_MUS_DesignerLookup.code = LEFT(s01_Products.code, 4)
OR
s01_MUS_DesignerLookup.code = LEFT(s01_Products.code, 2)
ELSE
s01_MUS_DesignerLookup.id = s01_Products.designer_id
END
WHERE
LEFT(s01_Products.code,2) = 'UO'
ORDER BY
LENGTH(s01_MUS_DesignerLookup.code) DESC
But it was pretty dirty and I wasn't getting true results anyways.

Can what I'm after be done? Can I check the results of a subquery inside a query?

View Replies !

Left Outer Join And ORDER BY Question
This question is best understood when illustrated with an example:

SELECT dept_name AS dept,
TRIM(CONCAT(fname,' ',lname)) AS name,
title,
number,
comments
FROM emp
LEFT OUTER JOIN dept
ON dept.dept_id = emp.dept_id
WHERE 1=1
ORDER BY dept ASC,
emp_order ASC
Note that the last condition of the ORDER BY clause references a field that is not selected in this SELECT statement. This works perfectly in MySQL, but I'm wondering if this is consistent with standard SQL? And, whether it's standard SQL or not, is this considered good practice? I have no use for emp_order except for determining the order of rows returned, so it just doesn't make sense to SELECT it, but at the same time it seems odd to reference a field I'm not SELECTing.

View Replies !

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.

View Replies !

Order By With Conditions
I have an items database that has Price and SPrice fields with a status of OnSale or Online. Obviously when Online the normal Price is used and when OnSale the SPrice is used. With that in mind here is what I am having trouble doing.

I want to sort items by sprice only if the item has an SPrice and is OnSale after that sort by Price. Is this possible without doing 2 queries or doing a sort() function in PHP once I gather the data?

I am using MySQL 4.3. I have tried to upgrade to 5.1 but it seems to always corrupt my data and I don't think doing a "IF" statement works in 4.3. I tried doing an

DECLARE NewOrder VARCHAR(12);
IF sprice > 0 and (Status = "Sale" or Status = "Clearance") THEN
SET NewOrder = 'sprice,price'
ELSE SET NewOrder = 'price'
END IF

This doesn't appear to work in 4.3 or I am doing it wrong. For that matter I don't even know if that would work.

View Replies !

Cross Join + Full Outer Join ?
I desesperately need to cross join 2 rows in a table, like this table :

table values :
env type value
---------------------
env1 a 1
env1 b 2
env2 b 3
env2 c 4
env3 c 5

-> to get another table crossed by env and type like this :

table results :
a b c
----------------------
env1 1 2 null
env2 null 3 4
env3 null null 5

i tried requests using cross join and full outer join but no way, FULL OUTER JOIN is unknow by mysql, indeed i doubt this is the good solution...

View Replies !

Difference Between Join And Outter Join
What is the difference between LEFT JOIN AND LEFT OUTTER JOIN

same like right and full ,

I asked to google , google threads say , no difference ,

But few place they are saying outter keyword is mandatory.

View Replies !

Joining Data (inner Join / Self Join?)
I am relatively new to php/mysql and I am having a problem figuring out how to do a join. I have a database with a person's name and each person has an ID. I want to be able to add their relatives by typing only their ID.

For example if person 1's descendant was person 37, I want to be able to enter that in the DB and then run a query on person 1's page so that when I have 37 entered as his descendant it will query the DB for his name and print his name but not the ID.

View Replies !

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)

View Replies !

Upgrading: Replacing Commas With "INNER JOIN" In LEFT JOIN Queries
I've just upgraded from 4.1 to 5.0 and I'm very scared. So, no more comma-separated table names in queries with LEFT JOIN clauses are allowed? ( http://forums.devshed.com/mysql-help-4/having-unknown-column-in-on-clause-error-323495.html)

Each of those commas has to become ' INNER JOIN '. I have almost 400 left join queries spread out over a couple hundred files. I'm sure it's for the best, but oh is this task going to hurt! It looks like even a regular expression search & replace solution won't be feasible.

I'm sure I'm not the first person who had to do some mass replacing. Any suggestions? Am I dreaming thinking there might be a variable that can be set that will allow the "old" format? I'm sure I'm in store for other issues, but are any of them major syntax changes like this one?

View Replies !

JOIN Within LEFT JOIN
I am using MySQL 3.23.54. I have the following table structure.

FORMS
form_id (PK)
form_name

STAFF ASSIGNMENTS
staff_assignment_id (PK)
form_id (FK)
staff_id (FK)

STAFF
staff_id (PK)
first_name
last_name

For each record in FORMS there may be zero, one or multiple records in STAFF ASSIGNMENTS. I need to perform a left join from FORMS on STAFF_ASSIGNMENTS. When there is a record in STAFF ASSIGNMENTS, I need to perform a join with STAFF to retreive staff name. Here is my attempt at the query.

SELECT forms.form_id, forms.form_name, staff.first_name, staff.last_name
FROM forms LEFT JOIN staff_assignments ON forms.form_id =
staff_assignments.form_id (JOIN staff on staff_assignements.staff_id =
staff.staff_id)

How do I need to write the query?

View Replies !

Inner Join Or Left Join?
What I am trying to do is this....

English Table:
Number Text Roman
1 One I
2 Two II
3 Three III
4 Four IV

Hindi Table:
Number Text
2 Do
3 Teen
4 Char
5 Panch

Expected Results where number is 2
Text Roman
Two II
Do II

View Replies !

Self Join Plus Left Join?
select distinct t2.personid, count(*) from messagexperson as t1, messagexperson as t2 where t1.messageid = t2.messageid and t1.personid = 2877 and t2.personid <> 2877 group by t2.personid

like to get the actual names of the other people rather than the person id. Is there a way to combine another join with the self join to accomplish this? I tried inserting a left join in place of the t2 definitions, i.e.

select distinct t2.name, count(*) from messagexperson as t1, (messagexperson left join person on messagexperson.personid = person.id) as t2 where t1.messageid = t2.messageid and t1.personid = 2877 and t2.personid <> 2877 group by t2.personid

View Replies !

To JOIN Or Not To JOIN... Or Am I Missing Something...?
Right, I was always under the impression that it was 'better' to use JOINs, partly because it is 'faster'.

I'm now wondering if that is simply a myth.

Take these two SQL statements:

SELECT DISTINCT p.ProductID, p.Image, p.Price
FROMproducts AS p
RIGHT JOINcategory_links AS c_l
ONc_l.ProductID= p.ProductID
INNER JOINcategories AS c
ONc.CategoryID= c_l.CategoryID
RIGHT JOINbrands AS b
ONp.BrandID= b.BrandID
RIGHT JOINsize_links AS s_l
ONs_l.ProductID= p.ProductID
INNER JOINsizes AS s
ONs.SizeID= s_l.SizeID
RIGHT JOINcolour_links AS co_l
ONco_l.ProductID= p.ProductID
INNER JOINcolours AS co
ONco.ColourID= co_l.ColourID

SELECT DISTINCT p.ProductID, p.Image, p.Price
FROMproducts AS p,
category_links AS c_l,
categories AS c,
brands AS b,
size_links
AS s_l,
sizes AS s,
colour_links AS co_l,
colours AS co
WHEREc_l.ProductID= p.ProductID
ANDc.CategoryID= c_l.CategoryID
ANDp.BrandID= b.BrandID
ANDs_l.ProductID= p.ProductID
ANDs.SizeID= s_l.SizeID
ANDco_l.ProductID= p.ProductID
ANDco.ColourID= co_l.ColourID

The first one uses JOINs and the second simply uses WHERE. As a matter of information, both have additional WHERE details added to refine the search.

I'm using a fast PC, and there are only 14 products in the database (and not all that much data in the other tables). However, I was getting REALLY slow script execution, and I traced it to the SQL query.

Running the first one takes an average of 7 seconds. Running the second query takes less than 1 second. It's almost instantaneous in fact.

This kinda tells me NOT to use JOINs... and to stick with WHERE for this. But in that case, I am left confused as to where it is appropriate to use JOINs and where it isn't...? I did a couple of hours of Googling and didn't clear the matter up. All the articles I found pointed towards using JOINs.

Obviously at the end of the day I'm going to use the faster method. Plus, after thinking about it, there's a lot more work being done with the JOINs, is there not...?

I'd love to know what some other people think about this, and whether I'm just totally out on my JOIN usage or if other people are using them in the same situations.

::] krycek [::

View Replies !

Need Help With Join And Values In The Join
I set up a join using two tables (description and product). I have multiple products that use the same description. I also have different material types for the products. The SQL is "SELECT descripID, subCategory, ProdID, subCat, materialType FROM products, descriptions WHERE subCategory = subCat".

from the description table:
- descripID
- subCategory

from the product table:
- ProdID
- subCat
- materialType

What I want to do is get the descripID and materialType into a new table. There is going to be multiple descripIDs that match multiple materialTypes. I only want one materialType to be with a particular DescripID.

for example:

descripID: 01
materialType: 01

descripID: 01
materialType: 02

descripID: 02
materialType: 01

descripID: 03
materialType: 03

I hope this helps.

Here is some of the output I currently have:

DescripID: 0024
Desc Subcat: LINSEED OIL
Prod Subcat: LINSEED OIL
ProdID: 00024
Prod Material: 01

DescripID: 0024
Desc Subcat: LINSEED OIL
Prod Subcat: LINSEED OIL
ProdID: 00025
Prod Material: 01

DescripID: 0024
Desc Subcat: LINSEED OIL
Prod Subcat: LINSEED OIL
ProdID: 00026
Prod Material: 03

DescripID: 0024
Desc Subcat: LINSEED OIL
Prod Subcat: LINSEED OIL
ProdID: 00027
Prod Material: 02

DescripID: 0024
Desc Subcat: LINSEED OIL
Prod Subcat: LINSEED OIL
ProdID: 00028
Prod Material: 02

View Replies !

Is Left Join And Left Outer Join Are Equal?
I just want to inquire that In Mysql,

Is left join and left outer join are equal.

View Replies !

(select Where) Join OR (select Join) Join
which one is better, (select where) join OR (select join) join ?!

I can join two table with select and where, also i can do the same with join keyword. The result is same but which one is better?
I know that joining with join keyword is better for explicit code but what about performance?

View Replies !

More JOIN Fun (Or Is It?)
Yes, I've read about JOINs, albeit after coding for a couple of years
already using queries like the following:

"SELECT m.LastName, m.FirstName, o.Address FROM members m, offices o WHERE
o.City='$vCity' AND m.Status<>'Retired' AND m.Status<>'Suspended' AND
o.MemberID=m.MemberID ORDER BY LastName,FirstName"

($vCity comes from a drop-down list of city names)

Now... everything works just fine, and has for a long time.... but reading
this NG, and some online articles about JOINs has be wondering/perplexed...

What I am hoping for is someone who knows MySQL and is really bored to
perhaps explain why the above query is NOT a 'real' join (which i don't
think it is), and why that's necessarily BAD.

How can I take that query and turn it into a 'real' join, and more
importantly, why should I? What is 'wrong' with queries like the one
above?

View Replies !

JOIN With Php
problem:

SELECT *
FROM alo LEFT JOIN alo_resp ON alo.id=alo_resp.id

result is:
id desc price id desc
1 gg 8 1 dd
9
3 df 5 3 ff

but if I do the same in mysqlgui
result is:
id desc price id desc
1 gg 8 1 dd
2 ss 9 2 jj
3 df 5 3 ff

What is the problem? PHP?

View Replies !

SQL - Join How?
Is it possible to basically do a conditional within a join (or
perhaps this is where a subquery is needed)?

I have a workorder table that I'm doing a join with. Each Client may
be a different client type and thus needs to be joined from one of
two different tables. Also, each client may choose to use a set of
standard "priorities" we have for each client type or define their
own.

If I was doing a SELECT that was for just one client, this wouldn't
be a big deal because the client type and which option (standard or
custom) would be know. However, we want to be able to show all
workorders from all clients at once. Code:

View Replies !

What JOIN Do I Need?
I know what a left join, right join and regular join-keyword-less joins do. But what if I want to display null on both tables/sides?

What i'm going to do if there's no full outer join in MySQL is take a union of inner and left joins.

Is this the only way?

View Replies !

3-way Join
using one query with a 3-way table join or using 2 querys (1 of which will be a 2-way join)?Table-A & Table-B I will be using in a 2-way join; these tables will not have many rows in them; however with Table-C it is possible it can contain a LOT of rows of data, maybe up to 500,000 or so, so I was thinking if I should leave Table-C out of the 3-way join & use 2 separate querys?

View Replies !

Self-join
Anyone knows what a self-join is?
I think it is a way of getting rid of the 1-index-only limitation of mysql for queries but not sure.

View Replies !

Join Sql
$sql = "SELECT * FROM News WHERE number='$a' LEFT OUTER JOIN Member ON News.memberName = Member.memberName";
the start of this sql i use the where condition like in a regular select command. But that wont work in a join. How do i select a row like this and join it to another table?
The join works if i remove the "WHERE number='$a' " from the sql. But it selects information that i dont need.

View Replies !

INNER JOIN 3
here is what i have:

sql = "SELECT open_house.*, listings.*, images.* FROM [open_house] INNER JOIN listings INNER JOIN images ON open_house.listing = listings.ID AND listings.id = images.listing"

I get an error in my FROM Clause...

View Replies !

Join From 3.x To 4/5.x
I have an old query I used to used back when I was on ver 3 Mysql. Unfortunately it does not work any more since I updated to ver 4.1. Where am I going wrong?

Code:

Select J.job_id, J.job_start, J.job_complete, J.target,
LEFT(J.job_notes,20) AS job_notes, P.p_name, C.cat_name,
A.action_notes FROM jobs as J, priority as P, category as C,
actions as A INNER JOIN priority on J.priority_id =
P.priority_cat_id INNER JOIN category on J.cat_id = C.cat_id
INNER JOIN actions on J.job_id = A.job_id WHERE J.cust_id = '20'
AND J.target != '00-00-00 00:00:00' AND J.target != '' AND
J.job_start BETWEEN '2006-10-22' AND '2007-01-22 23:59:59'
AND open = 'No' group by J.job_id

View Replies !

First-join-ever
Just got into joins, and i'm already lost. lets say i've got a table with genres, a table with cd's and a table that links the cd to a genre. cd's have a unique id called cdID, genre's have genreID and the cds_to_genre tabel links a certain cdID to a genreID. Now, I want to select all the cd's from a certain genre, seems I need a join. Any help on this

View Replies !

When To Use Join
why do you use a join instead of doing something like:

SELECT * FROM table1,table2 WHERE table1.column = table2.column

Is it an efficiency issue? Does MySQL optimize joins better than it optimizes that "=" constraint?

View Replies !

WHERE Or JOIN
SELECT post.*, user.*
FROM forumsposts post,users user
WHERE post.id=27541 and post.deleted=0 and user.id=post.posterid
ORDER BY post.time ASC LIMIT 0,20;

Would this query be faster if I used a JOIN ? If so, can someone give me a small explanation on when it's better to use JOIN than a mixed WHERE clause?

View Replies !

Using A Self Join
I have a table that lists the taxonomy of animals, from phylum down to species. Logically, if two animals share the same Genus, all of the information "above" Genus should be the same. In many cases, I have the information for one animal, and not the other.

SELECT m1.ParasiteCorrectedBinomial, m2.ParasiteCorrectedBinomial, m1.Taxonomy, m2.Taxonomy FROM MasterParasiteTax m1 JOIN MasterParasiteTax m2 ON m1.Genus=m2.Genus WHERE m1.ParasiteType IS NULL AND m2.ParasiteType IS NOT NULL;

View Replies !

Join If ?
I've been scratching so hard, that I'm getting splinters..... try to simplify some horrific legacy code with embedded layers of the SQL queries.

3 tables
- Customers
- Salespersons
- Sales

If only one salesperson has been involved in sales to a customer, list the salespersons details beside the customer details - otherwise display just the customer details.... all customers are to be listed.

View Replies !

Using JOIN
I was under the impression that the following was going to return all records that exist between the 2 dates mentioned below showing NULL values for the "billing_commnets.comment" column when it is blank. However this is only returing records that have values in both tables that match. Any ideas on what I am doing wrong?

SELECT customer_ads.job_title, customer_ads.start_date, customer_ads.client_id, billing_commnets.comment
FROM customer_ads
JOIN billing_commnets
ON (customer_ads.id = billing_commnets.orderID)
WHERE (customer_ads.start_date between '2007-06-02' AND '2007-06-12')

View Replies !