PHP Query Syntax

Can anyone shed some light on my error message?  The following is the query.  Everything work, but the line that causes the error and I'm not sure what is wrong with it:
	$check = "SELECT DISTINCT
				 p1.post_id AS post_id,
				 oi1.order_item_id AS order_item_id,
				 p3.meta_value AS address_2,
				 om1.meta_value AS first_name,
				 om2.meta_value AS last_name,
				 om3.meta_value AS serial_number,
				 om4.meta_value AS total_amount,
				 om5.meta_value AS starting_date,
				 om10.meta_value AS status
				 FROM wp_postmeta p1, wp_woocommerce_order_itemmeta oi1
					JOIN wp_postmeta p3 ON (p3.post_id = p1.post_id AND p3.meta_key = '_billing_address_2')
				 JOIN wp_woocommerce_order_itemmeta om1 ON (om1.order_item_id = oi1.order_item_id AND om1.meta_key = 'Policy Holder First Name - Policy Holder First Name')
				 JOIN wp_woocommerce_order_itemmeta om2 ON (om2.order_item_id = oi1.order_item_id AND om2.meta_key = 'Policy Holder Last Name - Policy Holder Last Name')
				 JOIN wp_woocommerce_order_itemmeta om3 ON (om3.order_item_id = oi1.order_item_id AND om3.meta_key = 'Bicycle Information - Frame Serial Number')
				 JOIN wp_woocommerce_order_itemmeta om4 ON (om4.order_item_id = oi1.order_item_id AND om4.meta_key = '_line_total')
				 JOIN wp_woocommerce_order_itemmeta om5 ON (om5.order_item_id = oi1.order_item_id AND om5.meta_key = '_subscription_start_date')	
			JOIN wp_woocommerce_order_itemmeta om10 ON (om10.order_item_id = oi1.order_item_id AND om10.meta_key = '_subscription_status')
				 WHERE p1.meta_value = :user_id AND om10.meta_value = 'active'";
				 

Open in new window

ERROR Message:   Array ( [0] => 42S22 [1] => 1054 [2] => Unknown column 'p1.post_id' in 'on clause' ) 1
LVL 7
rgranlundAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

PortletPaulfreelancerCommented:
that field (post_id) does not exist in that table (p1)  (p1 is an alias for wp_postmeta)

it is complaining about this join:

     JOIN wp_postmeta p3
                 ON (p3.post_id = p1.post_id AND p3.meta_key = '_billing_address_2')
0
PortletPaulfreelancerCommented:
(by omitting an explicit join you imply a cross join)

Is the CROSS JOIN between wp_postmeta  and wp_woocommerce_order_itemmeta deliberate?
SELECT DISTINCT
      p1.post_id        AS post_id
    , oi1.order_item_id AS order_item_id
    , p3.meta_value     AS address_2
    , om1.meta_value    AS first_name
    , om2.meta_value    AS last_name
    , om3.meta_value    AS serial_number
    , om4.meta_value    AS total_amount
    , om5.meta_value    AS starting_date
    , om10.meta_value   AS status
FROM wp_postmeta p1
     CROSS JOIN wp_woocommerce_order_itemmeta oi1
     JOIN wp_postmeta p3
                 ON (p3.post_id = p1.post_id AND p3.meta_key = '_billing_address_2')
     JOIN wp_woocommerce_order_itemmeta om1
                 ON (om1.order_item_id = oi1.order_item_id AND om1.meta_key = 'Policy Holder First Name - Policy Holder First Name')
     JOIN wp_woocommerce_order_itemmeta om2
                 ON (om2.order_item_id = oi1.order_item_id AND om2.meta_key = 'Policy Holder Last Name - Policy Holder Last Name')
     JOIN wp_woocommerce_order_itemmeta om3
                 ON (om3.order_item_id = oi1.order_item_id AND om3.meta_key = 'Bicycle Information - Frame Serial Number')
     JOIN wp_woocommerce_order_itemmeta om4
                 ON (om4.order_item_id = oi1.order_item_id AND om4.meta_key = '_line_total')
     JOIN wp_woocommerce_order_itemmeta om5
                 ON (om5.order_item_id = oi1.order_item_id AND om5.meta_key = '_subscription_start_date')
     JOIN wp_woocommerce_order_itemmeta om10
                 ON (om10.order_item_id = oi1.order_item_id AND om10.meta_key = '_subscription_status')
WHERE p1.meta_value = :user_id
      AND om10.meta_value = 'active'

Open in new window

0
Vimal DMSenior Software EngineerCommented:
Hi,

A simple solution would be adding the below line,
JOIN wp_postmeta p3 ON (p3.post_id = p1.post_id AND p3.meta_key = '_billing_address_2')

very next to

FROM wp_postmeta p1


Like this:


FROM wp_postmeta p1
   JOIN wp_postmeta p3 ON (p3.post_id = p1.post_id AND p3.meta_key = '_billing_address_2')
wp_woocommerce_order_itemmeta oi1
JOIN wp_woocommerce_order_itemmeta om1 ON (om1.order_item_id = oi1.order_item_id AND om1.meta_key = 'Policy Holder First Name - Policy Holder First Name')
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.