Deletion from LEFT JOINed tables - very basic question 
Author Message
 Deletion from LEFT JOINed tables - very basic question

I have two tables (products and groups) joined with one to many
relationship.
I want to display all products with their groups and be able to delete
selected Product  without however deleting Group.

This is SQL query I use to create recordset:
SELECT * FROM Products LEFT JOIN Groups ON Products.GroupID=Groups.GroupId

Deleting a record from such a recordset causes deletion of a Group
associated with particular Product.
I do not have to explain that this is not really what I want.  I also hate
creating two separate recordsets and displaying group for selected product
in TextBox or DataCombo.

How to prevent this?

I am using VB 6.0 and ADO. My database is Access97 file (I hope it does not
really make a difference..).

Thanks in advance,

Tomasz Jastrzebski



Fri, 11 May 2001 03:00:00 GMT  
 Deletion from LEFT JOINed tables - very basic question
You can use a separate SQL statement to do the deletion. For example,
    rsProduct.execute "delete from products where groupid = " list1.text
Quote:

> I have two tables (products and groups) joined with one to many
> relationship.
> I want to display all products with their groups and be able to delete
> selected Product  without however deleting Group.

> This is SQL query I use to create recordset:
> SELECT * FROM Products LEFT JOIN Groups ON Products.GroupID=Groups.GroupId

> Deleting a record from such a recordset causes deletion of a Group
> associated with particular Product.
> I do not have to explain that this is not really what I want.  I also hate
> creating two separate recordsets and displaying group for selected product
> in TextBox or DataCombo.

> How to prevent this?

> I am using VB 6.0 and ADO. My database is Access97 file (I hope it does not
> really make a difference..).

> Thanks in advance,

> Tomasz Jastrzebski




Fri, 11 May 2001 03:00:00 GMT  
 Deletion from LEFT JOINed tables - very basic question
On a basic level, why do you have a relationship between these tables?
Assuming the Groups table describes the group associated with the
GroupID and is the base table (the one side), and the Products table
describes the products and is foreign (the Many side)  you don't
really need to create a relationship since you can create the
necessary links in subsequent SQL statements. Drawing a one-to-many
relationship will cascade deletions (as you have found).

Delete the relationship and redraw it in SQL statements as needed.
That will prevent deletion of group records when products are
eliminated.

On Mon, 23 Nov 1998 10:11:00 GMT, "Tomasz Jastrzbski"

Quote:

>Deleting a record from such a recordset causes deletion of a Group
>associated with particular Product.
>I do not have to explain that this is not really what I want.  I also hate
>creating two separate recordsets and displaying group for selected product
>in TextBox or DataCombo.



Sat, 12 May 2001 03:00:00 GMT  
 
 [ 3 post ] 

 Relevant Pages 

1. Deletion from LEFT JOINed tables - very basic question

2. Deletion from LEFT JOINed tables - very basic question

3. Deletion from LEFT JOINed tables - very basic question

4. How to Left join more than two table ?

5. LEFT JOIN WITH AN EXTERNAL TABLE

6. Need info from table that is linked through another left outer join

7. SQL statement on 2 or more tables (left outer joins)

8. LEFT JOIN not selecting all records in originating table

9. AdoDC with LEFT JOIN deletes from both tables

10. left join + another table

11. PRB: Left join behaves like a inner join.

12. Four Table JOIN Question

 

 
Powered by phpBB® Forum Software