Results 1 to 11 of 11

Thread: [RESOLVED] How to select using multiple column match?

  1. #1

    Thread Starter
    Frenzied Member cory_jackson's Avatar
    Join Date
    Dec 2011
    Location
    Fallbrook, California
    Posts
    1,104

    Resolved [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.

  2. #2
    PowerPoster
    Join Date
    Nov 2017
    Posts
    3,116

    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)

  3. #3

    Thread Starter
    Frenzied Member cory_jackson's Avatar
    Join Date
    Dec 2011
    Location
    Fallbrook, California
    Posts
    1,104

    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?

  4. #4

    Thread Starter
    Frenzied Member cory_jackson's Avatar
    Join Date
    Dec 2011
    Location
    Fallbrook, California
    Posts
    1,104

    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.

  5. #5

    Thread Starter
    Frenzied Member cory_jackson's Avatar
    Join Date
    Dec 2011
    Location
    Fallbrook, California
    Posts
    1,104

    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.

  6. #6
    Frenzied Member
    Join Date
    May 2014
    Location
    Central Europe
    Posts
    1,372

    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...)

  7. #7

    Thread Starter
    Frenzied Member cory_jackson's Avatar
    Join Date
    Dec 2011
    Location
    Fallbrook, California
    Posts
    1,104

    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.

  8. #8
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    4,418

    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:
    1. SELECT/DELETE * FROM
    2. MyTable
    3. 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

  9. #9
    Super Moderator FunkyDexter's Avatar
    Join Date
    Apr 2005
    Location
    An obscure body in the SK system. The inhabitants call it Earth
    Posts
    7,900

    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

  10. #10

    Thread Starter
    Frenzied Member cory_jackson's Avatar
    Join Date
    Dec 2011
    Location
    Fallbrook, California
    Posts
    1,104

    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.

  11. #11

    Thread Starter
    Frenzied Member cory_jackson's Avatar
    Join Date
    Dec 2011
    Location
    Fallbrook, California
    Posts
    1,104

    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
  •  



Click Here to Expand Forum to Full Width