Hey there,
- Original Message -
From: Rocio Gomez Escribano r.go...@ingenia-soluciones.com
Hi!! Is it possible to create a left join consult with 2 tables??
I mean:
SELECT * FROM table1 LEFT JOIN (table2, table3) on table1.ID =
table2.subID and table1.ID= table3.subID
Pretty
Hi Johan,
I think you probably want something like this. Give the following a shot.
SELECT *
FROM table1
LEFT JOIN table2
ON table1.ID = table2.subID
LEFT JOIN table3
ON table1.ID= table3.subID
On Thu, Apr 28, 2011 at 9:41 AM, Johan De Meersman vegiv...@tuxera.be wrote:
Hey there,
-
2011/04/25 17:42 +0300, Andre Polykanine
Here is the first one.
We have two queries:
SELECT `blogs`.* FROM `Blogs` LEFT JOIN `Users` ON
`Blogs`.`UserId`=`Users`.`Id`;
and the following one:
SELECT `Blogs`.* FROM `Blogs`, `Users` WHERE
I would go with join rather than where condition.
2011/4/26 Halász Sándor h...@tbbs.net
2011/04/25 17:42 +0300, Andre Polykanine
Here is the first one.
We have two queries:
SELECT `blogs`.* FROM `Blogs` LEFT JOIN `Users` ON
`Blogs`.`UserId`=`Users`.`Id`;
and the following
Hello Halбsz,
Aha. So, I should write
SELECT `Blogs`.* INNER JOIN `Users` ON `Users`.`Id`=`Blogs`.`UserId`
instead of my original WHERE clause?
Thanks!
--
With best regards from Ukraine,
Andre
Skype: Francophile
My blog: http://oire.org/menelion (mostly in Russian)
Twitter:
2011/04/26 17:55 +0300, Andre Polykanine
Aha. So, I should write
SELECT `Blogs`.* INNER JOIN `Users` ON `Users`.`Id`=`Blogs`.`UserId`
instead of my original WHERE clause?
Thanks!
I think so.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To
'where' is a filter. You're limiting records based on a criterion.
'on' is used for joining.
On Mon, Apr 25, 2011 at 10:42 AM, Andre Polykanine an...@oire.org wrote:
Hello everyone,
Sorry for my beginner question. Actually I have been using MySql for a
long time but I just start using
I can be wrong about that, but I think the difference between them should be
irrelevant so it makes me think about a paranoiac thought.
For me, the only difference is: Chose the one you feel better to understand
your code.
Am I wrong or not?
--
João Cândido de Souza Neto
Andre Polykanine
The only difference once MySQL parses these two queries is the first one is
a LEFT JOIN, which will produce all records from the blogs table even if
there is no matching record in the users table. The second query produces an
INNER JOIN which means only rows with matching records in both tables
Hi Andre, everybody!
Andre Polykanine wrote:
Hello everyone,
Sorry for my beginner question. Actually I have been using MySql for a
long time but I just start using some advanced things (earlier I
accomplished those tasks with PHP), so I will be asking stupid
questions, please
A LEFT OUTER JOIN in that query in not necessary. An inner join should be used.
-Original Message-
From: Shiv [mailto:shiv...@gmail.com]
Sent: Saturday, May 30, 2009 10:18 PM
To: bharani kumar
Cc: mysql
Subject: Re: Left join query
Hi,
Along with tables, you should also provide
Hi,
Along with tables, you should also provide details on how they are
related. Assuming Code is unique in both tables and left joined on
Airport table, you can do something like this
SELECT A.Code, C.Code, A.SlNo, C.SlNo, A.Name, C.Location, A.status,
C.status, C.type
FROM Airport A
LEFT
-Original Message-
From: shawn.gr...@sun.com [mailto:shawn.gr...@sun.com]
Sent: Saturday, April 11, 2009 3:50 PM
To: Jerry Schwartz
Cc: mysql@lists.mysql.com
Subject: Re: LEFT JOIN with third-table key
Jerry Schwartz wrote:
This is a question that I run into frequently; I might even
Jerry Schwartz wrote:
This is a question that I run into frequently; I might even have posted it
before.
If I have three tables:
A: pub_product_id
B: product_id, publisher_id, pub_product_id
C: publisher_id, publisher_code
D: product_id, product_price
and I want to find those `pub_products`
You need to group by forum_id...
On 2/19/09 11:09 AM, Artem Kuchin mat...@itlegion.ru wrote:
I have two simple tables. One - list of forums, second - list of
messages and i want to
get the list of forums with number of messages in each.
Here is the query:
SELECT forums.id , COUNT(
SELECT forums.id , COUNT( forum_msg.id ) AS cnt
FROM forums
LEFT JOIN forum_msg ON forums.id = forum_msg.forum_id
ORDER BY forums.sorder ASC
Missing GROUP BY.
PB
-
Artem Kuchin wrote:
I have two simple tables. One - list of forums, second - list of
messages and i want to
get the list of
Following the documentation available at
http://dev.mysql.com/doc/refman/5.0/en/join.html
t1t2
a b a c
--- ---
1 x 2 z
2 y 3 w
Then a natural left join would product these results
mysql SELECT * FROM t1 NATURAL LEFT JOIN t2;
+--+--+--+
| a| b
At 09:05 AM 7/22/2008, you wrote:
Hi All,
Can u please let me know when should i use LEFT JOIN and when should i going
for a RIGHT JOIN. Please let me know some examples.
regards
anandkl
anandkl,
Take a look at the tutorial at
http://www.keithjbrown.co.uk/vworks/mysql/mysql_p5.php
Mike
, July 22, 2008 7:59 PM
To: mysql@lists.mysql.com
Subject: Re: LEFT JOIN and RIGHT JOIN
At 09:05 AM 7/22/2008, you wrote:
Hi All,
Can u please let me know when should i use LEFT JOIN and when should i
going
for a RIGHT JOIN. Please let me know some examples.
regards
anandkl
anandkl,
Take a look
| 32 | NULL |
+---+--++
Regards,
Amit Sharma @ Affle
-Original Message-
From: mos [mailto:[EMAIL PROTECTED]
Sent: Tuesday, July 22, 2008 7:59 PM
To: mysql@lists.mysql.com
Subject: Re: LEFT JOIN and RIGHT JOIN
At 09:05 AM 7/22/2008, you wrote:
Hi All,
Can u
Hi Jerry,
I think the problem is that NULL is not less than or greater than your
prod_published date. So you probably have eo_pub_date set to NULL in 56
of your rows.
so for
eo_name_table.eo_pub_date prod.prod_published
or
eo_name_table.eo_pub_date = prod.prod_published
mysql will
From: Bill Newton [mailto:[EMAIL PROTECTED]
Sent: Monday, April 14, 2008 2:09 PM
To: Jerry Schwartz
Cc: 'Mysql'
Subject: Re: LEFT JOIN problem
Hi Jerry,
I think the problem is that NULL is not less than or greater than your
prod_published date. So you probably have eo_pub_date set to NULL in 56
I've found yet another oddity with this situation. If I leave the date test
off of both JOINs they give the same number of rows, but they give me the
wrong number! Neither one of them gives me 860 rows returned. I must not
understand how a LEFT JOIN works.
By the way, the EXPLAIN for both of my
As usual, the computer is right and I am wrong. The only reason that one
query was coming out right is that it just happened the WHERE clause was
never failing. It was just luck that my data was just so.
Regards,
Jerry Schwartz
The Infoshop by Global Information Incorporated
195 Farmington Ave.
Hi,
On Jan 28, 2008 5:31 AM, [EMAIL PROTECTED] wrote:
We have just moved to a new machine where we are running MySQL 5.0.45. On
the old machine we were running MySQL 4.0.15a.
The following query worked in 4.0.15a but gives an error mesage in 5.0.45.
We're pretty sure the problem is with the
Malki,
which gives the error:
ERROR 1054 (42S22): Unknown column 'depts.dept_code' in 'on clause'
See the JOINs page of the manual. As of 5.0.12, MySQL improved ISO SQL
compliance in query parsing; ambiguities due to comma join syntax became
errors. It's highly recommended to switch from
Yes, that is the correct behavior of a LEFT JOIN. A left join keeps
all the record from the original/left table and will link up any
related data from the right table, but if there is no related data,
it sets it to NULL. If you want the join to act as a filter, the just
use regular JOIN.
Andrew Carlson wrote:
Is there a purchase order (10002) with no orders in the order_details table?
No, PO 10002 is related to the order_id = 2 here:
mysql SELECT order_id, SUM(quantity * unit_price) AS subtotal
- FROM order_details GROUP BY order_id;
+--+--+
| order_id |
Which table is bladref in?
Regards,
Jerry Schwartz
The Infoshop by Global Information Incorporated
195 Farmington Ave.
Farmington, CT 06032
860.674.8796 / FAX: 860.674.8341
www.the-infoshop.com
www.giiexpress.com
www.etudes-marche.com
-Original Message-
From: Kaj Schermer Didriksen
Hi,
two tables gi_t and gi
gi_t holds 5 records. gi holds ca. 1500.
I need all from gi for each in gi_t ie LEFT JOIN!!!
but this:
SELECT gi.id AS id, overskrift,gidata FROM gi_t LEFT JOIN gi ON
gi_t.id=gi.gitref WHERE bladref=137
gives the same as
SELECT gi.id AS id,
I think your problem is that you can't have a missing friends record that
also has a non-null value for friends.userA. If friends.userA = 79760, then
you've found a record.
You can have records where userA is something valid and UserB is null, but
then you can't join on UserB.
Does that help?
It works if I do AND instead of WHERE
Go figure
LEFT JOIN friends ON gameLeaderboards.userID = friends.userB
AND friends.userA = 79760
--
Dave
Jerry Schwartz wrote:
I think your problem is that you can't have a missing friends record that
also has a non-null value for friends.userA. If
www.giiexpress.com
www.etudes-marche.com
-Original Message-
From: Critters [mailto:[EMAIL PROTECTED]
Sent: Wednesday, August 22, 2007 3:30 PM
To: Jerry Schwartz
Cc: 'MySQL General'
Subject: Re: Left join is not doing what I thought it should do.
It works if I do AND instead of WHERE
hi
i am experimenting with sql for getting lists of friends.
select friend.*
from user
left join link on user.id=link.id and user.id = $MYID
right join user friend on link.friend_id = friend.id
where user.name is null;
on my local windows machine running mysql 5 it works
some more info on this:
putting user AS friend improves the query, but does not eliminate the problem.
also, here are the version numbers for each sql server:
mysql Ver 12.22 Distrib 4.0.22, for portbld-freebsd4.9 (i386)
mysql.exe Ver 14.12 Distrib 5.0.24, for Win32 (ia32)
the query even
Hi,
select * from t1,t2
left join t3 on t3.itemid=t1.itemid
left join t4 on t4.f2=t1.f2
mysql4.1 works, but mysql5.0 shows Unknown column 't1.itemid' in 'on clause'
If change into
select * from t2,t1
left join t3 on t3.itemid=t1.itemid
left join t4 on t4.f2=t1.f2
mysql4.1
This is makes more sense because it causes a SQL developer
to group the joined tables in a logical manner.
- Original Message -
From: wang shuming [EMAIL PROTECTED]
To: mysql@lists.mysql.com
Sent: Friday, November 3, 2006 8:04:20 PM GMT-0500 US/Eastern
Subject: left join , Unknown column
the thing with JOINs are you gotta JOIN a table ON another table where
something matches something else (in most cases). I tried to clean this up a
bit but im rather new to mysql.
SELECT DISTINCT
(td.td_id),
td.venue_id as ven_id,
td.td_date as td_date,
art.NAME as art_name,
art.WEB as art_url,
Paul Nowosielski wrote:
Dear All,
I've been hashing out this query for awhile with no luck as of yet.
Basically the query works if I put a limit of 500 or so but when I do the full
query it takes up so many resource that the database engine is useless.
Here is the query:
SELECT DISTINCT
Here is your query rephrased a bit. I find this query structure easier to debug, especially when their are lots of joins. This is
also the preferred structure in mysql 5 as I recall.
Notice the ON ? part of the join. You didn't specify anything join condition so your doing a full join, very very
I ammend my previous post.
Paul Nowosielski wrote:
Dear All,
I've been hashing out this query for awhile with no luck as of yet.
Basically the query works if I put a limit of 500 or so but when I do the full
query it takes up so many resource that the database engine is useless.
Here is the
Paul,
SELECT ...
FROM
tourdates td,
tbl_ARTST as art,
artist_tourdate artd ,
tbl_VENUES tv,
tbl_VENUE_CAPACITY tvc ,
tbl_VENUE_AGE_XREF tvax,
tbl_VENUE_AGES tvage
LEFT JOIN tbl_VENUE_CAPACITY ON (tv.ID=tvc.VENUE_ID)
LEFT JOIN tbl_VENUE_AGE_XREF ON (tv.ID=tvax.VENUE_ID)
LEFT JOIN
Thank you all so much for your help, here is my solution:
(I'm sure I can do a little more optimization)
SELECT DISTINCT (td.td_id) ,td.venue_id as ven_id, td.td_date as td_date,
art.NAME as art_name,art.WEB as art_url, artd.artist_id as art_id, tv.ID,
tv.NAME as ven_name, tv.ADDR1 ven_add0,
Scott
SELECT *
FROM UserInfo u, DslInfo d
LEFT JOIN DslExtra e ON d.DslID = e.DslID
LEFT JOIN ExtraAddr a ON a.UserID = u.UserID
WHERE u.UserID = d.UserID;
However it appears this syntax is not valid in MySQL 5.x
Right, as the 5.x docs say, 5.x wants ISO-compatible explicit joins, ie
SELECT *
Perfect. This is exactly what I needed. Is there some place I get
some more documentation on the specifics of the ISO-compatible
queries? Might save me some hair-pulling-out in the future.
Scott
Peter Brawley wrote:
Scott
SELECT *
FROM UserInfo u, DslInfo d
LEFT JOIN DslExtra e ON d.DslID =
Scott,
Perfect. This is exactly what I needed. Is there some place I get
some more documentation on the specifics of the ISO-compatible
queries? Might save me some hair-pulling-out in the future.
See 'Changes in 5.0.12' on the Joins manual page
Hi,
2006/1/12, Vincente Aggrippino [EMAIL PROTECTED]:
On 1/12/06, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote:
That fixed it... Thank you! But I don't understand how. Isn't my implicit
inner join the same as the explicit one you used?
I read Join Syntax in the ref. manual. Is it related
Vincente Aggrippino [EMAIL PROTECTED] wrote on 01/12/2006 12:47:31
AM:
On 1/12/06, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote:
Vincente Aggrippino [EMAIL PROTECTED] wrote on 01/11/2006
11:33:38
PM:
I have three tables joined on key fields: delivery is joined with
Vincente Aggrippino [EMAIL PROTECTED] wrote on 01/11/2006 11:33:38
PM:
I have three tables joined on key fields: delivery is joined with
invoice_detail on delivery_id and with location on loc_id. I want to
return
records of deliveries that have corresponding records in the location
table,
On 1/12/06, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote:
Vincente Aggrippino [EMAIL PROTECTED] wrote on 01/11/2006 11:33:38
PM:
I have three tables joined on key fields: delivery is joined with
invoice_detail on delivery_id and with location on loc_id. I want to
return
records of
On 24/11/2005 2:22 p.m., Terence wrote:
SELECT um.username,rm.role_name,dm.department_name
FROM user_master um, role_master rm
LEFT JOIN department_master dm ON um.department_id = dm.department_id
WHERE um.role_id = rm.role_id;
1054 - Unknown column 'um.department_id' in 'on clause'
Query:
Terence,
SELECT um.username,rm.role_name,dm.department_name
FROM user_master um, role_master rm
LEFT JOIN department_master dm ON um.department_id = dm.department_id
WHERE um.role_id = rm.role_id;
J
Your query joins rm to dm on a um column = a dm column. Recent 5.0 releases
rightly object to
Terence [EMAIL PROTECTED] wrote on 11/23/2005 08:22:30 PM:
Hi All,
We have upgraded from 4.1.0 to 5.0.16 and notice all left joins not
working. It works fine on 4.1 but 5.0.16 gives us an error:
How to reproduce:
CREATE TABLE `user_master` (
`user_id` int(5) unsigned NOT NULL
2005/11/5, Guy Brom [EMAIL PROTECTED]:
I have the following tables:
languages
language_id int,
language_title varchar(80)
objects
object_id int
object_language_id int
object_title varchar(100)
I want to select ALL available languages, and match the translated object
(if it is
Have a look at the reply I just wrote for Gobi, entitled Re: Help with an
SQL query. You'll find an explanation there for how to use temporary tables
and joins instead of subqueries in MySQL versions that don't support
subqueries.
Rhino
- Original Message -
From: Guy Brom [EMAIL
Angelo Zanetti [EMAIL PROTECTED] wrote on 06/08/2005 12:06:51 PM:
Hi guys.
I'm having a problem deciding whether a left join is suitable for what i
want to do.
I have two tables
A Users
-userID
-isactive
B BuddyList
-userID
-buddyID
what i want to do is to get all the users from A
Angelo Zanetti [EMAIL PROTECTED] wrote on 08/06/2005 17:06:51:
Hi guys.
I'm having a problem deciding whether a left join is suitable for what i
want to do.
I have two tables
A Users
-userID
-isactive
B BuddyList
-userID
-buddyID
what i want to do is to get all the users from
thanks shawn it seems to be working but i forgot to add that i need it
for a single user ID, in other words it must bring back all user ids in
the user table if they do not exist for that user in the buddylist.
so what i've tried is this:
SELECT u.* FROM users u LEFT JOIN buddylist bl ON
Angelo Zanetti wrote:
thanks shawn it seems to be working but i forgot to add that i need it
for a single user ID, in other words it must bring back all user ids in
the user table if they do not exist for that user in the buddylist.
so what i've tried is this:
SELECT u.* FROM users u LEFT JOIN
Let me rewrite what it's meant to do as i mgiht not have been clear.
ok for a single user I want to get all the users (from the user table)
that aren't a buddy for that user.
users
1 bob
2 tom
3 mike
buddylist
1 2
1 3
2 1
2 3
3 1
//therefore if i searched for mike it would return tom as he
Angelo Zanetti [EMAIL PROTECTED] wrote on 06/08/2005 01:38:42 PM:
Let me rewrite what it's meant to do as i mgiht not have been clear.
ok for a single user I want to get all the users (from the user table)
that aren't a buddy for that user.
users
1 bob
2 tom
3 mike
buddylist
1 2
1 3
[EMAIL PROTECTED] wrote:
Michael's last answer:
SELECT u.UserID
FROM Users u
LEFT JOIN BuddyList bl
ON u.userID = bl.buddyID
AND bl.userID = '$userid'
WHERE u.isactive =1
AND bl.userID is null;
Should do all of what you want except exclude the original user (so that
[EMAIL PROTECTED] writes:
You have a sort because you did an order by.
If you had an index with the desired order by, it may be used.
Try as you usage of covering indexes.
Hi Mathias,
Along with your suggestion, a little more thinking about the problem
and some experimenting seems to have
Johan Höök [EMAIL PROTECTED] writes:
Hi Scott,
I think you've been lucky so far...
As you're only ordering on listdate, which is the same
for both homes in your example you might definitely get different
results once you put in the limit.
A basic thing about rdb's is that you must never
hi,
mls_num is not in a key, have you tried index creation on (zip,price
desc,mls_num) ?
mathias
Selon Scott Gifford [EMAIL PROTECTED]:
Johan Höök [EMAIL PROTECTED] writes:
Hi Scott,
I think you've been lucky so far...
As you're only ordering on listdate, which is the same
for both
[EMAIL PROTECTED] writes:
hi,
mls_num is not in a key, have you tried index creation on (zip,price
desc,mls_num) ?
Hi mathias,
mls_num is the primary key, so it does have its own index.
I could create a multi-column index covering (zip,price,mls_num), but
that was really just one example of
you can also try to increase the value of the tmp_table_size variable.
A+
Selon [EMAIL PROTECTED]:
You have a sort because you did an order by.
If you had an index with the desired order by, it may be used.
Try as you usage of covering indexes.
you certainly know that one multi-column
You have a sort because you did an order by.
If you had an index with the desired order by, it may be used.
Try as you usage of covering indexes.
you certainly know that one multi-column index is similar to a lot of
multi-column others when desired columns are in the right position of columns
Hi Scott,
I think you've been lucky so far...
As you're only ordering on listdate, which is the same
for both homes in your example you might definitely get different
results once you put in the limit.
A basic thing about rdb's is that you must never make assumptions
that it returns resultsets in
In article [EMAIL PROTECTED],
Scott Gifford [EMAIL PROTECTED] writes:
The problem we're seeing is that when additional tables are pulled in
for the detailed view, the order is different from the summary view,
so the wrong homes are displayed. Here's a simplified example. A
summary query
Joshua Beall wrote:
Hi All,
I have two tables in a children's program registration system, parent and
child. 1 row in the parent table corresponds to 1 or more rows in the child
table.
Right now, when I do
SELECT * FROM parent LEFT JOIN child ON parent.ID = child.parentID
I get multiple rows
Wolfram Kraus [EMAIL PROTECTED] wrote in message
news:[EMAIL PROTECTED]
Use GROUP BY with GROUP_CONCAT:
http://dev.mysql.com/doc/mysql/en/group-by-functions.html
Thanks, I'll take a look and see if I can figure that out. I wasn't aware
of the GROUP_CONCAT function; it looks like that might
Joshua Beall wrote:
Wolfram Kraus [EMAIL PROTECTED] wrote in message
news:[EMAIL PROTECTED]
Use GROUP BY with GROUP_CONCAT:
http://dev.mysql.com/doc/mysql/en/group-by-functions.html
Thanks, I'll take a look and see if I can figure that out. I wasn't
aware of the GROUP_CONCAT function; it
[snip]
Is it possible to do two left joins involving three
tables in one query?
select a.id, a.amount FROM t1
LEFT JOIN t2 ON (t1.id=t2.id)
then
LEFT JOIN t3 ON (t1.id=t3.id)
Is this even possible?
[/snip]
Yes, and the keys from table to table don't have to be the same, save
for each JOIN..
- Original Message -
From: Richard Reina [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Sent: Thursday, October 14, 2004 8:26 AM
Subject: left join question
Is it possible to do two left joins involving three
tables in one query?
select a.id, a.amount FROM t1
LEFT JOIN t2 ON
Bruce,
I have reconstructed the database you have given me and used the following
query successfully (the one I originally gave you). I think there is
something wrong with your data.
SELECT
p2.statusID as parseStatus,
p2.action as parseAction,
u2.name,
p1.userID,
Bruce,
I'm getting the four rows with one correctly matching the userID for tom and
the others returning NULLs for the user info.
Lachlan
-Original Message-
From: bruce [mailto:[EMAIL PROTECTED]
Sent: Tuesday, 10 August 2004 5:13 PM
To: 'Lachlan Mulcahy'
Subject: RE: left join issues
PROTECTED]
Subject: RE: left join issues!!!
Bruce,
I have reconstructed the database you have given me and used the following
query successfully (the one I originally gave you). I think there is
something wrong with your data.
SELECT
p2.statusID as parseStatus,
p2.action
followup to my initial question.
i can use the following select query to get close... however, it doesn't
really use 'left joins', and i can't figure out how to arive at the results
with the user names being correctly identified.
mysql select
- p1.name as pname,
- p2.statusID as
Hi Bruce,
I haven't reconstructed your database to test for sure, but I think your
problem is stemming from the way your joins are working.
First of all, since you are left joining to the university_urlTBL, you will
also need to left join to any tables you join from that table. If you don't
do
Message-
From: Lachlan Mulcahy [mailto:[EMAIL PROTECTED]
Sent: Monday, August 09, 2004 5:21 PM
To: [EMAIL PROTECTED]
Subject: RE: left join issues!!!
Hi Bruce,
I haven't reconstructed your database to test for sure, but I think your
problem is stemming from the way your joins are working.
First
to it...
-Original Message-
From: Lachlan Mulcahy [mailto:[EMAIL PROTECTED]
Sent: Monday, August 09, 2004 5:21 PM
To: [EMAIL PROTECTED]
Subject: RE: left join issues!!!
Hi Bruce,
I haven't reconstructed your database to test for sure, but I think your
problem is stemming from the way your joins
Bruce,
What do you get when you do this:
SELECT
*
FROM
university_urlTBL
WHERE
universityID = 40;
Lachlan
-Original Message-
From: bruce [mailto:[EMAIL PROTECTED]
Sent: Tuesday, 10 August 2004 10:46 AM
To: 'Lachlan Mulcahy'
Subject: RE: left join issues!!!
i
To: [EMAIL PROTECTED]
Subject: RE: left join issues!!!
Bruce,
A couple of questions...
Do you always expect one or more entries in the university_urlTBL for a
universityID that is in the universityTBL? If so, then you don't need to
left join the university_urlTBL from the universityTBL.
If you
(fileID, testdate)
)type =bdb;
here's the rough structure of the db/tables...
right now i have ~2000 records in the universityTBL...
-Original Message-
From: Lachlan Mulcahy [mailto:[EMAIL PROTECTED]
Sent: Monday, August 09, 2004 6:02 PM
To: [EMAIL PROTECTED]
Subject: RE: left join
-Original Message-
From: Lachlan Mulcahy [mailto:[EMAIL PROTECTED]
Sent: Monday, August 09, 2004 6:02 PM
To: [EMAIL PROTECTED]
Subject: RE: left join issues!!!
Bruce,
What do you get when you do this:
SELECT
*
FROM
university_urlTBL
WHERE
universityID = 40
and fields and slowly add them
until you have the lot..
This is a good technique to find where your joins are going wrong.
Regards,
Lachlan
-Original Message-
From: bruce [mailto:[EMAIL PROTECTED]
Sent: Tuesday, 10 August 2004 12:27 PM
To: 'Lachlan Mulcahy'
Subject: RE: left join issues
PROTECTED]
Sent: Monday, August 09, 2004 8:04 PM
To: [EMAIL PROTECTED]
Cc: [EMAIL PROTECTED]
Subject: RE: left join issues!!!
Bruce,
Try building your join table by table.. start with
SELECT
fields
FROM
universityTBL LEFT JOIN university_urlTBL
Have you tried using the IN operator?
SELECT denewbs, dewrbtr, dekostl, desgtxt, deprojk,
degeber, de_heid, acdesc, acfund
FROM (details INNER JOIN headers ON details.de_heid = headers.heid)
LEFT JOIN acctref ON acctref.acfund IN
(details.dekostl,details.deprojk,details.degeber)
WHERE
Shawn, thank you - that did the trick.
-Mike
At 10:18 AM 6/2/2004, [EMAIL PROTECTED] wrote:
Have you tried using the IN operator?
SELECT denewbs, dewrbtr, dekostl, desgtxt, deprojk,
degeber, de_heid, acdesc, acfund
FROM (details INNER JOIN headers ON details.de_heid = headers.heid)
LEFT
Not sure if this is what you are asking, but how about:
SELECT *
FROM t1 LEFT JOIN t2 ON t1.c1 = t2.c2
Big Brother tells me that David Perron wrote:
Is there a way to do this? Im trying to LEFT JOIN to a table that needs to
select based on 2 keys, but the query Im writing only has the
It's tough to answer your question without seeing your actual table
structure. You can use AND in a join:
SELECT * FROM a1 LEFT JOIN b1 ON a1.f1=b1.f1 AND a1.f2=b1.f2
In you example query, MySQL will try to do the LEFT JOIN first (I'm
pretty sure), which is not what you what. But if you change
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
El Vie 23 Ene 2004 17:08, Chris Boget escribi:
Can you UNION 2 queries and use the result to JOIN with another table?
(SELECT tableA.name, tableC.company FROM tableA)
UNION
(SELECT tableB.name FROM tableB)
LEFT JOIN tableC ON tableA.name =
Chris Boget [EMAIL PROTECTED] wrote:
Can you UNION 2 queries and use the result to JOIN with another table?
(SELECT tableA.name, tableC.company FROM tableA)
UNION
(SELECT tableB.name FROM tableB)
LEFT JOIN tableC ON tableA.name = tableC.name;
This doesn't work. But I don't know if it
Jacque Scott wrote:
I have a fairly simple query where I feel it takes too long to run.
SELECT Products.NSIPartNumber,Products.Cost, Products.ModelNo,
Products.USPrice, Products.VendorPart, Products.Description ,
Products.ProductID, Sum(tblInvTransaction.Qty) AS SumOfQty FROM Products
LEFT JOIN
In Access 2.0 and mySQL Products.ProductID is indexed but
tblInvTransaction.ProductID is not. I tried to set
tblInvTransaction.ProductID as indexed but it still took a long time.
Daniel Kasak [EMAIL PROTECTED] 1/26/2004 12:18:46 PM
Jacque Scott wrote:
I have a fairly simple query where I
At 02:18 PM 1/26/2004, you wrote:
Jacque Scott wrote:
I have a fairly simple query where I feel it takes too long to run.
SELECT Products.NSIPartNumber,Products.Cost, Products.ModelNo,
Products.USPrice, Products.VendorPart, Products.Description ,
Products.ProductID, Sum(tblInvTransaction.Qty) AS
How did you determine the bottleneck was the left join? I would have
thought it was the WHERE clause.
I'd be curious what the times are on these queries:
SELECT count(*) FROM Products
LEFT JOIN tblInvTransaction ON Products.ProductID =
tblInvTransaction.ProductID Where ((NSIPartNumber Like
The first query that you wrote took 5.93 seconds with count of 523. The
second query took so long I aborted it. The way I figured it was the
'Left Join' was by running the following query.
#1: This takes out the join and sets 'Products.ProductID =
tblInvTransaction.ProductID' to join the two
Mike,
I used Explain and I don't see anything that says indexes. I use the
Control Center for all of my stuff. Would it show in there?
88 rows are returned both in mySQL and Access.
Brent Baisley [EMAIL PROTECTED] 1/26/2004 2:03:10 PM
How did you determine the bottleneck was the left
1 - 100 of 188 matches
Mail list logo
