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 wrote:
> Hey there,
>
> - Original Message -
Hey there,
- Original Message -
> From: "Rocio Gomez Escribano"
> 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 close already. Might I sugg
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/my
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: http://t
I would go with join rather than where condition.
2011/4/26 Halász Sándor
> 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 o
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
`Blogs`.`UserId`
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, pl
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 wil
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"
'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 wrote:
> Hello everyone,
> Sorry for my beginner question. Actually I have been using MySql for a
> long  time  but  I  just  start  using some
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 OU
>-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
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` th
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
>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
You need to group by forum_id...
On 2/19/09 11:09 AM, "Artem Kuchin" 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( forum_msg.id )
bleA.Name=tableB.Name;
> +---+--++
> | Name | Age | Salary |
> +---+--++
> | Amit | 27 | 5 |
> | Sumit | 31 | 6 |
> | Anand | 29 | 55000 |
> | Parry | 32 | NULL |
> +---+--++
>
> Regards,
> Ami
ECTED]
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 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.
>
>regar
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
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.
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 or
>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.
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 rer
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 com
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
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 |
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, oversk
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 Didrikse
he-infoshop.com
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 sho
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 frien
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?
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 r
> 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
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 wor
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,
a
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, tv.A
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)
>L
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
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 b
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 (td.
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
(http://dev.mysql.com/doc/refman/5.1/en/joi
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.Ds
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
SEL
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
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
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
> >
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
tab
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
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 t
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:
S
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 PRO
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
>
[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
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
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 is
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
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 u.userID
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 a
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
[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
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
used
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
[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
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
>
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 ne
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 qu
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
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
"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
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 fo
- 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 (t
[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..
S
004 11:17 PM
To: [EMAIL PROTECTED]
Cc: [EMAIL 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.
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: l
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,
Original Message-
From: Lachlan Mulcahy [mailto:[EMAIL 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
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 j
NULL1
158 NULL2
159 NULL3
160 1 4
userTBL
nameid
tom 1
this is a sample of what the db/tbl looks like for
auburn/universityTBL.ID=40
-Original Message-
From: Lachlan Mul
x27;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 issues!!!
Bruce,
What do you ge
PM
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 universityTB
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 iss
assigned 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 th
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.
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 th
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 ps
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 JOI
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 hea
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 i
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
-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 =
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 l
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 t
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 "%02-0
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 S
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 whe
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 t
"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 kn
1 - 100 of 193 matches
Mail list logo