Hey there!..Don!
The Problemo returns! :(
Let me be explicit this time with my query :
#1
mysql > desc table A;
+-------------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------------+---------+------+-----+---------+-------+
| mId | int(11) | | PRI | 0 | |
| tId | int(11) | | PRI | 0 | |
+-------------+---------+------+-----+---------+-------+
2 rows in set (0.44 sec)
mysql > desc table B;
+-----------------+--------------+------+-----+------------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------------+--------------+------+-----+------------+-------+
| tId | int(11) | | PRI | 0 | |
| tName | varchar(100) | | | | |
+-----------------+--------------+------+-----+------------+-------+
2 rows in set (0.08 sec)
mysql> select * from table A;
+-------------+--------+
| mId | tId |
+-------------+--------+
| 1 | 1 |
| 2 | 1 |
| 2 | 6 |
+-------------+--------+
3 rows in set (0.00 sec)
#2 what I need to do is :
mysql> select * from tableA where mId=1;
[ this gives me the result set of all rows that have mId=1]
+-------------+--------+
| mId | tId |
+-------------+--------+
| 1 | 1 |
+-------------+--------+
1 row in set (0.00 sec)
>> Now with this result set [ tId=1]
I want to eliminate the resulting Ids frm table A and display the rest of the rows in
table B.
>> The join stmt (left and right) eliminate the rows frm each table and display it...
For that I wud have to run another query to get the other rows which are there in
table A , which have a valid tId and which are related to other mIds as well..
current solution -- In brief >>
#1 I am getting the result set of all tIds, mIds frm table A which have mIds=1;
>> capturing the resultant Ids as (1,2...etc) , storing that as a string (tString =
>"1,2" and running the following:
#2 query : " select * from Table B where tId not in (tString);
All I want is the same result , but using joins, Iff it is possible!
I certainly hope u get my problem this time!!:(..
Thanks for your time
Rgds,
Ash
----- Original Message -----
From: Don Read <[EMAIL PROTECTED]>
To: Asha Ramaiah <[EMAIL PROTECTED]>
Cc: <[EMAIL PROTECTED]>
Sent: Wednesday, July 04, 2001 12:59 PM
Subject: RE: Clean way of execution of query....
> <java-digest elide>
>
> On 04-Jul-01 Asha Ramaiah wrote:
> > Hey! guys!
> >
> > I wanted a little help on the following : ..
> >
> > Scenario:
> > I have 2 tables : one task and the other tasksequence
> >
> > desc task : (primary key taskId, varchar taskdesc)
> > desc tasksequence (primary key (taskId,milestoneId))
> >
> > Now the query I want to form should give me a list of all taskIds in table
> > task
> > excluding the taskIds frm tasksequence..( a result set of >15 rows(taskIds))
> >
> > Can somebody help me with this ??...I have developed a quick fix for this :
> >
> > -- capture the resultset of all the taskIds in tasksequence , as a string
> >
> > --and then : "select taskId from task where taskId not in (taskIdsString)"
> >
> > Its working , but is the same not possible with left/right or natural
>
> Start reading the manual, pay attention at:
>
> "If there is no matching record for the right table in the ON or USING part in
> a LEFT JOIN, a row with all columns set to NULL is used for the right table.
> You can
> use this fact to find records in a table that have no counterpart in
> another table:
>
> mysql> select table1.* from table1
> LEFT JOIN table2 ON table1.id=table2.id
> where table2.id is NULL;
>
> "
>
> stop reading.
>
> --
> Don Read [EMAIL PROTECTED]
> -- It's always darkest before the dawn. So if you are going to
> steal the neighbor's newspaper, that's the time to do it.
>
> ---------------------------------------------------------------------
> Before posting, please check:
> http://www.mysql.com/manual.php (the manual)
> http://lists.mysql.com/ (the list archive)
>
> To request this thread, e-mail <[EMAIL PROTECTED]>
> To unsubscribe, e-mail
><[EMAIL PROTECTED]>
> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
>
>
