jeudi 4 juin 2015

Stop returning multiple similar rows on joins

I have the following scenario in SQLite.

TableA 

ID
-----
1   |
2   |
3   |


Table B

ID  | AID   |Tag
----------------
1   | 1     | Hide 
2   | 1     | Show
3   | 2     | Null
4   | 3     | Show 

Table B has column AID which is the IDs of table A.

In the example above Table A ID: '1' has -> Table B ID of '1' and '2' and Tags 'Hide' and 'Show' attached to it.

I am looking for an SQL that will only return, in the example above, Table A IDs: '2' and '3'. Basically, as TableA ID: '1' has a 'Hide' tag attached to it, don't return it (even though it also has a show tag attached to it)

The sql I am using is (excuses the names, this is just a quick example)

select 
        a.ID    as a_ID,
        b.ID    as b_ID,
        b.Tag   as Tag
    from
        Table A as a
    left join Table B on a.id = b.aID
        and b.tag != 'hide' 

The problem with this SQL it's still returning

a_ID    | b_ID  | Tag
-------------------------------
1       | 2     | Show

I'm a tad stuck and any help would be really appreciated. I'm not 100% sure how I would work this on for a google search. The closest I got was this question How to return only 1 row if multiple duplicate rows and still return rows that are not duplicates? but I couldn't work out how the GROUP BY would help here.

If anymore info is needed, just let me know.

Thanks in advance for any help.

Jon

Aucun commentaire:

Enregistrer un commentaire