Multiple SELECT statements - 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

Multiple SELECT statements

Author

Topic

KewlToyZ021
Starting Member

5 Posts

Posted - 2010-03-31 : 10:26:05

Good day, its been a few years since I did any SQL queries.
I'm trying to get my head around the order of logical operations all over.
Mainly conditions to create columns then grouping them by the ProjectNumber and outputting them when they meet a final set of conditions.

What is troubling me is how to get multiple conditions in a select statement to create separate record columns and group them by the ProjectNumber from two tables as long as the product id meets a third set of conditions.

I was considering UNION but the order to get the SELECT statement together is giving me trouble as well as getting the distinct records.

Any direction toward a more eloquent solution would be appreciated :)

SELECT
Projects.ProjectNumber,
Projects.ProjectName,
Projects.Studio,
Projects.SqFtNew,
Projects.SqFtReno,
ProjectContacts.Contact as Client Where ContactTypeID = 1,
ProjectContacts.Contact as Architect Where ContactTypeID = 2,
ProjectContacts.Contact as Owner Where ContactTypeID = 3,
ProjectContacts.Contact as Engineer Where ContactTypeID = 4,

FROM Projects, ProjectContacts

WHERE
(Status = 'Active' OR Projects.Status = 'Start Date') and Studio ='A'

ORDER BY ProjectNumber

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2010-03-31 : 10:36:41

A CASE statement would be appropriate here:


case when  ContactTypeID = 1 then ProjectContacts.Contact else null end as Client,
case when ContactTypeID = 2 then ProjectContacts.Contact else null end as Architect,
...

Harsh Athalye
http://www.letsgeek.net/

KewlToyZ021
Starting Member

5 Posts

Posted - 2010-03-31 : 13:34:56

quote:
Originally posted by harsh_athalye

A CASE statement would be appropriate here:

case when ContactTypeID = 1 then ProjectContacts.Contact else null end as Client,
case when ContactTypeID = 2 then ProjectContacts.Contact else null end as Architect,
...

Harsh Athalye
http://www.letsgeek.net/

Thank you Harsh
Now I finally see the data entry errors causing the failures in other queries.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-31 : 13:43:33

please keep in mind that in your original post you've not added any condition to specify relation between tables so it will do a cross join between them and bring you all possible combinations of records from both tables satisfying the given conditions ((Status = 'Active' OR Projects.Status = 'Start Date') and Studio ='A')

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

KewlToyZ021
Starting Member

5 Posts

Posted - 2010-03-31 : 15:06:37

quote:
Originally posted by visakh16

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

Thanks, I'm trying to trim that data down, part of the problem is it is SQL 2000 and had multiple applications updating table fields over the years. Lots of data entry errors I am trying to filter out of the result. So i have different instances of NULL and empty strings generating extra records with the way the query is running.

Wondering if I should Update the table for the empty strings in that column with NULL which isn't a bad idea to start with or try to just continue editing the query to filter out Empty & NULL rows for that column.


SELECT 
Projects.ProjectNumber AS Project,
Projects.ProjectName AS Title, 
Projects.Studio,
Projects.SqFtNew,
Projects.SqFtReno, 
ProjectContacts.ContactTypeID AS Type,
CASE WHEN ContactTypeID = 1 THEN ProjectContacts.Contact ELSE NULL END AS Client ,
CASE WHEN ContactTypeID = 2 THEN ProjectContacts.Contact ELSE NULL END AS Architect,	
CASE WHEN ContactTypeID = 3 THEN ProjectContacts.Contact ELSE NULL END AS Owner,
CASE WHEN ContactTypeID = 4 THEN ProjectContacts.Contact ELSE NULL END AS Engineer

FROM Projects, ProjectContacts 

WHERE
(ProjectContacts.ContactTypeID < 5) AND (ProjectContacts.Contact IS NOT NULL) AND (Status = 'Active' OR Projects.Status = 'Start Date') AND Studio ='A'

