Anyone willing to help me out?

[DaShen]

I am having issues on SQL Server.

I need to do an insert select into another database.

The select is very specific

there are 3 tables (2 have the same primary key with one being the main table containing the information <so it may exist in one table but not the other>) No problem I just LEFT OUTER JOIN and that takes care of that issue.

the other table has a many relationship to the main table. there is guaranteed to be one in this table for every one in the main table, but there can be more. I need to find the first instance of that table based on the first one and do a selection. I am having difficulties trying to think of a way to do this. Any help would be much appreciated.
Here is the code. I have changed the names and titles to preculde any secure information

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

ALTER PROCEDURE [dbo].[sp_InsertSelect]
AS
BEGIN
SET NOCOUNT ON;

INSERT tblForInsert (mainID, infoT1, infoT2, infoT3)
SELECT t1.mainID, t1.info, t2.info, t3.info
FROM
tableMain t1
INNER JOIN tableMany t2 -- this is causing duplications because the tableMany is just that, many. (I cannot GROUP BY because of the other join, I don't think EXISTS would help, but I am not sure)
ON t1.mainID = t2.mainID
LEFT OUTER JOIN addressINFO t3
ON t1.mainID = t3.mainID
WHERE
t1.pseudoField LIKE 'V%' OR t1.pseudoField LIKE 'H%' OR t1.pseudoField LIKE 'J[0-9]'
ORDER BY t1.pseudoField
END

any ideas?

**UPDATE**

I actually got it. Someone from another place suggested a subquery in the join (to replace the tblMany) that was grouped by the mainID.


It would not work to set up a distinct value and treat the query as distinct. that would not pull the correct information.

the new query is actually quite simple. I don't know why I didn't think about that. Must be because I was a little annoyed at the time.

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

ALTER PROCEDURE [dbo].[sp_InsertSelect]
AS
BEGIN
SET NOCOUNT ON;

INSERT tblForInsert (mainID, infoT1, infoT2, infoT3)
SELECT t1.mainID, t1.info, t2.infoMin, t3.info --or infoMax depending on what you want
FROM
tableMain t1
INNER JOIN (select min(info) As infoMin, max(info) As infoMax, mainID FROM tableMany t2 GROUP BY mainID)
ON t1.mainID = t2.mainID
LEFT OUTER JOIN addressINFO t3
ON t1.mainID = t3.mainID
WHERE
t1.pseudoField LIKE 'V%' OR t1.pseudoField LIKE 'H%' OR t1.pseudoField LIKE 'J[0-9]'
ORDER BY t1.pseudoField
END

Of course this isn't an elegant solution, because it only select the min or maximum value of the info, so if there are any better solutions, feel free to post them.

But for now, I am going to try this.

 

[DaShen]

I know the INNER JOIN won't work, and that I need to write it differently, but if anyone knows a good solution...?

I can't seem to figure out how to get the first instance of table2 without GROUP BY in SQL Server. I don't think TOP will work.

 

[DaShen]

I want DISTINCT values based only on the mainID, but it must contain the values from t1 and t3 and only one of the values from t3

say

the tables have these values (where * means primary key)
t1
mainID(*) | info
1 | test
2 | blah
3 | whatever

t2
mainID(*) | secondaryID(*) | info2
1 | 1 | c1
1 | 2 | a1
2 | 3 | b4
3 | 4 | d3
3 | 5 | a1
3 | 6 | c1

t3
mainID(*) | info3
1 | 90210
2 | 78705
3 | 41111

well I would want a list that contained

mainID(*) | info | info2 | info3
1 | test | c1 | 90210
2 | blah | b4 | 78705
3 | whatever | d3 | 41111

which I would then insert into another table with the mianID as the primary key. Don't ask me why I need to do this. My boss is demanding it.

I am seriously thinking if the raise is worth the aggravation. She increased it by two steps, but even still, I don't know if it is worth it anymore.

 

[HBalzer]

I'm to lazy to really think about your problem but if yours works and just creates duplicates just run another script to remove the duplicates

 

[ISAslot]

Can you use subqueries rather than joins? and just specify a distint on the select subquery for the many table?

 

[crystal]

Really, the way you set it up. t1 and t3 pretty much the same table because of mainID.

Since t2 require mainID and SecondaryID to be unique - you can't expect uniqueness just using mainID for your join.

i.e.

1 | 1 | c1
1 | 2 | a1

which of this you expected to appear in the new table?

- reread what your example, you only want the the first info of t2 inserted.

...
SELECT
t1.mainID,
t1.info,
(select t2.info from t2
where t2.mainId = t1.mainId
and rownum = 1
order by secondaryId)
t3.info
FROM t1 & t3 ...

- you must limited the subquery result to return only 1 row or it will crashes.

 

[Chryso]

OR statements are a real bastard in SQL. Avoid them if at all possible.

Personally, I would declare a #temptable and pull your original data into it.
Then I would use a cursor to go through it and pull the data for each of your
OR statements separately and stick all that data in a second #temptable2.
Then do the insert into your table by selecting from #temptable2

 

[DaShen]

I actually got it. Someone from another place suggested a subquery in the join (to replace the tblMany) that was grouped by the mainID.


It would not work to set up a distinct value and treat the query as distinct. that would not pull the correct information.

the new query is actually quite simple. I don't know why I didn't think about that. Must be because I was a little annoyed at the time.

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

ALTER PROCEDURE [dbo].[sp_InsertSelect]
AS
BEGIN
SET NOCOUNT ON;

INSERT tblForInsert (mainID, infoT1, infoT2, infoT3)
SELECT t1.mainID, t1.info, t2.infoMin, t3.info --or infoMax depending on what you want
FROM
tableMain t1
INNER JOIN (select min(info) As infoMin, max(info) As infoMax, mainID FROM tableMany t2 GROUP BY mainID)
ON t1.mainID = t2.mainID
LEFT OUTER JOIN addressINFO t3
ON t1.mainID = t3.mainID
WHERE
t1.pseudoField LIKE 'V%' OR t1.pseudoField LIKE 'H%' OR t1.pseudoField LIKE 'J[0-9]'
ORDER BY t1.pseudoField
END

Of course this isn't an elegant solution, because it only select the min or maximum value of the info, so if there are any better solutions, feel free to post them.

But for now, I am going to try this.

 

[DaShen]

Originally posted by: crystal
Really, the way you set it up. t1 and t3 pretty much the same table because of mainID.

Since t2 require mainID and SecondaryID to be unique - you can't expect uniqueness just using mainID for your join.

i.e.

1 | 1 | c1
1 | 2 | a1

which of this you expected to appear in the new table?

- reread what your example, you only want the the first info of t2 inserted.

...
SELECT
t1.mainID,
t1.info,
(select t2.info from t2
where t2.mainId = t1.mainId
and rownum = 1
order by secondaryId)
t3.info
FROM t1 & t3 ...

- you must limited the subquery result to return only 1 row or it will crashes.

I am dealing with a large dataset so that probably would be a good idea to use something like top and have subsequent subqueries for every instance of mainID.

It is better to group by or something of that nature. more efficient.

 

[DaShen]

Originally posted by: HBalzer
I'm to lazy to really think about your problem but if yours works and just creates duplicates just run another script to remove the duplicates

Not if your new database sets the mainID as the primary key.

Also, that would involve cursors to actually be done well, and that is just one more step when it should be easily done in one query.

 

[Chryso]

Originally posted by: DaShen

Originally posted by: HBalzer
I'm to lazy to really think about your problem but if yours works and just creates duplicates just run another script to remove the duplicates

Not if your new database sets the mainID as the primary key.

Also, that would involve cursors to actually be done well, and that is just one more step when it should be easily done in one query.

It may be easy to write in one query but that doesn't mean it will run as quickly or efficiently in one query.