sql subquery issue with inner join

Question

I've got these tables in my database:

Tourist - this is the first table

Tourist_ID - primary key
name...etc...

EXTRA_CHARGES

Extra_Charge_ID - primary key 
Extra_Charge_Description
Amount

Tourist_Extra_Charges

Tourist_Extra_Charge_ID
Extra_Charge_ID - foreign key
Tourist_ID - foreign key

So here is the example

I've one Tourist with Tourist_ID - 86 . This tourist with id 86 has extra charges with Extra_Charge_ID - 7 and and Extra_charge_ID - 11;

I try to make a query so I can take the name of the Tourist and all the charges in EXTRA_CHARGES table that doesn't belong to this tourist.

Here is the query that I try - but it doesn't return nothing.

SELECT
    Tourist.Name
    , EXTRA_CHARGES.Extra_Charge_Description
    , EXTRA_CHARGES.Amount 
FROM 
    Tourist 
    INNER JOIN TOURIST_EXTRA_CHARGES 
        ON Tourist.Tourist_ID = TOURIST_EXTRA_CHARGES.Tourist_ID 
    INNER JOIN EXTRA_CHARGES 
        ON TOURIST_EXTRA_CHARGES.Extra_Charge_ID = EXTRA_CHARGES.Extra_Charge_ID 
WHERE
    Tourist.Tourist_ID= 86 
    and EXTRA_CHARGES.Extra_Charge_ID NOT IN
    (   SELECT Extra_Charge_ID 
        FROM TOURIST_EXTRA_CHARGES te
        WHERE te.Tourist_ID = 86
    )

I of course can get only the charges with this query

SELECT * FROM EXTRA_CHARGES e
WHERE e.Extra_Charge_ID NOT IN
 (SELECT Extra_Charge_ID from TOURIST_EXTRA_CHARGES te

  WHERE te.Tourist_ID = 86
 )

but I can't find a way to get the name of this tourist

can you show an output of your query to give as more understanding — zxc, Aug 16 '13 at 09:40

score 1 · Answer 1 · answered Aug 16 '13 at 09:40

1

You may include the tourist name (for Tourist_ID=86) as a subquery in the select statement:

SELECT (SELECT Tourist.Name FROM Tourist WHERE Tourist_ID=86) TouristName, e.*
FROM   EXTRA_CHARGES e
WHERE  e.Extra_Charge_ID NOT IN
       (SELECT Extra_Charge_ID 
        FROM   TOURIST_EXTRA_CHARGES te
        WHERE  te.Tourist_ID = 86
       )

answered Aug 16 '13 at 09:40

Khadim Ali

2,548
3
33
61

Hey it works, but when I get the name on the column - it's written "no column name".Do you know how to get it – Tania Marinova Aug 16 '13 at 10:35
1

Probably you have omitted the column name. Write like `(SELECT ....) AS TouristName` – Khadim Ali Aug 16 '13 at 10:50
Yes I was so tired that I really omitted it :) Thans – Tania Marinova Aug 19 '13 at 12:49

score 1 · Answer 2 · answered Aug 16 '13 at 09:47

1

You may try this it's more efficient:

SELECT t.Name 
FROM Tourist t JOIN 
 (SELECT * FROM EXTRA_CHARGES e 
 JOIN TOURIST_EXTRA_CHARGE tec ON e.Extra_Charge_ID = tec.Extra_Charge_ID AND tec.TOURIST_EXTRA_CHARGES != 86)
WHERE t.Tourist_ID = 86

btw. you do not need Tourist_Extra_Charge_ID column in Tourist_Extra_Charges

answered Aug 16 '13 at 09:47

MiGro

1,471
10
8

I thought the OP also require EXTRA_CHARGES columns with the result. – Khadim Ali Aug 16 '13 at 09:50

score 1 · Accepted Answer · edited May 23 '17 at 12:11

You can use two options, both are pretty much the same, but one may perform better than the other depending on your DBMS.

The principal of both is the same, get a cross join of tourist and extra charges so you have all extra charges for all tourists, then use either NOT EXISTS or LEFT JOIN/IS NULL to eliminate all extra charges that the tourist has:

SELECT  Tourist.Name,
        EXTRA_CHARGES.Extra_Charge_Description,
        EXTRA_CHARGES.Amount 
FROM    Tourist
        CROSS JOIN EXTRA_CHARGES
WHERE   Tourist.Tourist_ID= 86 
AND     NOT EXISTS
        (   SELECT  1
            FROM    TOURIST_EXTRA_CHARGES
            WHERE   TOURIST_EXTRA_CHARGES.Tourist_ID = Tourist.Tourist_ID
            AND     TOURIST_EXTRA_CHARGES.Extra_Charge_ID = EXTRA_CHARGES.Extra_Charge_ID
        );


SELECT  Tourist.Name,
        EXTRA_CHARGES.Extra_Charge_Description,
        EXTRA_CHARGES.Amount 
FROM    Tourist
        CROSS JOIN EXTRA_CHARGES
        LEFT JOIN TOURIST_EXTRA_CHARGES
            ON TOURIST_EXTRA_CHARGES.Tourist_ID = Tourist.Tourist_ID
            AND TOURIST_EXTRA_CHARGES.Extra_Charge_ID = EXTRA_CHARGES.Extra_Charge_ID
WHERE   Tourist.Tourist_ID = 86 
AND     TOURIST_EXTRA_CHARGES.Tourist_Extra_Charge_ID IS NULL;

EDIT

Since the two criteria you are applying are logically different, you need to use two queries to get it. The first is as before, the extra charges that a tourist does not have, and the second is for tourists who have all extra charges

SELECT  Tourist.Name,
        EXTRA_CHARGES.Extra_Charge_Description,
        EXTRA_CHARGES.Amount 
FROM    Tourist
        CROSS JOIN EXTRA_CHARGES
        LEFT JOIN TOURIST_EXTRA_CHARGES
            ON TOURIST_EXTRA_CHARGES.Tourist_ID = Tourist.Tourist_ID
            AND TOURIST_EXTRA_CHARGES.Extra_Charge_ID = EXTRA_CHARGES.Extra_Charge_ID
WHERE   Tourist.Tourist_ID = 1 
AND     TOURIST_EXTRA_CHARGES.Tourist_Extra_Charge_ID IS NULL
UNION ALL
SELECT  Tourist.Name,
        NULL,
        NULL
FROM    Tourist
        CROSS JOIN EXTRA_CHARGES
        LEFT JOIN TOURIST_EXTRA_CHARGES
            ON TOURIST_EXTRA_CHARGES.Tourist_ID = Tourist.Tourist_ID
            AND TOURIST_EXTRA_CHARGES.Extra_Charge_ID = EXTRA_CHARGES.Extra_Charge_ID
WHERE   Tourist.Tourist_ID = 1 
GROUP BY Tourist.Name
HAVING COUNT(*) = COUNT(TOURIST_EXTRA_CHARGES.Tourist_Extra_Charge_ID);

Example on SQL Fiddle

how whathappens in the first case when the tourist has all of the charges so - query will return no row. but I need the name - even if they have no extra_charges — Tania Marinova, Oct 11 '13 at 17:08

KekuSemau · Answer 4 · 2013-08-16T09:55:51.903

Just use INNER JOIN ... ON .. <> ...

SELECT
    Tourist.Name
    , EXTRA_CHARGES.Extra_Charge_Description
    , EXTRA_CHARGES.Amout 
FROM 
    Tourist 
    INNER JOIN TOURIST_EXTRA_CHARGES 
        ON Tourist.Tourist_ID <> TOURIST_EXTRA_CHARGES.Tourist_ID 
    INNER JOIN EXTRA_CHARGES 
        ON TOURIST_EXTRA_CHARGES.Extra_Charge_ID = EXTRA_CHARGES.Extra_Charge_ID 
WHERE
    Tourist.Tourist_ID= 86

See here: http://sqlfiddle.com/#!2/28665/2

sql subquery issue with inner join

4 Answers4