Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

add new records to existing query, nested join?

Posted on 2007-08-02
5
Medium Priority
?
408 Views
Last Modified: 2008-01-09
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.
0
Comment
Question by:ottenm
  • 3
  • 2
5 Comments
 
LVL 15

Expert Comment

by:derekkromm
ID: 19616449
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'
0
 
LVL 15

Expert Comment

by:derekkromm
ID: 19616454
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))
0
 

Author Comment

by:ottenm
ID: 19616472
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!
0
 

Author Comment

by:ottenm
ID: 19616488
Woops, and without the ORDER BY I'm getting 200,000 rows instead of the expected 5!
0
 
LVL 15

Accepted Solution

by:
derekkromm earned 2000 total points
ID: 19616568
Are there a lot of columns in each table? You may have to do something like this:

create table #tmp(etobjects_col1, etobjects_col2, ...., vt_accounting_notes_col1, ...)
insert into #tmp(etobjects_col1, etobjects_col2, ....) SELECT * FROM et_objects WHERE (master_object_id = 'i123')
insert into #tmp(vt_accounting_notes_col1, ...) select * from vt_accounting_notes where vt_folder_id='123'

select * from #tmp order by
CONVERT(int, RIGHT(object_id, LEN(object_id) - 1))
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Suggested Courses

810 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question