CODEWITHSUNDEEP
sql
Pradeep
Pradeep

Reputation: 637

SQL JOIN using COUNT

I want to use sql JOIN with COUNT in a way that using the COUNT as the condition for the JOIN.

e.g.

SELECT * FROM tbl1
  INNER JOIN (SELECT * FROM tbl2) t2
  ON (SELECT COUNT(*) FROM tbl1) > 0

Is this possible or can someone please tell me another way?

PS- The real question I'm having is that I have two tables A and B and I need to select id from A where the date of creation is greater than some value. At the same time I want to make sure that table B does not have that id in table A. This works fine but when I have no data in table B, the query gives no result. 

SELECT a.user_id from (SELECT * FROM A WHERE DATEDIFF(event_date,'certain_value') >=another_value) a INNER JOIN B b ON a.user_id != b.user_id

I tried to use count like this but failed

SELECT a.user_id from (SELECT * FROM A WHERE DATEDIFF(event_date,'certain_value') >=another_value) a INNER JOIN B b ON count(B.user_id) = 0 OR a.user_id != b.user_id

Upvotes: 0

Views: 301

Answers (3)

Stefan
Stefan

Reputation: 1

Try this:

SELECT A.user_id
FROM A
WHERE DATEDIFF(A.event_date,'certain_value') >= another_value)
  AND NOT EXISTS (SELECT B.user_id FROM B Where A.user_id = B.user_id)

Upvotes: 0

a1ex07
a1ex07

Reputation: 37354

The way your query is written, condition in ON is doing nothing and equivalent to ON (1=1) (if there is no records in tbl1, select * from tbl1 inner join tbl2 on [any condition] will always return empty resultset).

From your updated question I think you need:

SELECT
    a.user_id
FROM
    (SELECT *
     FROM   A
     WHERE  DATEDIFF(A.event_date, 'certain_value') >= another_value) AS a
WHERE
    NOT EXISTS (SELECT 1
                FROM   b
                WHERE  b.user_id = a.user_id)

Upvotes: 1

HasaniH
HasaniH

Reputation: 8392

"Inner joins return rows only when there is at least one row from both tables that matches the join condition." What you want is is an OUTER JOIN so that you get results even when there is no data in B. See: http://msdn.microsoft.com/en-us/library/aa213228%28v=SQL.80%29.aspx

If I understand your question correctly though you may want to use:

WHERE a.user_id NOT IN (SELECT user_id FROM B)

for your condition rather than doing a join on the tables since you're not selecting any data from B at all

Upvotes: 0

Related Questions