How do I remove results from the first query, if they exist in a second query?

Question

0.00/5 (No votes)

See more:

How do I remove results from the first query, if they exist in a second query?

I've looked over previous stackoverflow threads and still can't get it to work.

My MySQL queries so far are below:

SQL

SELECT roomL.CityCode, roomL.TableCode
FROM (SELECT roomL.CityCode, roomL.TableCode
      FROM roomL
      WHERE roomL.StatusEnum = 1
      AND roomL.TableCode NOT IN (select stock.TableCode from stock)
      AND roomL.TimeSeen BETWEEN 10/10/2014 AND Now())

WHERE NOT EXISTS (SELECT roomL.CityCode , roomL.TableCode
                  FROM roomL
                  WHERE roomL.StatusEnum = 1
                  AND roomL.TableCode NOT IN (select stock.TableCode from stock)
                  AND roomL.TimeSeen BETWEEN DATE_ADD( 17/10/2014 ,INTERVAL 1 DAY) AND Now())

Currently I'm getting the error:

SQL Error (1248): Every derived table must have its own alias

Posted 21-Oct-14 23:43pm

Empir

Updated 21-Oct-14 23:49pm

v4

Add a Solution

2 solutions

Solution 1

try this hope it will help you

SQL

SELECT roomL.CityCode, roomL.TableCode
    FROM (SELECT roomL.CityCode, roomL.TableCode
          FROM roomL
          WHERE roomL.StatusEnum = 1
          AND roomL.TableCode NOT IN (select stock.TableCode from stock)
          AND roomL.TimeSeen BETWEEN 10/10/2014 AND Now()) as x

    WHERE roomL.CityCode+'#'+roomL.TableCode Not in
    (SELECT roomL.CityCode+'#'+roomL.TableCode
                      FROM roomL
                      WHERE roomL.StatusEnum = 1
                      AND roomL.TableCode NOT IN (select stock.TableCode from stock)
                      AND roomL.TimeSeen BETWEEN DATE_ADD( 17/10/2014 ,INTERVAL 1 DAY) AND Now())

Posted 22-Oct-14 0:19am

Salman622

Comments

Empir 22-Oct-14 6:27am

I then get the error :
SQL Error (1054): Unknown column 'roomL.CityCode' in 'field list'

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Blesson Mathew · Accepted Answer · 2014-10-22T00:26:00

SQL

SELECT RL.CityCode, RL.TableCode
    FROM (SELECT roomL.CityCode, roomL.TableCode
          FROM roomL
          WHERE roomL.StatusEnum = 1
          AND roomL.TableCode NOT IN (select stock.TableCode from stock)
          AND roomL.TimeSeen BETWEEN 10/10/2014 AND Now()) RL

    WHERE RL.CityCode Not in
    (SELECT roomL.CityCode 
                      FROM roomL
                      WHERE roomL.StatusEnum = 1
                      AND roomL.TableCode NOT IN (select stock.TableCode from stock)
                      AND roomL.TimeSeen BETWEEN DATE_ADD( 17/10/2014 ,INTERVAL 1 DAY) AND Now())