Phelipe Freitas Phelipe Freitas - 6 months ago 7
SQL Question

SQL Server query don't retrieves expected result

There are two tables:

certificate
and
store
. I have to write a query to return certificates that have a
store_id
in store_id or fk_store_id columns in
store
table. This query has to be filtered by person_id and store_id.

I've already tried some queries, but it don't bind the correct certificate with the right store.

This is my certificate table:

STORE_ID | CERTIFICATE_ID | PERSON_ID
---------|----------------|----------
1464 | 87083 | 100258
336 | 87123 | 100258


This is my store table:

STORE_ID | FK_STORE_ID | STORE_DESC
---------|-------------|---------------
336 | 1464 | A First Store
1117 | 1464 | Store two
1464 | 340 | The Third


I've built this query:

SELECT
S.STORE_ID
,S.FK_STORE_ID, C.STORE_ID AS CERTIFICATE_STORE_ID
,C.PERSON_ID
,CERTIFICATE_ID
FROM CERTIFICATE C
INNER JOIN STORE S ON C.STORE_ID = S.STORE_ID OR C.STORE_ID = S.FK_STORE_ID
WHERE
C.PERSON_ID = 100258
AND C.STORE_ID = 1464


This query retrieves:

STORE_ID | FK_STORE_ID | STORE_ID | PERSON_ID | CERTIFICATE_ID
---------|-------------|----------|-----------|----------------
336 | 1464 | 1464 | 100258 | 87083
1117 | 1464 | 1464 | 100258 | 87083
1464 | 340 | 1464 | 100258 | 87083


I expected to get this:

STORE_ID | FK_STORE_ID | CERTIFICATE_STORE_ID | PERSON_ID | CERTIFICATE_ID
---------|-------------|----------------------|-----------|----------------
336 | 1464 | 336 | 100258 | 87123
1464 | 340 | 1464 | 100258 | 87083


Is there any way to get this, without change the table structure?

Need your help, thanks!

Answer

As commented above, it seemed you needed the OR in the filter rather than on the join.

So, here is my comment in answer form...

SELECT 
    S.STORE_ID
    ,S.FK_STORE_ID, C.STORE_ID AS CERTIFICATE_STORE_ID
    ,C.PERSON_ID
    ,CERTIFICATE_ID 
FROM CERTIFICATE C 
     INNER JOIN STORE S ON C.STORE_ID = S.STORE_ID
WHERE 
    C.PERSON_ID = 100258    
    AND (C.STORE_ID = 1464 OR C.FS_STORE_ID = 1464)
Comments