MYSQL :: Join Vs. Inner Join Vs. Implied Join = Different Results ??

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 !

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 !

Straight_join, Join Order & Join Conditions
I have a query with 4 tables and plain 'JOIN's the explain gives the best join order, and it completes in 1.5 secs

I add a single ORDER BY (a calculated column) and the join orders all shift and the query takes 85secs!

So I read the docs and it suggests STRAIGHT_JOIN to force join order. now I was using:

JOIN myTable ON xyx=abc

but in the docs it seems the ON condition is not permisible here, though it does work. Am I infact doing an 'INNER JOIN'? certainly if I change to INNER JOIN there is no difference.

However the only way I can force the join order is to use STRAIGHT_JOIN that does not accept an ON condition, so I have shifted the clauses to the WHERE and it works fine.

Is there any syntax I can use to keep the ON conditions, I prefer this approach it makes the code clearer regarding intent. Code:

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 !

How To Join Results ?
Is it possible to join results of the select query ?

So If I get data from two rows (firstname, secondname)Can I somehow join query results like this: firstname.secondname

Example:

QUERY: SELECT firstname,secondname FROM table;

RESULTS:
John.Doe
Mike.Monroe
Kate.Moss

View Replies !

Limit Inner Join Results ?
Is it possible to limit the results of an inner join or sub select ?
For example, let say that I have a table of countries and a talbe of cities:

View Replies !

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

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

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

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

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

here's the SQL Code:

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 !

Join 3 Tables - Distinct Results
I've got a database that keeps track of sales of widgets. Each company that belongs to my organiztion is to report their widget sales or no sales every month.

There are several different types of widgets. Not all companies sell or report all types of widgets.

We want to report how many companies have reported or not reported their sales (ie. x companies of a possible y companies have reported sales this month - y will always be the same - lets say 5).

Because of the way that sales are input, "big widgets" are reported in 2 different tables called widgets_a and widgets_b. If they don't have any sales to report, they still report and it goes into a table called no_reports. Each table has a couple of common fields - ManufacturerID
and OrderDate.

I can search all of the tables individually to find if a manufacturer has reported -

SELECT DISTINCT ManufacturerID FROM widgets_a WHERE OrderDate >='2003-06-01' AND OrderDate <= '2003-06-30';

but I want to search through the 3 tables and find how many distinct manufacturers have reported in the given month.

View Replies !

NOT IN And Left Join Should Give The Same Results In The Example, But They Don't . Why?
Just against me getting insane: I want to list all categories for which there exist no items.

I thought the following would give the exact same result, but it doens't. The join gives results while the NOT IN gives 0 results. How come?

select cats.id from cats left join items on cats.id = items.catid where items.id is null

select cats.id from cats where cats.id not in (select catid from items)

btw: what query is the most performant if any? or do you need more info for that?

View Replies !

How To Join Two COUNT Results From Two Tables
I have an email campagin system while sending each mails i am recording it with current timestamp

sample data ....

View Replies !

Inner Join Of Two Tables, No Results And Errors
I am trying to do a INNER JOIN of 2 tables. but I am not getting any results or any errors.

I hope you can help as I have been trying this for a few hours now without any success.

Here is the SQL:

SELECT table1.*, table2.*
FROM table1
INNER JOIN table2
ON table1.teamneame = table2.teamname

Obviouslt both tables have data and the teamname will match up. Like I have said it is not returning any results. If I do a LEFT , RIGHT join I only get data from that side.

View Replies !

Selecting Distinct Results In A Join
SELECT p.post_title
, p.post_name
, c.cat_name
, DATE_FORMAT(p.post_date, '%M %D, %Y') AS dateadded
FROM wp_posts AS p
LEFT JOIN wp_post2cat AS pc ON p.ID = pc.post_id
LEFT JOIN wp_categories AS c ON pc.category_id = c.cat_ID
WHERE MATCH(p.post_content, p.post_title)
AGAINST(TRIM('merchant') IN BOOLEAN MODE)
ORDER BY p.post_date DESC
I am doing a fulltext search on a Wordpress database. Some posts are in multiple categories. When using the above query I will get a result for every category a post is in. I don't want that. I have tried using distinct with no effect. The wp_categories has a row for each category a post is in and how I am joining it seems to be the culprit.

View Replies !

