July 23, 2003 at 1:12 pm
Is there a way to do an inner join on 2 tables where table a has data but table 2 does not.
Example:
Select field1, field2 from table1 INNER JOIN table 2 on field1 = someid
As of right now, if I ran something similar to that it woudl return no records since there is nothing in table b. Or do I need to enter in a blank row with the id?
Matt
July 23, 2003 at 1:29 pm
Instead of using an inner join:
Select field1, field2 from table1 INNER JOIN table 2 on field1 = someid
Try using an outer join:
Select field1, field2 from table1 LEFT JOIN table 2 on field1 = someid
This will give you every record in table1 that matches your WHERE criteria and every corresponding match record in table2 where it meets the criteria for a match in the left join.
July 23, 2003 at 3:34 pm
Yep. Mind you, if you do not include a join condition (ON ...) that binds table 1 and table 2, the data from table 1 will be duplicated for each row that exists in table 2!
July 24, 2003 at 2:21 am
...if you explicitly use the JOIN keyword, you cannot leave out the ON clause.
/Hans
July 24, 2003 at 2:27 am
Is there any way to use the update in this context.
Like...existing rowid should be updated.
non-existing rowid should be inserted.
update b
Set b.column1=a.column1,b.column2=a.column2,b.column3=a.column3
from table2 as b
right join table1 as a
on (a.rowid = b.rowid)
when I run this it is updating only one record. Whereas it should insert the non-existing record and update the existing record. How do do this ?
Thanks in advance.
Ganesh
July 24, 2003 at 7:34 am
Thanks that worked but I do need to do more testing.
As for your question ganesh you need to break your inserts and updates. You can not have it run as one as far as my knowledge is concerned. Eitehr usiung flags if its an insert or update or if its truely dynamic you could do an If Exists(select statement) to determine that.
Matt
July 24, 2003 at 10:00 am
Is there a way to do an inner join on 2 tables where table a has data but table 2 does not.
Matt,
What is the outcome you are trying to achieve? If you do an inner join and get back the no rows result for a select then you are getting the logically correct result. More likely what you want is either to create a list of records in table A which don't have matching records in table B, in which case you can use an outer join as follows:
Select A.*
From TableA A Left Outer Join TableB B
ON A.RecordID = B.RecordID
Where B.RecordID IS NULL.
If what you are trying to do is check to be sure a record doesn't already exist in table B before inserting one you could use Where Exists and a subquery against TableB as follows:
Insert Into TableB
Select RecordID, DataField
From TableA A
Where Exists (Select * From TableB B
Where B.RecordID = A.RecordID)
If you are trying to do something else I hadn't thought of yet, could you clarify what you are trying to do. Hope this was helpful.
Bob
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply