[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 559
  • Last Modified:

SQL Exists on both SELECT and WHERE clauses

I have two tables. For example purposes, let's assume they are:
TableA (ID1 int,ID2 int)
TableB(FK_ID1 int,FK_ID2 int,bField bit)

I need to create a query that will return values from TableA and also a couple columns that declare if there's any record on TableB and if bField is true or false. I also need to perform a check against it on the where clause. My first query was:
SELECT ID1,ID2,CASE WHEN bField IS NOT NULL THEN 'True' ELSE 'False' END col1,CASE WHEN bField IS NOT NULL AND bField='True' THEN 'True' ELSE 'False' END col2
FROM TableA LEFT JOIN TableB ON ID1=FK_ID1 AND ID2=FK_ID2
WHERE CHARINDEX('|'+CAST(bField AS varchar(5))+'|',@bParam)>0

Open in new window

bParam can be '|True|','|False|' or '|True|False|'
This query has the problem that it creates many repeated records, depending on how many rows TableB has. So I tried:
SELECT ID1,ID2,CASE WHEN EXISTS (SELECT 'a' FROM TableB WHERE FK_ID1=ID1 AND FK_ID2=ID2) THEN 'True' ELSE 'False' END col1,CASE WHEN EXISTS (SELECT 'a' FROM TableB WHERE FK_ID1=ID1 AND FK_ID2=ID2 AND bField='True') THEN 'True' ELSE 'False' END col2
FROM TableA
WHERE CHARINDEX('|'+CASE WHEN EXISTS (SELECT 'a' FROM TableB WHERE FK_ID1=ID1 AND FK_ID2=ID2)+'|',@bParam)>0

Open in new window

Now, this last query works, but I was wondering if there's a more effective way to do this, as doing the same subquery twice seems to be a waste.
0
Cluskitt
Asked:
Cluskitt
1 Solution
 
BlueYonderCommented:
Looks like you only need a left outer join

SELECT ID1,ID2,CASE WHEN bField IS NOT NULL THEN 'True' ELSE 'False' END col1,CASE WHEN bField IS NOT NULL AND bField='True' THEN 'True' ELSE 'False' END col2
FROM TableA LEFT OUTER JOIN TableB ON ID1=FK_ID1 AND ID2=FK_ID2
WHERE CHARINDEX('|'+CAST(bField AS varchar(5))+'|',@bParam)>0
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
SELECT a.ID1, a.ID2
FROM TableA
   JOIN TableB on a.ID1 = b.FK_ID1 AND a.ID2 = b.FK_ID3
WHERE b.bField = True AND whatever

>bParam can be '|True|','|False|' or '|True|False|'
How is |True|False| possibile for a Bit column?  Last I remember it can either be True, False, or NULL
0
 
CluskittAuthor Commented:
@BlueYonder: If you'll notice, my first query is exactly the same as yours, other than mine saying LEFT JOIN instead of LEFT OUTER JOIN (which is the same thing). As I said, this will repeat records when there are more than one row on TableB for said ID1 and ID2.

@jimhorn: If I just join, I will lose the records on TableA that have no rows on TableB. Also, bParam is a string parameter. The bit field is bField.
0
Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

 
CluskittAuthor Commented:
To make it more clear:
TableB can have many rows with the same ID1 and ID2 (there are other fields that ensure the keys, but these are the only relevant ones here). There can be, for example, 10 rows where 7 have bField as False and 3 as True. What I need is for select to return True on col1 when there are records on TableB and True on col2 when there are any records that have bField as 'True', even if there are some as 'False'. Also, I need to use that same value on my where clause.

As I said, the second query will return the intended records, but doesn't seem to be efficient. One idea that occurred to me is that I could encapsulate the query as a subquery:
SELECT * FROM (SELECT ID1,ID2,CASE WHEN EXISTS (SELECT 'a' FROM TableB WHERE FK_ID1=ID1 AND FK_ID2=ID2) THEN 'True' ELSE 'False' END col1,CASE WHEN EXISTS (SELECT 'a' FROM TableB WHERE FK_ID1=ID1 AND FK_ID2=ID2 AND bField='True') THEN 'True' ELSE 'False' END col2
FROM TableA) t WHERE CHARINDEX('|'+col1+'|',@bParam)>0

Open in new window

I'm not sure which would be more efficient though, or if there's a better way.
0
 
ralmadaCommented:
I don't think both of your query will make a whole lot of difference in terms of efficiency. Best way will to check the execution plan

I would still be inclined to use the last one though (the subquery), although I would change the where clause, dropping the charindex

SELECT * FROM (
	SELECT 	ID1,
		ID2,
		CASE WHEN EXISTS (SELECT 'a' FROM TableB WHERE FK_ID1=ID1 AND FK_ID2=ID2) THEN 'True' ELSE 'False' END col1,
		CASE WHEN EXISTS (SELECT 'a' FROM TableB WHERE FK_ID1=ID1 AND FK_ID2=ID2 AND bField='True') THEN 'True' ELSE 'False' END col2
	FROM TableA
) t 
WHERE @bParam like '%|' + col1 + '|%'

Open in new window


Having said that, I still not sure why you need col2 there.
0
 
CluskittAuthor Commented:
col1 is whether there are records on TableB, true or false. col2 is whether there are records on TableB that are true. I also use a different param for col2, but I thought it wouldn't be necessary to place it in this example.

Is like '%something%' better than a charindex? It's my understanding that neither one is SARGable, so it shouldn't make much difference.

I will check the execution plans on both queries, but I don't have enough data on those tables to make a real difference (only about a dozen records for now).
0
 
ralmadaCommented:
>>I also use a different param for col2, but I thought it wouldn't be necessary to place it in this example.<<
Fair enough, I didn't see you using it there, that's why I asked.


>>Is like '%something%' better than a charindex? It's my understanding that neither one is SARGable, so it shouldn't make much difference.<<
I would still prefer the LIKE. Just my preference to make it more clear for me. Which one will performe better in your situation, well, I guess you need to check execution plan again :)

I would also suggest you look into having some indexes there in your FKs if performance is an issue.
0
 
CluskittAuthor Commented:
I'm pretty sure they're already there. I don't know if performance will be an issue or not. I just wanted the most efficient query I could make so I wouldn't have any issue later on when we have thousands of records.

I don't really know how to interpret the results of execution plans. They're a bit confusing to me.
0
 
ralmadaCommented:
if you post them here, I could give it a try
0
 
CluskittAuthor Commented:
Ok, I'll post them. Basically, each of the two queries, one with CHARINDEX, the other with LIKE.

EDIT: I had to change the .sqlplan to .sql, because EE uploader doesn't allow .sqlplan
Execs.zip
0
 
ralmadaCommented:
Ok, I don't have a server to play around with here, but will take a look later. Unless you want to post a screenshot.
0
 
CluskittAuthor Commented:
That's ok. It's not urgent. You can study them at your leisure.
0
 
Scott PletcherSenior DBACommented:
SELECT a.ID1, a.ID2,
    CASE WHEN b.FK_ID1 IS NOT NULL THEN 'True' ELSE 'False' END AS col1,
    CASE WHEN b.bFieldFlag = 1 THEN 'True' ELSE 'False' END AS col2
FROM dbo.TableA a
LEFT OUTER JOIN (
    SELECT FK_ID1, FK_ID2, MAX(CASE WHEN bField = 'True' THEN 1 ELSE 0 END) AS bFieldFlag
    FROM dbo.TableB
    GROUP BY
        FK_ID1, FK_ID2
) AS b ON
    b.FK_ID1 = a.ID1 AND
    b.FK_ID2 = a.ID2
WHERE
    CHARINDEX('|'+CASE WHEN b.FK_ID1 IS NOT NULL THEN 'True' ELSE 'False' END+'|',@bParam) > 0
0
 
CluskittAuthor Commented:
That is a great solution. It seems to be the most efficient way of achieving this. I'm going to try it on the real query and get back to you later.
0
 
CluskittAuthor Commented:
Sorry for taking so long, but I've been busy.
This works wonderfully and seems efficient enough.
Thanks for your help.
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now