August 29, 2011 at 2:33 pm
I have some tables which I am trying to perform a join. My result set data comes from the DATA_TABLE.
SYS_DEF_TABLE (org_code, check_flag...)
DATA_TABLE (key_no, org_code, status_code...)
VISIT_TABLE (key_no, visit_status...)
What I am trying to do is write a single select statement to get my results. When I join the DATA_TABLE with the SYS_DEF_TABLE using org_code, if the check_flag column is 'Y' for that org_code, then I want the data returned from the DATA_TABLE where the status_code = 'EDI'. If the check_flag is 'N' or NULL, then I want to join the DATA_TABLE to the VISIT_TABLE where the visit_status = 'CI'.
The DATA_TABLE can contain data that has org_code with and without the check_flag = 'Y'.
So, my result set should contain data from the DATA_TABLE where the org_code from the DATA_TABLE matches the SYS_DEF_TABLE and where status_code = 'EDI' if the check_flag = 'Y' and....data from the DATA_TABLE where the key_no from the DATA_TABLE matches the VISIT_TABLE and where visit_status = 'CI' if the check_flag is 'N' or NULL on the SYS_DEF_TABLE.
Here are additional details that I hope will help.
CREATE TABLE SYS_DEF_TABLE (org_code char(5) NOT NULL,check_flag char(1) NULL)
CREATE UNIQUE CLUSTERED INDEX XPKSYS_DEF_TABLE ON SYS_DEF_TABLE
(org_code)
Insert into SYS_DEF_TABLE(org_code, check_flag) Values ('123','N')
Insert into SYS_DEF_TABLE(org_code, check_flag) Values ('456','Y')
Insert into SYS_DEF_TABLE(org_code, check_flag) Values ('789',NULL)
Insert into SYS_DEF_TABLE(org_code, check_flag) Values ('111','N')
Insert into SYS_DEF_TABLE(org_code, check_flag) Values ('222','Y')
Insert into SYS_DEF_TABLE(org_code, check_flag) Values ('333','Y')
CREATE TABLE DATA_TABLE (key_no char(5) NOT NULL, org_code char(5) NULL, test_data char (5) NULL, status_code char(3) NULL)
CREATE UNIQUE CLUSTERED INDEX XPKDATA_TABLE ON DATA_TABLE
(key_no)
Insert into DATA_TABLE (key_no, org_code, test_data, status_code)
Values ('AAA','123', 'TEST1', 'EDI')
Insert into DATA_TABLE (key_no, org_code, test_data, status_code)
Values ('BBB','123', 'TEST2', 'EDI')
Insert into DATA_TABLE (key_no, org_code, test_data, status_code)
Values ('CCC','456', 'TEST3', 'EDI')
Insert into DATA_TABLE (key_no, org_code, test_data, status_code)
Values ('DDD','789', 'TEST4', 'EDI')
Insert into DATA_TABLE (key_no, org_code, test_data, status_code)
Values ('EEE','111', 'TEST5', 'EDI')
Insert into DATA_TABLE (key_no, org_code, test_data, status_code)
Values ('FFF','222', 'TEST6', 'EDI')
Insert into DATA_TABLE (key_no, org_code, test_data, status_code)
Values ('GGG','333', 'TEST7', 'EEE')
------
CREATE TABLE VISIT_TABLE (key_no char(5) NOT NULL, visit_status char(3) NULL)
CREATE UNIQUE CLUSTERED INDEX XPKVISIT_TABLE ON VISIT_TABLE
(key_no)
Insert into VISIT_TABLE (key_no, visit_status)
Values ('AAA','CA')
Insert into VISIT_TABLE (key_no, visit_status)
Values ('BBB','CI')
Insert into VISIT_TABLE (key_no, visit_status)
Values ('CCC','CI')
Insert into VISIT_TABLE (key_no, visit_status)
Values ('DDD','CI')
Insert into VISIT_TABLE (key_no, visit_status)
Values ('EEE','CB')
Insert into VISIT_TABLE (key_no, visit_status)
Values ('FFF','CB')
Insert into VISIT_TABLE (key_no, visit_status)
Values ('GGG','CI')
select * from DATA_TABLE d, VISIT_TABLE v, SYS_DEF_TABLE s
WHERE d.key_no = v.key_no
and d.org_code = s.org_code
AAA 123 TEST1 EDI AAA CA 123 N------Should not return
BBB 123 TEST2 EDI BBB CI 123 N------Should return
CCC 456 TEST3 EDI CCC CI 456 Y------Should return
DDD 789 TEST4 EDI DDD CI 789 NULL---Should return
EEE 111 TEST5 EDI EEE CB 111 N------Should not return
FFF 222 TEST6 EDI FFF CB 222 Y------Should return
GGG 333 TEST7 EEE GGG CI 333 Y------Should not return
Desired Result
BBB 123 TEST2 EDI BBB CI 123 N
CCC 456 TEST3 EDI CCC CI 456 Y
DDD 789 TEST4 EDI DDD CI 789 NULL
FFF 222 TEST6 EDI FFF CB 222 Y
August 29, 2011 at 2:40 pm
Hey Rog, you seem semi-new around here, but when asking for code assistance it helps if we can test it before we hand it to you. There's too many things that you never know if you needed to account for.
If you'll check out the link in my signature here, first one at the bottom, it'll show you what we're looking for to help you. Schema, test data, expectations, etc.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
August 29, 2011 at 2:44 pm
OK, thank you. I will produce data , etc. and return when I have the information.
August 30, 2011 at 10:48 am
I think you need a query like this:
SELECT
D.key_no,
D.org_code,
D.test_data,
D.status_code,
DT.org_code,
DT.check_flag,
VT.key_no,
VT.visit_status
FROM
data_table AS D JOIN
sys_def_table DT ON D.org_code = DT.org_code JOIN
dbo.VISIT_TABLE AS VT ON D.key_no = VT.key_no
WHERE
DT.check_flag = 'Y' AND
D.status_code = 'EDI'
UNION
SELECT
DT.key_no,
DT.org_code,
DT.test_data,
DT.status_code,
SDT.org_code,
SDT.check_flag,
VT.key_no,
VT.visit_status
FROM
dbo.DATA_TABLE AS DT JOIN
dbo.SYS_DEF_TABLE AS SDT ON DT.org_code = SDT.org_code JOIN
dbo.VISIT_TABLE AS VT ON DT.key_no = VT.key_no
WHERE
(SDT.check_flag = 'N' OR SDT.check_flag IS NULL) AND
VT.visit_status = 'CI'
OR this:
SELECT
D.key_no,
D.org_code,
D.test_data,
D.status_code,
DT.org_code,
DT.check_flag,
VT.key_no,
VT.visit_status
FROM
data_table AS D JOIN
sys_def_table DT ON D.org_code = DT.org_code JOIN
dbo.VISIT_TABLE AS VT ON D.key_no = VT.key_no
WHERE
CASE WHEN DT.check_flag = 'Y' AND
D.status_code = 'EDI' THEN 1
WHEN (DT.check_flag = 'N' OR DT.check_flag IS NULL) AND
VT.visit_status = 'CI' THEN 1
ELSE 0 END = 1
BOth return the desired results from your test data. There may be another way to do it using some case statements in the where clause as well. You might need to tweak the joins because they might leave something out as INNER JOINS. The one with the CASE in the WHERE clause produces a better Execution Plan based on the tiny amount of data being tested on.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
August 30, 2011 at 2:44 pm
Jack,
Thank you very much for atking the time to help me out - I will try it out.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply