SQL question
#1
Scooby Regular
Thread Starter
Join Date: Jul 2000
Location: Islington
Posts: 2,145
Likes: 0
Received 0 Likes
on
0 Posts
Microsoft Access 97
Table A has all the information
Table B has some of the rows from table A
they both have the same columns
how do I get table A's rows minus table B's rows into table C?
mars bar for the most efficient solution (no cartesian selects please )
I've tried...
select x.1, x.2, x.3
from (select a.1, a.2, a.3 from tablea a
union
select b.1, b.2, b.3 from tableb b) x
group by x.1, x.2, x.3
having count(*)=1
...but it's not having it.
Table A has all the information
Table B has some of the rows from table A
they both have the same columns
how do I get table A's rows minus table B's rows into table C?
mars bar for the most efficient solution (no cartesian selects please )
I've tried...
select x.1, x.2, x.3
from (select a.1, a.2, a.3 from tablea a
union
select b.1, b.2, b.3 from tableb b) x
group by x.1, x.2, x.3
having count(*)=1
...but it's not having it.
#2
Okay...
Table A has
Row with Keys : 1,2,3,4,5,6,7,8,9,10
Table B has
Rows with Keys : 1,2,3,4,5
So Table C will have 6,7,8,9,10?
This works for me:
SELECT DISTINCTROW tblOne.IndexID, tblOne.KeyID, tblOne.FieldB, tblOne.FieldC INTO tblThree
FROM tblOne LEFT JOIN tblTwo ON tblOne.KeyID = tblTwo.KeyID
WHERE (((tblTwo.KeyID) Is Null));
I think
Table A has
Row with Keys : 1,2,3,4,5,6,7,8,9,10
Table B has
Rows with Keys : 1,2,3,4,5
So Table C will have 6,7,8,9,10?
This works for me:
SELECT DISTINCTROW tblOne.IndexID, tblOne.KeyID, tblOne.FieldB, tblOne.FieldC INTO tblThree
FROM tblOne LEFT JOIN tblTwo ON tblOne.KeyID = tblTwo.KeyID
WHERE (((tblTwo.KeyID) Is Null));
I think
#6
Scooby Regular
Thread Starter
Join Date: Jul 2000
Location: Islington
Posts: 2,145
Likes: 0
Received 0 Likes
on
0 Posts
Were it only that simple chaps
table A column data | table B column data
a,b,c,d,e | a,b,c,d,e
a,f,c,d,e | a,f,c,d,e
a,b,c,d,f | a,b,c,d,e
a,b,c,d,e | a,b,c,d,g
x,b,c,d,t |
j,b,c,d,e | a,b,c,d,e
a,b,c,d,e | a,y,c,d,e
x,b,c,d,e | a,b,c,d,e
x,b,c,d,h |
none of them are keyed fields, and you can see row 1 and 2 are the only matching rows columns 1 to 5
I want rows 1 and 2 in table C and the remaining unmatched rows in tables D and E (from A and B, respectively).
table A column data | table B column data
a,b,c,d,e | a,b,c,d,e
a,f,c,d,e | a,f,c,d,e
a,b,c,d,f | a,b,c,d,e
a,b,c,d,e | a,b,c,d,g
x,b,c,d,t |
j,b,c,d,e | a,b,c,d,e
a,b,c,d,e | a,y,c,d,e
x,b,c,d,e | a,b,c,d,e
x,b,c,d,h |
none of them are keyed fields, and you can see row 1 and 2 are the only matching rows columns 1 to 5
I want rows 1 and 2 in table C and the remaining unmatched rows in tables D and E (from A and B, respectively).
Thread
Thread Starter
Forum
Replies
Last Post
Brzoza
Engine Management and ECU Remapping
1
02 October 2015 05:26 PM