SQL JOIN not returning wanted results

Question

I'm running Oracle 11g but this is a more general SQL question.

So I have the following tables/data -

CREATE TABLE TEST_GROUP ( ID VARCHAR2(20), CONSTRAINT test_group_pk PRIMARY KEY (ID) ); INSERT INTO TEST_GROUP VALUES ('AAA'); CREATE TABLE GROUP_PREFERENCE ( GROUP_ID VARCHAR2(20), ID VARCHAR2(20), TYPE VARCHAR2(20), CONSTRAINT GROUP_PREF_PK PRIMARY KEY (GROUP_ID, ID, TYPE) ); INSERT INTO GROUP_PREFERENCE VALUES ('AAA', 'AAA-P1', 'CONFIGURABLE'); INSERT INTO GROUP_PREFERENCE VALUES ('AAA', 'AAA-P2', 'ENABLED'); CREATE TABLE PREFERENCE_ENTRY ( PREFERENCE_ID VARCHAR2(20), PREFERENCE_VALUE VARCHAR2(20), CONSTRAINT PREF_PK PRIMARY KEY (PREFERENCE_ID, PREFERENCE_VALUE) ); INSERT INTO PREFERENCE_ENTRY VALUES ('AAA-P1', 'TRUE'); INSERT INTO PREFERENCE_ENTRY VALUES ('AAA-P2', 'TRUE');

My SQL -

SELECT g.ID FROM TEST_GROUP g JOIN GROUP_PREFERENCE gp ON g.ID = gp.GROUP_ID JOIN PREFERENCE_ENTRY pe ON gp.ID = pe.PREFERENCE_ID WHERE ( (gp.TYPE = 'CONFIGURABLE' AND pe.PREFERENCE_VALUE = 'TRUE') AND (gp.TYPE = 'ENABLED' AND pe.PREFERENCE_VALUE = 'TRUE') )

I want the SQL to return the TEST_GROUP 'AAA' but it looks like because I am testing for both PREFERENCE_ENTRY values it doesn't. If I test for either one on it's own it does return the row but I need to test that both PREFERENCE_ENTRY entries are 'TRUE'.

I'm sure this is quite simple but I'm struggling with it...

Any help from you SQL gurus will be most appreciated.

Thanks in advance.

gp.TYPE = 'CONFIGURABLE' and gp.TYPE = 'ENABLED' cannot be true at all — vadzim dvorak
– vadzim dvorak, Commented Dec 15, 2017 at 12:07

Thorsten Kettner · Accepted Answer · 2017-12-15 12:51:24Z

Here is another option that may seem like the straight-forward approach.

You noticed that you cannot join the tables and then use the WHERE clause to check for 'CONFIGURABLE' and 'ENABLED', because the WHERE clause refers to one row and one row's type cannot be 'CONFIGURABLE' and 'ENABLED' at the same time.

What you would do instead is select from the main table and look up the other tables in the WHERE clause with EXISTS or IN (where IN is the simpler of the two).

select id from test_group where id in ( select gp.group_id from group_preference gp join preference_entry pe on gp.id = pe.preference_id where gp.type = 'CONFIGURABLE' and pe.preference_value = 'TRUE' ) and id in ( select gp.group_id from group_preference gp join preference_entry pe on gp.id = pe.preference_id where gp.type = 'ENABLED' and pe.preference_value = 'TRUE' );

Simply aggregating over group_preference and preference_entry as shown by Gordon is easier though and the preferred approach (you can remove the table TEST_GROUP from his query - it's not needed). But sometimes there is a situation where you need lookups with EXISTS or IN instead, so I thought I'd just mention the method :-)

DamianR · Accepted Answer · 2017-12-15 12:52:08Z

2

In Where conditions use OR not AND

(...) WHERE ( (gp.TYPE = 'CONFIGURABLE' AND pe.PREFERENCE_VALUE = 'TRUE') OR (gp.TYPE = 'ENABLED' AND pe.PREFERENCE_VALUE = 'TRUE') )

Edit:

SELECT g.ID FROM TEST_GROUP g JOIN GROUP_PREFERENCE gp1 ON g.ID = gp1.GROUP_ID and gp1.TYPE='CONFIGURABLE' JOIN GROUP_PREFERENCE gp2 ON g.ID = gp2.GROUP_ID and gp2.TYPE='ENABLED' JOIN PREFERENCE_ENTRY pe1 ON gp1.ID = pe1.PREFERENCE_ID JOIN PREFERENCE_ENTRY pe2 ON gp2.ID = pe2.PREFERENCE_ID WHERE pe1.PREFERENCE_VALUE = 'TRUE' and pe2.PREFERENCE_VALUE = 'TRUE'

edited Dec 15, 2017 at 12:52

answered Dec 15, 2017 at 12:05

DamianR

814 bronze badges

7 Comments

Hallam Over a year ago

But I only want the row returned if both are true.

asthasr Over a year ago

How can gp.TYPE equal two values?

Hallam Over a year ago

Yes I know, that's the problem. But I only want 'AAA' returned if both 'preferences' are TRUE.

Hallam Over a year ago

I already tried as you have suggested but to me it just didn't look right and so I thought I must be missing some more obvious way...?

Thorsten Kettner Over a year ago

Don't use CASE WHEN in WHERE; use AND and OR instead. You will probably notice that your second query does not work. For the same reason that OP's query doesn't. A type cannot be 'CONFIGURABLE' and 'ENABLED' at the same time.

|

Gordon Linoff · Accepted Answer · 2017-12-15 12:11:23Z

You can use group by and having. Oracle also has a nice shorthand for what you want to do using in:

SELECT g.ID FROM TEST_GROUP g JOIN GROUP_PREFERENCE gp ON g.ID = gp.GROUP_ID JOIN PREFERENCE_ENTRY pe ON gp.ID = pe.PREFERENCE_ID WHERE (gp.TYPE, pe.PREFERENCE_VALUE) IN ( ('CONFIGURABLE', 'TRUE'), ('ENABLED', 'TRUE') ) GROUP BY g.ID HAVING COUNT(DISTINCT gp.TYPE) = 2;

Could you explain a little as to why the HAVING is needed. It seems to work without that?
Because you only want to see groups that contain both types.

Collectives™ on Stack Overflow

SQL JOIN not returning wanted results

3 Answers 3

Comments

7 Comments

2 Comments

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

7 Comments

2 Comments

Related