CODEWITHSUNDEEP
mysqlsqldatabase
Malav Shah
Malav Shah

Reputation: 143

combining 2 select query in 1 mysql

I am trying to figure out 1 query for a long time. I am new to mysql and query statements. I have below 2 queries which I would like to pass into 1 statement so that i can get the desired output.

part 1

select custid, fname from customertbl where createdate < 01-01-2011

part 2

select custid, orddate from ordertbl where orddate < 01-01-2011

in general, what i need is the 1st query gives me the list of customer who are created before 01-01-2011.

and 2nd query gives list of who has not made any order after 01-01-2011 .

my desired output is list of customer whose createdate is before 01-01-2011 and have not made any order after 01-01-2011.

I would really appreciate if you could help me out on this.

forgot to mention that custid is same in both tables.

thanks.

--EDIT : to make a little more clear, many customers whose createdate is before 1-1-2011 are still active and i just want the list of customer who are inactive after 01-01-2011

Upvotes: 0

Views: 101

Answers (5)

Saeed
Saeed

Reputation: 7370

Use this:

select custid, fname 
from customertbl 
where createdate < 01-01-2011 and custid not in (
         select custid
         from ordertbl 
         where orddate > 01-01-2011
     )

Or you can use this which is better in performance:

select custid, fname 
from customertbl 
where createdate < 01-01-2011 and not exist (
         select custid
         from ordertbl 
         where orddate > 01-01-2011
         And customertbl.custid=ordertbl.custid
     )

Upvotes: 0

Kuzgun
Kuzgun

Reputation: 4737

SELECT c.custid, c.fname FROM customertbl c 
LEFT JOIN ordertbl o ONc.custid=o.custid 
WHERE createdate < 01-01-2011 AND orddate < 01-01-2011

EDIT: For customers who do not have orders further than that date:

 SELECT c.custid, c.fname FROM customertbl c 
WHERE createdate < 01-01-2011 
AND (SELECT Count(*) FROM ordertbl WHERE custid=c.custid AND orddate>01-01-2011)=0

Upvotes: 0

bjackfly
bjackfly

Reputation: 3336

SELECT usr.custid, usr.fname
    FROM customertbl usr
    WHERE usr.createdate < '01-01-2011' 
    AND NOT EXISTS ( select 1 from orderdate where custid = usr.custid and orddate > '01-01-2011' )

I just read your edit and it seems you want to know customers that were created prior to 01-01-2011 and haven't placed any order after that date. This simplifies things and doesn't require a join unless you need to see their last order date

Upvotes: 1

Siluni
Siluni

Reputation: 11

Try this

SELECT usr.custid, usr.fname, od.orddate
    FROM customertbl usr
    JOIN ordertbl od ON od.custid = usr.custid
    WHERE usr.createdate < '01-01-2011' AND od.orddate < '01-01-2011'

Upvotes: 1

Chibuzo
Chibuzo

Reputation: 6117

Use JOIN

SELECT c.custid, c.fname, o.orddate from customertbl c
JOIN ordtbl o ON c.custid = o.custid
WHERE c.orddate < '01-01-2011' AND c.createdate < '01-01-2011'

Upvotes: 0

Related Questions