CODEWITHSUNDEEP
sql
fightstarr20
fightstarr20

Reputation: 12568

SQL Inner Join Not Returning Any Results

I have the following tables in a WordPress SQL database....

wp_rg_lead

id     form_id    post_id        date_created         
--------------------------------------------------
3    |    5     |   786   |   2014-11-25 14:00:20
9    |    6     |   98    |   2014-11-23 22:52:40
5    |    7     |   87    |   2014-11-22 14:32:46
6    |    1     |   88    |   2014-11-22 23:13:39
78   |    33    |   689   |   2014-11-25 22:52:40
34   |    51    |   77    |   2014-11-24 14:00:20

wp_rg_lead_detail

id     lead_id    form_id    field number
--------------------------------------------------
3    |    5     |   786   |       33
9    |    6     |   98    |       4
5    |    7     |   87    |       21
6    |    1     |   88    |       5
78   |    33    |   689   |       55
34   |    51    |   77    |       1

I am attempting to do an INNER JOIN like this...

SELECT *
FROM
    wp_rg_lead
    INNER JOIN wp_rg_lead_detail ON
            wp_rg_lead.id=wp_rg_lead_detail.lead_id
WHERE form_id = '46'
and  cast(date_created as date) >= current_date - interval '7' day;

But it is not returning any results for some reason, anyone any ideas?

Upvotes: 0

Views: 128

Answers (3)

Christopher Bonitz
Christopher Bonitz

Reputation: 856

Without knowing your data it becomes more difficult to troubleshoot, i would recommend that your break your query up and troubleshoot every part.

simply by removing (comment out) every part, start with the where, what happens if you comment out the date filter (use --)

--and  cast(date_created as date) >= current_date - interval '7' day;

No data? then try to do a select, but only on wp_rg_lead where form_id = 46, also are you sure the ID is a character datatype? it properly works fine, however it is a better practice to use proper data types when comparing, please use the number without ' in the condition.

what are the result now?

if there is an result, try the last part, 

select * from wp_rg_lead_detail where id = 46

this does not answer your question, but it might lead you into finding the missing link.

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269503

Presuming that such a row actually exists, I would suggest that you use a left join, as well as table aliases:

SELECT *
FROM wp_rg_lead l LEFT JOIN
     wp_rg_lead_detail ld
     ON l.id = ld.lead_id AND ld.form_id = '46' 
WHERE cast(l.date_created as date) >= current_date - interval '7' day;

Another possibility is that the query is returning an error. As written in the question, form_id is ambiguous and the query will not return any results. Are you checking for appropriate errors?

Upvotes: 1

ashkufaraz
ashkufaraz

Reputation: 5297

why JOIN wp_client_detail ?

instead of wp_client_detail use wp_rg_lead_detail

SELECT *
FROM
    wp_rg_lead
    INNER JOIN wp_rg_lead_detail ON
            wp_rg_lead.id=wp_rg_lead_detail.lead_id
WHERE form_id = '46'
and  cast(date_created as date) >= current_date - interval '7' day;

Upvotes: 2

Related Questions