CODEWITHSUNDEEP
phpsqljoin
fightstarr20
fightstarr20

Reputation: 12628

Join SQL tables by id field

I have the following sql query on a mysql db which returns all results from wp_client where the form_id is 46 and the date_created is within the last 7 days...

SELECT *
FROM
    wp_client WHERE form_id = '46'
and
    cast(date_created as date) >= current_date - interval '7' day

I have another table wp_client_detail that stores more information that I would like to be included in the results. The field client_id in wp_client_detail matches up with the field id in wp_client.

I assume I need to use a JOIN command but can't work out how, I have tried...

    INNER JOIN
            wp_client_detail
        ON
            wp_client.id=wp_CLIENT_detail.lead_id;

But it's not working, can anyone help?

Upvotes: 0

Views: 30

Answers (1)

crthompson
crthompson

Reputation: 15875

Nothing wrong with your syntax, just make sure you have it all in the right order:

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

If that syntax doesnt work, then I would suggest that you have problems with your data.

This can also be rendered with an IN

select
  *
from
  wp_client c
where
  form_id = '46'
  and cast(date_created as date) >= current_date - interval '7' day
  id in (select lead_id from wp_CLIENT_detail)

Or EXISTS:

select
  *
from
  wp_client c
where
  form_id = '46'
  and cast(date_created as date) >= current_date - interval '7' day
  id exists (select 1 from wp_CLIENT_detail d where c.id = d.lead_id)

Upvotes: 1

Related Questions