asked on

add new records to existing query, nested join?

Given the integer 123, I have some asp.net code that generates the following query:

SELECT * FROM et_objects
WHERE (master_object_id = 'i123')
ORDER BY CONVERT(int, RIGHT(object_id, LEN(object_id) - 1))

I now need the query to return exactly what it currently returns *plus* any rows from the table 'vt_accounting_notes' where vt_accounting_notes.vt_folder_id == the original integer (123). The tables have almost no similarities, and whatever makes for the simplest query is fine as a result (I'm guessing NULL's in the et_objects fields for rows from vt_accounting_notes, and NULL's in the vt_accounting_notes fields for rows from et_objects). It does not matter where the vt_accounting_notes end up sorted in the final list, as long as the ORDER BY for et_objects is preserved.

Thanks for any help.

derekkromm

SELECT * FROM et_objects
WHERE (master_object_id = 'i123')
ORDER BY CONVERT(int, RIGHT(object_id, LEN(object_id) - 1))

UNION ALL

SELECT * FROM vt_accounting_notes where vt_folder_id='123'

derekkromm

actually, this is probably more correct:

SELECT * FROM et_objects, vt_accounting_notes
WHERE (master_object_id = 'i123') OR (vt_folder_id='123')
ORDER BY CONVERT(int, RIGHT(object_id, LEN(object_id) - 1))

ottenm

ASKER

Trying the second one, I get:
Syntax error converting the varchar value 'EPSI' to a column of data type int.

I do not see that value in any of the fields in the original results, or in any field in the vt_accounting_notes.

Any thoughts? And thanks for the awesome response time. Was headed out for a coffee hoping someone would help while I was gone. Never got out of the seat!

ottenm

ASKER

Woops, and without the ORDER BY I'm getting 200,000 rows instead of the expected 5!

ASKER CERTIFIED SOLUTION

derekkromm

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial