-
Nov 4th, 2020, 03:15 PM
#1
Thread Starter
Frenzied Member
[RESOLVED] How to select using multiple column match?
Consider table A with columns named ID (PK), City, State, and more and table B with only City and State. How do I do a WHERE condition to show all the rows in A that match the city and state in B. I tried "Where City, State in (Select City, State from Blacklist)" but that doesn't work. I also tried EXISTS with no luck either.
-
Nov 4th, 2020, 04:05 PM
#2
Re: How to select using multiple column match?
Isn't it just something like this? Tested in Access and works as expected.
Code:
SELECT A.ID, A.City, A.State FROM A INNER JOIN B ON (A.City = B.City) AND (A.State = B.State)
-
Nov 4th, 2020, 04:09 PM
#3
Thread Starter
Frenzied Member
Re: How to select using multiple column match?
Using a join is a good idea. I'll go with that for now.
Is using a join the best method, or are there others I should consider?
-
Nov 4th, 2020, 04:17 PM
#4
Thread Starter
Frenzied Member
Re: How to select using multiple column match?
It worked well for the SELECT, but not for a DELETE criteria. Ug. I feel so stupid on this one.
-
Nov 4th, 2020, 04:30 PM
#5
Thread Starter
Frenzied Member
Re: How to select using multiple column match?
Code:
Where Exists (Select 1 From b Where a.City = b.City and a.State = b.State)
Works, but I'd be interested to see how others would do this.
-
Nov 5th, 2020, 01:08 PM
#6
Re: How to select using multiple column match?
both ways are totaly valid. there is a syntax for update/delete using a join for selection. you can also always do something like
Code:
DELETE FROM A WHERE ID IN (SELECT ID FROM A INNER JOIN B ON...)
-
Nov 5th, 2020, 02:01 PM
#7
Thread Starter
Frenzied Member
Re: How to select using multiple column match?
Unfortunately I don't have an ID. I was hoping I could compare two columns instead of ID but it doesn't like that. Non-Boolean something or other.
-
Nov 6th, 2020, 02:21 AM
#8
Re: How to select using multiple column match?
Why don't you build a computed column from your 2 columns and compare that one?
If you have fixed-length Text-Columns, use TRIM to cut off leading/trailing whitespace
Aircode
SQL Code:
SELECT/DELETE * FROM MyTable WHERE COLUMNA || COLUMNB IN (SELECT (COLUMNA || COLUMNB) AS COLCOMPUTED FROM MyTable WHERE SomeColumn=AFilter)
|| is a concat-operator. Lookup how it's done in your DBMS. Might even be a Function (CONCAT(COLUMNA,COLUMNB))
Last edited by Zvoni; Tomorrow at 31:69 PM.
----------------------------------------------------------------------------------------
One System to rule them all, One Code to find them,
One IDE to bring them all, and to the Framework bind them,
in the Land of Redmond, where the Windows lie
---------------------------------------------------------------------------------
People call me crazy because i'm jumping out of perfectly fine airplanes.
---------------------------------------------------------------------------------
Code is like a joke: If you have to explain it, it's bad
-
Nov 6th, 2020, 03:49 AM
#9
Re: How to select using multiple column match?
What database is this? Most support deleting directly from a join but you have to specify the alias of the table in the join that you want the delete to operate against. This is TSQL:-
Code:
Create Table DataTable (Col1 Integer, Col2 Integer)
Create Table LookupTable (Col1 Integer, Col2 Integer)
Insert into LookupTable Values (1,1)
Insert into LookupTable Values (2,2)
Insert into DataTable Values (1,1)
Insert into DataTable Values (2,2)
Insert into DataTable Values (3,3)
Insert into DataTable Values (4,4)
Select * From DataTable
Select * From LookupTable
Delete DT
From DataTable DT
Join LookupTable LT
on DT.Col1 = LT.Col1
and DT.Col2 = LT.Col2
Select * From DataTable
Select * From LookupTable
Drop Table DataTable
Drop Table LookupTable
Is using a join the best method, or are there others I should consider?
There's no single answer to this. I would say a join is always the best place to start but there are always exceptions and there's a whole bunch of alternative approaches that might perform better in some scenarios. From sub selects, IN clauses, creating artificial keys... the list is pretty endless and you're probably best picking them up as you go along rather than me trying to provide an exhaustive list (I'd fail and you'd be overwhelmed).
As long as you know how to work against a join you'll have a tool that's at least appropriate to 99% of cases and you'll probably never have to use anything else as a programmer. As a DBA, then you're more likely to need some of the other techniques from time to time.
Last edited by FunkyDexter; Nov 6th, 2020 at 03:56 AM.
The best argument against democracy is a five minute conversation with the average voter - Winston Churchill
Hadoop actually sounds more like the way they greet each other in Yorkshire - Inferrd
-
Nov 6th, 2020, 07:06 PM
#10
Thread Starter
Frenzied Member
Re: How to select using multiple column match?
MS SQL Server. This is cool Funky, thank you. I did try something like this but I didn't know about the alias. And thank you so much for including an example with all the sample creation. You rock.
-
Nov 10th, 2020, 03:36 PM
#11
Thread Starter
Frenzied Member
Re: How to select using multiple column match?
Thanks again FunkyDexter. I had never seen using an alias with a delete command. I never realized it was implicit most of the time. But that makes sense now to me that one needs to direct which table to delete rows from if there are multiple specified.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|