none
Can this query be better constructed

    Question

  • I notice that this query when analyzed returned a warning of "No Join Predicate" which worries me especially since I also noticed that it fails to run when applied as a sqldatasource...returning an error of multipart identifier cannot be bound..it runs fine in management studio btw, I don't mind using it in code behind or stored proc...im just wondering if this thing is testing the boundaries of a Cartesian join.

    DECLARE @ID INT = 4053717; 
    
    SELECT  CASE WHEN HISTORY >= 1
                      AND ELA >= 3
                      AND MATH >= 1
                      AND SCIENCE >= 2
                      AND FL >= 1
                      AND VA >= 0
                      AND Prep >= 0 THEN 'Yes'
                 ELSE 'No'
            END AS [On Target?]
    FROM    ( SELECT    SUM(HISTORY) AS HISTORY ,
                        SUM(ELA) AS ELA ,
                        SUM(MATH) AS MATH ,
                        SUM(SCIENCE) AS SCIENCE ,
                        SUM(FL) AS FL ,
                        SUM(VA) AS VA ,
                        SUM(Prep) AS Prep
              FROM      ( SELECT    COUNT(CASE WHEN CRS.U1 = 'A'
                                                    AND ( HIS.MK NOT LIKE '%FD%' )
                                               THEN STU.ID
                                          END) AS HISTORY ,
                                    COUNT(CASE WHEN CRS.U1 = 'B'
                                                    AND ( HIS.MK NOT LIKE '%FD%' )
                                               THEN STU.ID
                                          END) AS ELA ,
                                    COUNT(CASE WHEN CRS.U1 = 'C'
                                                    AND ( HIS.MK NOT LIKE '%FD%' )
                                               THEN STU.ID
                                          END) AS MATH ,
                                    COUNT(CASE WHEN CRS.U1 = 'D'
                                                    AND ( HIS.MK NOT LIKE '%FD%' )
                                               THEN STU.ID
                                          END) AS SCIENCE ,
                                    COUNT(CASE WHEN CRS.U1 = 'E'
                                                    AND ( HIS.MK NOT LIKE '%FD%' )
                                               THEN STU.ID
                                          END) AS FL ,
                                    COUNT(CASE WHEN CRS.U1 = 'F'
                                                    AND ( HIS.MK NOT LIKE '%FD%' )
                                               THEN STU.ID
                                          END) AS VA ,
                                    COUNT(CASE WHEN CRS.U1 = 'G'
                                                    AND ( HIS.MK NOT LIKE '%FD%' )
                                               THEN STU.ID
                                          END) AS Prep
                          FROM      CRS AS CRS
                                    INNER JOIN HIS ON CRS.CN = HIS.CN
                                    INNER JOIN STU ON HIS.PID = STU.ID
                          WHERE     ( STU.ID = @ID )
                          UNION ALL
                          SELECT    COUNT(CASE WHEN CRS.U1 = 'A'
                                                    AND ( GRD.M1 NOT LIKE '%FD%' )
                                               THEN STU.ID
                                          END) AS HISTORY ,
                                    COUNT(CASE WHEN CRS.U1 = 'B'
                                                    AND ( GRD.M1 NOT LIKE '%FD%' )
                                               THEN STU.ID
                                          END) AS ELA ,
                                    COUNT(CASE WHEN CRS.U1 = 'C'
                                                    AND ( GRD.M1 NOT LIKE '%FD%' )
                                               THEN STU.ID
                                          END) AS MATH ,
                                    COUNT(CASE WHEN CRS.U1 = 'D'
                                                    AND ( GRD.M1 NOT LIKE '%FD%' )
                                               THEN STU.ID
                                          END) AS SCIENCE ,
                                    COUNT(CASE WHEN CRS.U1 = 'E'
                                                    AND ( GRD.M1 NOT LIKE '%FD%' )
                                               THEN STU.ID
                                          END) AS FL ,
                                    COUNT(CASE WHEN CRS.U1 = 'F'
                                                    AND ( GRD.M1 NOT LIKE '%FD%' )
                                               THEN STU.ID
                                          END) AS VA ,
                                    COUNT(CASE WHEN CRS.U1 = 'G'
                                                    AND ( GRD.M1 NOT LIKE '%FD%' )
                                               THEN STU.ID
                                          END) AS Prep
                          FROM      CRS AS CRS
                                    INNER JOIN GRD ON GRD.CN = CRS.CN
                                    INNER JOIN STU ON GRD.SN = STU.SN
                          WHERE     ( STU.ID = @ID )
                        ) AS r
            ) AS derived

    Thursday, October 17, 2013 5:53 AM

Answers

  • Please try this

    					SELECT    CASE WHEN 
    								SUM(CASE WHEN CRS.U1 = 'A' THEN 1 ELSE 0 END) >= 1 --AS HISTORY 
                                    SUM(CASE WHEN CRS.U1 = 'B' THEN 1 ELSE 0 END) >= 3 --AS ELA ,
                                    SUM(CASE WHEN CRS.U1 = 'C' THEN 1 ELSE 0 END) >= 1 --AS MATH ,
                                    SUM(CASE WHEN CRS.U1 = 'D' THEN 1 ELSE 0 END) >= 2 --AS SCIENCE ,
                                    SUM(CASE WHEN CRS.U1 = 'E' THEN 1 ELSE 0 END) >= 1 --AS FL ,
                                    SUM(CASE WHEN CRS.U1 = 'F' THEN 1 ELSE 0 END) >= 0 --AS VA ,
                                    SUM(CASE WHEN CRS.U1 = 'G' THEN 1 ELSE 0 END) >= 0 --AS Prep
    							  THEN 'Yes' ELSE 'No' END
    					  FROM
                          (SELECT CRS.U1     
                          FROM      CRS AS CRS
                                    INNER JOIN HIS ON CRS.CN = HIS.CN
                                    INNER JOIN STU ON HIS.PID = STU.ID
                          WHERE     ( STU.ID = @ID ) AND (HIS.MK NOT LIKE '%FD%')
                          UNION ALL
                          SELECT CRS.U1                      
                          FROM      CRS AS CRS
                                    INNER JOIN GRD ON GRD.CN = CRS.CN
                                    INNER JOIN STU ON GRD.SN = STU.SN
                          WHERE     ( STU.ID = @ID ) AND (GRD.M1 NOT LIKE '%FD%')
                          )derived

    Note: Not tested

    Thanks

    Saravana Kumar C

    Thursday, October 17, 2013 7:22 AM

All replies

  • The code doesnt have any syntactical errors. Where did you get the error?
    Thursday, October 17, 2013 6:13 AM
  • Please try this

    					SELECT    CASE WHEN 
    								SUM(CASE WHEN CRS.U1 = 'A' THEN 1 ELSE 0 END) >= 1 --AS HISTORY 
                                    SUM(CASE WHEN CRS.U1 = 'B' THEN 1 ELSE 0 END) >= 3 --AS ELA ,
                                    SUM(CASE WHEN CRS.U1 = 'C' THEN 1 ELSE 0 END) >= 1 --AS MATH ,
                                    SUM(CASE WHEN CRS.U1 = 'D' THEN 1 ELSE 0 END) >= 2 --AS SCIENCE ,
                                    SUM(CASE WHEN CRS.U1 = 'E' THEN 1 ELSE 0 END) >= 1 --AS FL ,
                                    SUM(CASE WHEN CRS.U1 = 'F' THEN 1 ELSE 0 END) >= 0 --AS VA ,
                                    SUM(CASE WHEN CRS.U1 = 'G' THEN 1 ELSE 0 END) >= 0 --AS Prep
    							  THEN 'Yes' ELSE 'No' END
    					  FROM
                          (SELECT CRS.U1     
                          FROM      CRS AS CRS
                                    INNER JOIN HIS ON CRS.CN = HIS.CN
                                    INNER JOIN STU ON HIS.PID = STU.ID
                          WHERE     ( STU.ID = @ID ) AND (HIS.MK NOT LIKE '%FD%')
                          UNION ALL
                          SELECT CRS.U1                      
                          FROM      CRS AS CRS
                                    INNER JOIN GRD ON GRD.CN = CRS.CN
                                    INNER JOIN STU ON GRD.SN = STU.SN
                          WHERE     ( STU.ID = @ID ) AND (GRD.M1 NOT LIKE '%FD%')
                          )derived

    Note: Not tested

    Thanks

    Saravana Kumar C

    Thursday, October 17, 2013 7:22 AM
  • thanks for the effort but it has a lot syntax errors..
    Thursday, October 17, 2013 3:27 PM
  • Please post DDL, so that people do NOT have to guess what the keys, constraints, declarative referential integrity, data types, etc. in your schema are. learn how to follow ISO-11179 data element naming conventions and formatting rules. You have no idea how to do this and have used awful names! Temporal data should use ISO-8601 formats. code should be in standard SQL as much as possible and NOT local dialect. 

    this is minimal polite behavior ON SQL forums. 

    You have the generic “id” of Kabbalah magic! In violation of a tiered architecture, you do display formatting. Why do you think names like “CRS” and “Prep” are clear and precise table names? None of the column names make sense!! Think about them! “math_<something>” could be correct; credits? Courses? SAT score? What?? 

    Try again and follow the rules. This looks like a total disaster; too many self-joins and too many LIKE predicates. 

    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

    Thursday, October 17, 2013 8:58 PM