Need Help with SQL Syntax - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Need Help with SQL Syntax

Author

Topic

Sanch
Starting Member

2 Posts

Posted - 2005-11-16 : 11:27:52

Hi,

I have three tables. Table A,B and an exception table.
Table A has the following columns: suite_id, ex_id, cad_id
Table B has the following columns: suite_code_id, ex_code_id, cad_code_id

I need to archieve the following tasks:

- I need to FIRST check if the suite_id in table A matches suite_code_id in table B.
- If it does, I need to check if the ex_id and cad_id of Table A matches suite_code_id and ex_code_id of Table B for a perticular suite_id.
- If it does not match, I need to write an exception to the exception table.

Well, I am not sure how to do this. I have the following so far:
I am just starting to work with sql, so I am not good at all. But this is what I have.

insert into exception
(
reason,
fields)
select 'the ex_id and cad_id does not match',
ex_id + '/' + cad_id
from TableA a
where a.suite_id = dbo.TableB.suite_code_id
and
a.ex_id != dbo.TableB.ex_code_id
and
a.cad_id != dbo.TableB.cad_code_id

I don't even know the last two works.

But I am not sure if the overall works. Can you help me with this please. Thank you.

Sanch

chloraphil
Starting Member

7 Posts

Posted - 2005-11-17 : 16:01:26

Hi Sanch,

It seems to me that you're approaching your list of tasks 'procedurally' and you need to think of it more in SQL terms - as a whole.

I would rephrase your tasks like this:
Find all records in EITHER table A or table B that do not have corresponding records in the other table, and insert those into Table C.
(If you have a scenario where the records will always be in Table A but not always in B, or vice versa, this is easier. But I'll assume it's either-or.)

Basically what you need here is a FULL JOIN, like this:

SELECT a.*, b.*
FROM TableA a FULL JOIN TableB b
ON a.suite_id = b.suite_code_id
AND a.ex_id = b.ex_code_id
AND a.cad_id = b.cad_code_id

This will show you all records in all tables, joined on the appropriate fields. It shouldn't be too hard to add conditions in the WHERE clause to only pull out ones that have nulls in all fields of the columns from A or B. Run this on your data and see what you get.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-11-18 : 01:41:38

Post some sample data and the result you want

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources