Join Same Table Results

Resolved moultrex
(@moultrex)

6 years, 5 months ago

Hello. I tried your plugin. Although it seems promising my custom query doesn’t work.

I try to join the same table with different alias names to show me the results i want.

In SQLyog software i have and phpmyadmin the query brings the data just fine.

One of the querries i use is this:

SELECT stock.meta_key, stock.meta_value, sync.meta_key, sync.meta_value FROM wp_postmeta AS stock
INNER JOIN wp_postmeta AS sync
ON stock.post_id = sync.post_id
WHERE stock.meta_key = ‘_stock’
AND sync.meta_key = ‘_sku’
AND LENGTH(sync.meta_value) = ’12’

Does your plugin have this functionality?

Thank you.

Viewing 7 replies - 1 through 7 (of 7 total)

Plugin Author Ian Sadovy
(@iansadovy)

6 years, 5 months ago

Hello @moultrex,

Thank you for reporting.
Do you see the Invalid query. Please check the syntax and try again. error?
I have reproduced this error with complex query, so I will fix it and let you know.
Hope it works for you.

Regards,
Ian

Thread Starter moultrex
(@moultrex)

6 years, 5 months ago

Yeah it throws that error.

Thanks for the feedback i hope you’ll get it fixed.

In theory it should return 4 columns with data from the same table joined with diffrent alias.

Plugin Author Ian Sadovy
(@iansadovy)

6 years, 5 months ago

Hello @moultrex,

Thank you for giving me some time.
I just have published an update that should fix the issue with MySQL query.
Also, you may use single quotes ' instead of ‘ in the WHERE statement.
Please try an update and let me know if it works.

Regards,
Ian

Thread Starter moultrex
(@moultrex)

6 years, 4 months ago

Hello. Thanks for the update.

The thing is now it half works.

I want to join the same table and bring the matched alias columns but know it brings only two columns of total four that should bring the query.

I attach you a screenshot to know what i mean.

What SQL actually brings (i use SQLyog software)

https://prnt.sc/hfrn79

What WPTables brings:

http://prntscr.com/hfrnsq

Maybe it’s wordpress limitation? Maybe you can’t join the same table with different alias there?

Nonetheless thank you.
Plugin Author Ian Sadovy
(@iansadovy)

6 years, 4 months ago
Hello @moultrex,

Thanks for the details.
I think it works like that because columns have the same names.
Let’s try aliases for column names:
```
SELECT stock.meta_key, stock.meta_value, 
sync.meta_key AS meta_key_2, 
sync.meta_value AS meta_value_2
FROM wp_postmeta AS stock
INNER JOIN wp_postmeta AS sync
ON stock.post_id = sync.post_id
WHERE stock.meta_key = '_stock'
AND sync.meta_key = '_sku'
AND LENGTH(sync.meta_value) = '12'
```
Please let me know if it works.

Regards,
Ian
Thread Starter moultrex
(@moultrex)

6 years, 4 months ago

Whops..my bad.

Got used with SQLyog and didn’t think that.

Donated.

Keep up the good work.

Thanks!

Plugin Author Ian Sadovy
(@iansadovy)

6 years, 4 months ago

Glad to hear it works. Thanks!

Viewing 7 replies - 1 through 7 (of 7 total)

The topic ‘Join Same Table Results’ is closed to new replies.

Tags