i have problem with left outer join

Question

0.00/5 (No votes)

See more:

I want to found (with left outer join) customers that this month haven't any order. i wrote it but it not work properly because of previous month record.why this happent?, please guid me.

For simple
I have two tables:

Table1

+------+------+
| col1 | col2 |
+------+------+
|    1 | a    |
|    2 | b    |
|    3 | c    |
+------+------+

Table2

+------+------+------+
| col1 | col2 | col3 |
+------+------+------+
|    1 | x    |    0 |
|    2 | y    |    0 |
|    1 | x    |    1 |
+------+------+------+
     2   y          1

Record (2,'y',1), not inserted In Table2 and I want found it with Left Outer Join, like below.

SQL

SELECT * FROM table1 
WHERE col1 NOT IN (SELECT col1 FROM table2 
                   WHERE col3=1) AND col1 IN (SELECT col1 FROM table2);

+------+------+
| col1 | col2 |
+------+------+
|    2 | b    |
+------+------+

SQL

CREATE TABLE `table1` (
  `col1` int,
  `col2` varchar(1));

CREATE TABLE `table2` (
  `col1` int,
  `col2` varchar(1),
  `col3` int);

INSERT INTO table1 VALUES (1,'a'),(2,'b'),(3,'c');

INSERT INTO table2 VALUES (1,'x',0),(2,'y',0),(1,'x',1);

Best wishes.

Posted 21-May-14 9:10am

Member 10190382

Updated 22-May-14 5:46am

Maciej Los

v2

Add a Solution

Comments

Andrius Leonavicius 21-May-14 16:59pm

Hi,

Could you please explain this in more detail: "Record (2,'y',1), not inserted In Table2 and I want found it with Left Outer Join"?

Member 10190382 22-May-14 10:46am

Hi(i apologize for poor english.)
because this record not inserted, i want with outer join and check Null value found it.

this statement not important, you can don't any pay attention.

thanks

Andrius Leonavicius 22-May-14 11:22am

Please take a look at my answer (solution 2).

Peter Leow 21-May-14 20:49pm

are you using mysql or mssql, I saw these back ticks?

Member 10190382 22-May-14 10:47am

Mysql
(but not differ)

Peter Leow 22-May-14 20:29pm

Homework?

1 solution

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)

Andrius Leonavicius · Accepted Answer · 2014-05-22T05:21:00

Solution 2

I'm not sure if this is what you want, but try this:

SQL

SELECT t1.col1,
       t1.col2
FROM   table1 AS t1
       LEFT OUTER JOIN (SELECT col1
                        FROM   table2
                        WHERE  col3 = 1) AS t2
                    ON t2.col1 = t1.col1
       LEFT OUTER JOIN (SELECT col1
                        FROM   table2) AS t3
                    ON t3.col1 = t1.col1
WHERE  t2.col1 IS NULL
       AND t3.col1 IS NOT NULL

Please let me how it works for you.

Posted 22-May-14 5:21am

Andrius Leonavicius

Comments

Member 10190382 22-May-14 13:51pm

thanks, this is my answer.

At first i wrote below and give me wrong answer.

SELECT *
FROM table1 t1 LEFT JOIN table2 t2 ON t1.col1 = t2.col1
WHERE t2.col1 IS NULL AND col3=1;

but you get "AND col3=1" into
... JOIN (SELECT col1 FROM table2 WHERE col3 = 1) t2...

led to problem solved

if possible and Do not bother , Explain why my solution not work?

Andrius Leonavicius 22-May-14 16:22pm

You're welcome.

The problem was that you were trying to solve this with one LEFT JOIN. You need two LEFT JOINS with appropriate WHERE clauses. Two joins work here in a similar way like IN and NOT IN; EXISTS and NOT EXISTS.