Join Duplicated Results Issue
I have two tables, one is a catalogue of products and another one with media connected to the catalogue items. the media can be either 'main image' or an 'alternative product view'. I need to retrieve 4 products that are marked as live (c.status = Ƈ') and their 4 corresponding main images (m.category = 'main') with no repeats on the products.

I have the following query:

SELECT DISTINCT c.id, c.category_id, c.title, c.description, m.filename, m.description, m.copyright
FROM catalogue c
LEFT JOIN media m
ON m.parent_id = c.id
WHERE c.status = Ƈ'
AND m.category = 'main'
LIMIT 4
the problem I have is that some products have more than one 'main image' so this query is returning a duplicate product because it has two main images, even if the product only exists once on the catalogue..

View Replies !

Join Results From Select Multiple Statment
how I can join results from multiple queries in one result without geting a prodict of the tables

ex:
table1_Col1, table1_Col2, table1_Col3...table2_Col1, table2_Col2, table2_Col3...table3_Col1, table3_Col2, table3_Col3...
table1_Col1, table1_Col2, table1_Col3...table2_Col1, table2_Col2, table2_Col3...table3_Col1, table3_Col2, table3_Col3...
, , ...table2_Col1, table2_Col2, table2_Col3...table3_Col1, table3_Col2, table3_Col3...
, , ... , , ...table3_Col1, table3_Col2, table3_Col3...
, , ... , , ...table3_Col1, table3_Col2, table3_Col3...

using the 4.0 sql so no subqueries supported.

View Replies !

Join 3 Tables, Limit Results From 3rd Table
Yet again I have a query about joins and even though I keep reading I cannot find a solution for my problem that is relevant or that I understand.

I have 3 tables (I have shortened the number of fields to those that are relevant):

category:

Quote:

catid

product:

Quote:

prodid
catid
prodname

prodimgs:

Quote:

imgid
prodid
imgname

I can select all products per category and I can select all images per product, but where I am having the problem is selecting all products for one category and at the same time selecting all the images per product and limiting the number of images displayed to one without limiting the products to one.

I have tried various joins and tested a lot, getting different results but none of them are returning what I need.

Select all products for a category:

PHP

SELECT prodid, prodname FROM product WHERE catid='$this->catid'

Works fine.

Select all images per product per category and limit results of image to 1:

PHP

SELECT product.prodid, product.catid, product.prodname, prodimgs.imgname FROM product, prodimgs WHERE product.catid ='$this->catid' AND prodimgs.prodid = product.prodid LIMIT 1

This returns only one product and one image per category despite there being more.

Using the same code as above but removing the limit I get a display of all images per product with the product name repeated over and over.

I know I need to use joins somehow but I have tried various ways and tested the sql but am always getting an error on the joins and I don't know how or where to put the limit so that it is only applied to the prodimgs table.

So what I want is:

1. User click on a category

2. On the next page all product names linked to the category are displayed, but once only.

3. Next to each product name, one image related to that product is to be displayed if an image is stored in the prodimgs table

View Replies !

Unique Results On Join/Union Query
I have 2 tables:
Table 1: groupid, groupname etc,
Table 2: groupid, firstname, lastname etc;

I have these data in table 1:
1, groupname1
2, groupname2

In Table 2 I have:
1, Fname1, lname1,
1, fname2, lname2
1, fname3, lname3
2, Fname1, lname1,
2, fname2, lname2

For each loop, i'm looking for these results:
ROW1 Groupname1 + all matching fname,lname using its groupid
ROW2 Groupname2 + all matching fname,lname using its groupid

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 !

Doing INNER JOIN
Is it possible to do INNER JOIN with more than 2 tables? I've tried this statement but MySQL 3x gives me a 'syntax error' message:

SELECT * FROM Blah1 AS t1 INNER JOIN Blah2 AS t2 INNER JOIN Blah3 AS t3
ON ...

View Replies !

Join
I have a table orders from which I have been able to create a view that has a list of ordernum. Now I want to count or determine within the orders table how many instances there are for each ordernum in the view. Maybe I could have gotten that with the view creation, but don't know how to formulate that query.

View Replies !

Inner Join Or More?
I'm starting creating for me more interessting queries but I stuck at the following part, i have a table like this:

t_messages (table):
m_id
m_fk_u_id_s*
m_fk_u_id_r*
m_title
m_text
m_datum
m_read

* those are to foreign keys to the t_user (table) where of course is:

t_user (table):
u_id
u_username
...
...

Now, I want do a Query which shous me:

m_title, m_text, m_fk_u_id_s but of course not the id, the username, same for the other fk key: MsgTitel, MsgText, UserSender, UserReceipt

View Replies !