logo
down
shadow

SQL Not returning Expected Records


SQL Not returning Expected Records

By : user2954220
Date : November 22 2020, 01:01 AM
hop of those help? NOT IN can be dangerous. The problem is probably caused because Applicant_Mail_id takes on NULL values. You can fix this readily with:
code :
SELECT COUNT(*)
FROM Backyard_Burn
WHERE YEAR(Expiration_Date) = 2013 AND
      Applicant_Mail_ID NOT IN (SELECT Applicant_Mail_ID 
                                FROM Backyard_Burn 
                                WHERE YEAR(Expiration_Date) = 2014 AND Applicant_Mail_ID IS NOT NULL
                               );
SELECT COUNT(*)
FROM Backyard_Burn bb
WHERE YEAR(Expiration_Date) = 2013 AND
      NOT EXISTS (SELECT 1 
                  FROM Backyard_Burn bb2
                  WHERE YEAR(bb2.Expiration_Date) = 2014 AND
                        bb2.Applicant_Mail_ID = bb.Applicant_Mail_ID
                 );
select Applicant_Mail_ID
from Backyard_Burn
group by Applicant_Mail_ID
having sum(case when year(Expiration_Date) = 2013 then 1 else 0 end) > 0 and
       sum(case when year(Expiration_Date) = 2014 then 1 else 0 end) > 0;


Share : facebook icon twitter icon
This self-left-join is returning more records than I expected it to

This self-left-join is returning more records than I expected it to


By : Aleksandrs Krupenņik
Date : March 29 2020, 07:55 AM
I hope this helps you . There are many way on how solve this particular problem. The one I used is by using a subquery which gets the current exchange date for every combination of *currency_from, currency_to*. The result of the subquery is only 3 columns and that's the reason why we need to join it on the original table table to get the remaining columns, provided that it matches with: currency_from, currency_to, and exchange_date.
code :
SELECT  a.*
FROM    currency_exchange a
        INNER JOIN
        (
            SELECT  currency_from, currency_to, MAX(exchange_date) max_date
            FROM    currency_exchange
            GROUP   BY currency_from, currency_to
        ) b ON  a.currency_from = b.currency_from AND
                a.currency_to = b.currency_to AND
                a.exchange_date = b.max_date
╔════╦═══════════════╦═════════════╦═════════╦══════════╦═════════════════════╗
║ ID ║ CURRENCY_FROM ║ CURRENCY_TO ║ RATE_IN ║ RATE_OUT ║    EXCHANGE_DATE    ║
╠════╬═══════════════╬═════════════╬═════════╬══════════╬═════════════════════╣
║ 20 ║             1 ║           2 ║ 1.43    ║ 1.65     ║ 2013-03-05 15:55:29 ║
║ 19 ║             1 ║           3 ║ 1.1     ║ 1.26     ║ 2013-03-05 15:49:45 ║
║ 16 ║             2 ║           3 ║ 2       ║ 3        ║ 2012-11-16 15:44:33 ║
╚════╩═══════════════╩═════════════╩═════════╩══════════╩═════════════════════╝
Unicode T-SQL like not returning expected all records

Unicode T-SQL like not returning expected all records


By : Chex_mix
Date : March 29 2020, 07:55 AM
like below fixes the issue In my tests, if NVARCHAR data type is used with Indic_General_100_CI_AI collation then LIKE will returns required results:
code :
SELECT  *
FROM (
    SELECT N'क'  UNION ALL
    SELECT N'कु' UNION ALL
    SELECT N'how (strange!)'
) x (Author)
WHERE   x.Author    COLLATE Indic_General_100_CI_AI 
LIKE    N'क%'       COLLATE Indic_General_100_CI_AI 
Author
--------------
क
कु
MYSQL Query not returning expected records

MYSQL Query not returning expected records


By : Forrest Herlick
Date : March 29 2020, 07:55 AM
Hope this helps I think subqueries make sense for this query. The first subquery selects the id of the plan of your video and the second subquery selects the ids of the associated plans.
code :
SELECT 
    sp.id,
    sp.plan,
    sp.days_limit,
    sp.rate 
FROM 
    subscription_plans sp
WHERE 
    id IN (SELECT DISTINCT plan_id FROM subscribed_videos sv where sv.videoid = $videoid)
    OR id IN (SELECT DISTINCT assosiated_plan_id 
        FROM subscription_groups sg
        JOIN subscribed_videos sv ON sv.plan_id = sg.plan_id
        WHERE sv.videoid = $videoid)
QoQ: LIKE comparison not returning all records expected

QoQ: LIKE comparison not returning all records expected


By : Albert o
Date : March 29 2020, 07:55 AM
I wish this help you First time poster. I can normally figure these things out on my own. But alas I've hit a brick wall.
code :
... WHERE lowercase(item_name) LIKE '%electrodes%'
SQL Left Join returning more records than expected

SQL Left Join returning more records than expected


By : Diana Narvaez Orozco
Date : March 29 2020, 07:55 AM
I wish this helpful for you I am trying to join two tables dbo.contact and dbo.notes using the contactno column, when I only run the query for dbo.contact , If you want only one note per contact, you can use outer apply:
shadow
Privacy Policy - Terms - Contact Us © ourworld-yourmove.org