ORDER BY ProjectNumber

KewlToyZ021
Starting Member

5 Posts

Posted - 2010-03-31 : 16:12:24

Filtered out the Empty Strings and NULLS.
I have to find another column because of all the data entry errors returning the same project number for every time someone tried entering a variant for a contact on the project. Over 2 million records still returned because people entered it instead of looking it up with the older interfaces.


SELECT 
Projects.ProjectNumber AS Project,
Projects.ProjectName AS Title, 
Projects.Studio,
Projects.SqFtNew,
Projects.SqFtReno, 
ProjectContacts.ContactTypeID AS Type,
CASE WHEN ContactTypeID = 1 THEN ProjectContacts.Contact ELSE NULL END AS Client ,
CASE WHEN ContactTypeID = 2 THEN ProjectContacts.Contact ELSE NULL END AS Architect,         
CASE WHEN ContactTypeID = 3 THEN ProjectContacts.Contact ELSE NULL END AS Owner,
CASE WHEN ContactTypeID = 4 THEN ProjectContacts.Contact ELSE NULL END AS Engineer

FROM Projects, ProjectContacts 

WHERE
(ProjectContacts.Contact <> '') AND (ProjectContacts.Contact IS NOT NULL) AND (ProjectContacts.ContactTypeID < 5) AND (Status = 'Active' OR Projects.Status = 'Start Date') AND Studio ='A'

ORDER BY ProjectNumber

KewlToyZ021
Starting Member

5 Posts

Posted - 2010-03-31 : 16:50:21

What an insane speed difference:


set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON

SET NOCOUNT ON

      SELECT     
      ProjectNumber, 
      ProjectName, 
      Studio,
      SqFtNew,
      SqFtReno, 
      PCArchitect.Contact as Architect,
      PCClient.Contact as Client,
      PCOwner.Contact as Owner,
      PCEngineer.Contact as Engineer

FROM
      Projects P
      LEFT JOIN ProjectContacts PCArchitect
            ON PCArchitect.ProjectID = P.ProjectID AND PCArchitect.ContactTypeID = 2
      LEFT JOIN ProjectContacts PCClient
            ON PCClient.ProjectID = P.ProjectID AND PCClient.ContactTypeID = 1
      LEFT JOIN ProjectContacts PCOwner
            ON PCOwner.ProjectID = P.ProjectID AND PCOwner.ContactTypeID= 3
      LEFT JOIN ProjectContacts PCEngineer
            ON PCEngineer.ProjectID = P.ProjectID AND PCEngineer.ContactTypeID = 4

WHERE
       (Status = 'Active' OR STATUS = 'Start Date') 

SET NOCOUNT OFF

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-01 : 04:55:05

quote:

Originally posted by KewlToyZ021

What an insane speed difference:


set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON

SET NOCOUNT ON

      SELECT     
      ProjectNumber, 
      ProjectName, 
      Studio,
      SqFtNew,
      SqFtReno, 
      PCArchitect.Contact as Architect,
      PCClient.Contact as Client,
      PCOwner.Contact as Owner,
      PCEngineer.Contact as Engineer

FROM
      Projects P
      LEFT JOIN ProjectContacts PCArchitect
            ON PCArchitect.ProjectID = P.ProjectID AND PCArchitect.ContactTypeID = 2
      LEFT JOIN ProjectContacts PCClient
            ON PCClient.ProjectID = P.ProjectID AND PCClient.ContactTypeID = 1
      LEFT JOIN ProjectContacts PCOwner
            ON PCOwner.ProjectID = P.ProjectID AND PCOwner.ContactTypeID= 3
      LEFT JOIN ProjectContacts PCEngineer
            ON PCEngineer.ProjectID = P.ProjectID AND PCEngineer.ContactTypeID = 4

WHERE
       (Status = 'Active' OR STATUS = 'Start Date') 

SET NOCOUNT OFF

Now it has translated into join based on condition rather than returning all possible combinations

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources