CODEWITHSUNDEEP
sql-server
user10466320
user10466320

Reputation: 23

Give reasons as to why my AND clause returns an empty string in SQL

I am new to SQL and it seems not to be the same as tradition coding. Anyways, I am trying to figure out why my results end up empty but only with the first AND statement. If I remove this statement, the code works. The syntax seems correct. What I am trying to do is match channel names with 'P' and 'HDP' at the end of the columns and not match channel numbers. Maybe I am wrong on the syntax. Any help on this matter would be appreciated. Also, I am using Microsoft SQL Server Management Studio 2012.

How the results should look:

enter image description here

SELECT a.ChannelNumber AS "Standard Channel",
       a.DisplayName AS "Standard Name",
       b.ChannelNumber AS "HD Channel",
       b.DisplayName AS "HD Name"               
FROM   CHANNEL a CROSS JOIN CHANNEL b
WHERE  b.ChannelNumber <> a.ChannelNumber
AND    b.DisplayName = a.DisplayName        /*this is what is giving me problems*/
AND    RIGHT(b.DisplayName, 3) LIKE '%HDP' 
AND    RIGHT(a.DisplayName, 1) LIKE '%P';

Upvotes: 1

Views: 211

Answers (2)

Zorkolot
Zorkolot

Reputation: 2027

To answer your question,

"Give reasons as to why my AND clause returns an empty string in SQL"

  • it's because you've declared a.DisplayName = b.DisplayName in the WHERE. And that can't be the case according to the picture of the output you've linked to because the Display names are spelled differently.

The only difference between standard and HD tables is HD tables end with "HDP". Standard tables never end with "HDP", though they do end with a "P".

In the absence of sample data, I've included the most basic example I could think of using a temp table.

DECLARE @CHANNEL TABLE(ChannelNumber int, DisplayName varchar(100))
INSERT INTO @CHANNEL VALUES
 (3, 'ABCP'), (25, 'ABCHDP')

SELECT a.ChannelNumber AS "Standard Channel",
       a.DisplayName AS "Standard Name",
       b.ChannelNumber AS "HD Channel",
       b.DisplayName AS "HD Name"  
  FROM @CHANNEL a CROSS JOIN @CHANNEL b
 WHERE LEFT(a.DisplayName, LEN(a.DisplayName) - 1) + 'HDP' = b.DisplayName
   AND a.DisplayName NOT LIKE '%HDP'
   AND b.DisplayName LIKE '%HDP'
   AND a.ChannelNumber <> b.ChannelNumber

Produces output:

Standard Channel    Standard Name   HD Channel  HD Name
               3    ABCP            25          ABCHDP

The algorithm identifies standard channels (NOT LIKE '%HDP') and HD channels (LIKE '%HDP') on the left and right sides of the CROSS JOIN.

Notice in your code you put: AND RIGHT(b.DisplayName, 3) LIKE '%HDP'... it is unnecessary to specify the RIGHT function with a length of chars- when you indicate the end of the string using LIKE '%HDP'.

LEFT(a.DisplayName, LEN(a.DisplayName) - 1) + 'HDP' cuts off the last char of the Standard Channel's DisplayName (which is always a 'P' by it's naming convention) and concatenates 'HDP' at the end of the result. This is compared to the format for HD channels which always end with 'HDP'.

When the conditions match you get a row of data.

Looking at the filtering conditions- you can see that a.DisplayName can never equal b.DisplayName

Upvotes: 0

Brad
Brad

Reputation: 12255

Ultimately you want things like AETVP and AETVHDP to be "equal". This doesn't seem like a use case for a Cross Join. You can break this down with a CTE.

First you'll define your HD channels, then your Standard Channels. In each of those blocks you can get the core part of the channel's name (the part without the P or HDP). Then join those together on the CoreName. This will enable us to join AETV to AETV

WITH HdChannels
AS (
    SELECT *
        ,CoreName = left(DisplayName, len(DisplayName) - len('HDP'))
    FROM Channel
    WHERE displayName LIKE '%HDP'
    )
    ,StdChannels
AS (
    SELECT *
        ,CoreName = left(DisplayName, len(DisplayName) - len('P'))
    FROM Channel
    WHERE displayName LIKE '%P'
        AND displayName NOT LIKE '%HDP'
    )
SELECT std.ChannelNumber AS [Standard Channel]
    ,std.DisplayName AS [Standard Name]
    ,hd.ChannelNumber AS [HD Channel]
    ,hd.DisplayName AS [HD Name]
FROM HdChannels hd
INNER JOIN StdChannels std ON std.CoreName = hd.CoreName

Upvotes: 1

Related Questions