turning 2 bits of SQL into 1 - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

turning 2 bits of SQL into 1

Author

Topic

steveUsingSQL
Starting Member

6 Posts

Posted - 2012-02-24 : 08:38:43

Hi,
Hoping someone can help.

1. This piece of SQL populates a table:

INSERT INTO tblECHO SELECT * FROM qrytblXYZ

2. The query (qrytblXYZ) code looks like:

SELECT DISTINCT tblALPHA.one, tblALPHA.Surname AS two, tblBRAVO.three,tblBRAVO.four
FROM tblCHARLIE
INNER JOIN tblALPHA ON tblCHARLIE.one = tblALPHA.one
INNER JOIN vwDELTA ON vwDELTA.five = tblALPHA.five
INNER JOIN tblBRAVO ON tblBRAVO.four = vwDELTA.four

3. Then this runs:

SELECT tblCHARLIE.six, tblCHARLIE.one, tblCHARLIE.[seven],
tblCHARLIE.[eight], tblCHARLIE.nine, tblECHO.two, tblCHARLIE.ten
FROM tblCHARLIE LEFT JOIN tblECHO ON tblCHARLIE.one = tblECHO.one
GROUP BY tblCHARLIE.six, tblCHARLIE.one, tblCHARLIE.[seven],
tblCHARLIE.[eight], tblCHARLIE.nine, tblECHO.two, tblCHARLIE.ten
ORDER BY tblCHARLIE.[seven];

I need to make this 1 SQL statement. Please can anyone help with this complex question. Many thanks in advance for todays conundrum.
Thanks, Steve

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-02-24 : 09:10:15

[code]
INSERT INTO tblECHO
SELECT required columns here....
FROM
(
SELECT DISTINCT tblALPHA.one, tblALPHA.Surname AS two, tblBRAVO.three,tblBRAVO.four
FROM tblCHARLIE
INNER JOIN tblALPHA ON tblCHARLIE.one = tblALPHA.one
INNER JOIN vwDELTA ON vwDELTA.five = tblALPHA.five
INNER JOIN tblBRAVO ON tblBRAVO.four = vwDELTA.four
)t
INNER JOIN (SELECT tblCHARLIE.six, tblCHARLIE.one, tblCHARLIE.[seven],
tblCHARLIE.[eight], tblCHARLIE.nine, tblECHO.two, tblCHARLIE.ten
FROM tblCHARLIE LEFT JOIN tblECHO ON tblCHARLIE.one = tblECHO.one
GROUP BY tblCHARLIE.six, tblCHARLIE.one, tblCHARLIE.[seven],
tblCHARLIE.[eight], tblCHARLIE.nine, tblECHO.two, tblCHARLIE.ten
)t1
ON t1.one = t.one
AND t1.two = t.two
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

steveUsingSQL
Starting Member

6 Posts

Posted - 2012-02-24 : 09:26:56

Thank you.

1. I am now no longer using table tblECHO. This is now a SELECT statement. So the SELECT statement needs to do everything without tblECHO.

The SQL code above in my original post(part 1 and 2) populated a temporary table, and then part 3 ran against that temporary table.

I hope you can still assist. Thank you.
Steve

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-02-24 : 09:56:22

so without using tblECHO how are tables related?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

steveUsingSQL
Starting Member

6 Posts

Posted - 2012-02-24 : 10:04:52

the secret to this is tblECHO and the the column "one" i believe.

There is a join in part 3 SQL:

FROM tblCHARLIE LEFT JOIN tblECHO ON tblCHARLIE.one = tblECHO.one

in SQL part 3, tblECHO needs to be replaced with the results from the part 2 SQL.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-02-24 : 10:39:12

[code]
INSERT INTO tblECHO
SELECT tblCHARLIE.six, tblCHARLIE.one, tblCHARLIE.[seven],
tblCHARLIE.[eight], tblCHARLIE.nine, t.two, tblCHARLIE.ten
FROM tblCHARLIE
LEFT JOIN
(SELECT DISTINCT tblALPHA.one, tblALPHA.Surname AS two, tblBRAVO.three,tblBRAVO.four
FROM tblCHARLIE
INNER JOIN tblALPHA ON tblCHARLIE.one = tblALPHA.one
INNER JOIN vwDELTA ON vwDELTA.five = tblALPHA.five
INNER JOIN tblBRAVO ON tblBRAVO.four = vwDELTA.four
)t
ON tblCHARLIE.one = t.one
GROUP BY tblCHARLIE.six, tblCHARLIE.one, tblCHARLIE.[seven],
tblCHARLIE.[eight], tblCHARLIE.nine, t.two, tblCHARLIE.ten
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

X002548
Not Just a Number

15586 Posts

Posted - 2012-02-24 : 10:50:18

The "Secret"...you guys crack me up

"Like it's a miracle!!!"

Or

"Do you believe in Miracles?"

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

steveUsingSQL
Starting Member

6 Posts

Posted - 2012-02-24 : 10:54:18

quote:

Originally posted by visakh16


INSERT INTO tblECHO
SELECT tblCHARLIE.six, tblCHARLIE.one, tblCHARLIE.[seven],
tblCHARLIE.[eight], tblCHARLIE.nine, t.two, tblCHARLIE.ten
FROM tblCHARLIE 
LEFT JOIN 
(SELECT DISTINCT tblALPHA.one, tblALPHA.Surname AS two, tblBRAVO.three,tblBRAVO.four
FROM tblCHARLIE
INNER JOIN tblALPHA ON tblCHARLIE.one = tblALPHA.one
INNER JOIN vwDELTA ON vwDELTA.five = tblALPHA.five
INNER JOIN tblBRAVO ON tblBRAVO.four = vwDELTA.four
)t
ON tblCHARLIE.one = t.one
GROUP BY tblCHARLIE.six, tblCHARLIE.one, tblCHARLIE.[seven],
tblCHARLIE.[eight], tblCHARLIE.nine, t.two, tblCHARLIE.ten

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Thank you for this, but i can now no longer use the temporary table - tblECHO - So i need to make this 1 SELECT statement and NOT an INSERT statement.
** EDIT **
Some results came back - i will now test them vigourously.
Many thanks!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-02-25 : 13:02:52

welcome...if you check my latest suggestion i'm not using tblECHO
so i think this is what you're looking for

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

steveUsingSQL
Starting Member

6 Posts

Posted - 2012-02-29 : 06:35:43

quote:
Originally posted by visakh16

welcome...if you check my latest suggestion i'm not using tblECHO
so i think this is what you're looking for

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Hello again,

This solution works great. I just need to modify it a bit. In the solution, the item: t.two, can have many values. It would be ideal to have the first row with all the fields, and then t.two listd by itself under this row. e.g.

________________________________
value | value | t.two | value |
_____|______| t.two |_____|
_____|______| t.two |_____|

Please can you help refine this.
Steve.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-02-29 : 11:48:49

this is a presntation issue and you should be handling it in your front end application. It is not all recommended to do this in sql

if you're using a reporting tool as front end, then this property is called suppress duplicates or remove duplicates

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

steveUsingSQL
Starting Member

6 Posts

Posted - 2012-03-01 : 05:19:39

quote:
Originally posted by visakh16

this is a presntation issue and you should be handling it in your front end application. It is not all recommended to do this in sql

if you're using a reporting tool as front end, then this property is called suppress duplicates or remove duplicates

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Thank You,
I think i am coming to that conclusion also.
I will have a look at Visual Studio and see if it can do that.
Steve

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-03-01 : 10:50:11

quote:
Originally posted by steveUsingSQL

quote:
Originally posted by visakh16

this is a presntation issue and you should be handling it in your front end application. It is not all recommended to do this in sql

if you're using a reporting tool as front end, then this property is called suppress duplicates or remove duplicates

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Thank You,
I think i am coming to that conclusion also.
I will have a look at Visual Studio and see if it can do that.
Steve

are you working on .NET app of reporting services?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources