June 16, 2006 at 9:15 am
I want to combine the results of these 2 tables into 1 SQL statement. But I only want those records from COMMENT_TABLE where display_order is NOT NULL. How can I do this?
Table 1 SQL
SELECT MAIN_TABLE.key_no
FROM MAIN_TABLE
where MAIN_TABLE.key_no = 'XYZ'
Table 2 SQL
SELECT COMMENT_TABLE.comment
FROM COMMENT_TABLE
where key_no= 'XYZ'
and display_order is NOT NULL
Will the following work?
SELECT MAIN_TABLE.key_no,
COMMENT_TABLE.comment
FROM MAIN_TABLE, COMMENT_TABLE
where MAIN_TABLE.key_no = 'XYZ'
AND MAIN_TABLE.key_no *= COMMENT_TABLE.key_no
and display_order is NOT NULL -- not sure how to do this part
June 16, 2006 at 9:22 am
Hello Rog,
You need to specify the alias name for the display_order column also. As it is from the "Comment_Table" it should be
Comment_Table.display_order is not null
Thanks and have a nice day!!!
Lucky
June 16, 2006 at 9:24 am
Do the following:
SELECT MAIN_TABLE.key_no,
COMMENT_TABLE.comment
FROM MAIN_TABLE
LEFT OUTER JOIN COMMENT_TABLE
ON MAIN_TABLE.key_no = COMMENT_TABLE.key_no and COMMENT_TABLE.display_order is NOT NULL
where MAIN_TABLE.key_no = 'XYZ'
Note, the old style (*=, =* and *=*) outer join syntax has been deprecated and will not be supported in the future and therefore should not be used
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply