Issues with query report from multiple tables

Hi, 

I'm trying to create a query report for use with multiple tables and can't seem to get it to work. I'm not extremely well versed in MySQL, and might be able to figure it out but there isn't an error console when creating a query report, it just will say "Loading Report" and never load when something is broken.

Brief background : For sale of parts, one part can fit in many engines. The different engines that a part is compatible with are stored in the website item group table, so that a customer can select their engine and see available parts on the ERPNext website. 

I need a query report that allows me to type in a certain engine, and see parts that are compatible with that engine (and further filter in top bar by brand, part number, etc), along with current stock, and pricing. I can't do a script report as I don't know how, and don't have access to backend. 

Here's what I've gotten done so far: 

SELECT
   tabWebsite Item Group.item_group as "Engine:120",
   tabItem.item_code as "Part Number:Link/Item:150",
   tabItem.brand as "Brand:120",
   tabItem.item_group as "Item Group:Link/Item Group:120",
   tabItem Price.price_list as "Price List:120",
   tabItem Price.ref_rate as "Rate:Currency:120",
   tabItem.actual_qty as "Actual:80",
   tabItem.projected_qty as "Projected:80"
FROM
   tabWebsite Item Group, tabItem, tabItem Price
WHERE
   tabItem.is_purchase_item = "Yes"


It's notable that this doesn't work both with and without single quotes around the tables - but when I do a test-table, with SELECT 'tabItem'.item_code (note quotes) FROM 'tabItem', it does not work. When the quotes are removed, it works. So I don't know how to handle tables with a space in their name, as if I try to make a table with only price_list from tabItem Price, it does not work. 

I've also experimented with using a UNION command but have failed there as well. 

Thank you for your help and time,
Alec Ruiz-Ramon



Note:

 

If you are posting an issue,

  1. We should be able to replicate it at our end. So please give us as much information as you can. Please see it from the point of view of the person receiving the communication.
  2. Paste your code at http://pastebin.com or http://gist.github.com and send only the URL via email
  3. For sending images, use http://imgur.com or other similar services. Do not send images as attachments. Links are good. Same goes for any file you are going to send.

     

    End of Note



    You received this message because you are subscribed to the Google Groups "ERPNext Developer Forum" group.

    To unsubscribe from this group and stop receiving emails from it, send an email to erpnext-developer-forum+un…@googlegroups.com.

    To view this discussion on the web visit https://groups.google.com/d/msgid/erpnext-developer-forum/ccd62967-6050-4046-b989-321ad701b8b3%40googlegroups.com.

    For more options, visit https://groups.google.com/d/optout.
1. Lookup SQL help on how to join tables in queries - you have not put enough conditions and this is off topic for this forum.
2. Check how other reports are written

for table names with spaces, use `tabItem Price`



@rushabh_mehta

On 28-May-2014, at 12:47 am, Alec Ruiz-Ramon <al...@gmail.com> wrote:

Hi, 

I'm trying to create a query report for use with multiple tables and can't seem to get it to work. I'm not extremely well versed in MySQL, and might be able to figure it out but there isn't an error console when creating a query report, it just will say "Loading Report" and never load when something is broken.

Brief background : For sale of parts, one part can fit in many engines. The different engines that a part is compatible with are stored in the website item group table, so that a customer can select their engine and see available parts on the ERPNext website. 

I need a query report that allows me to type in a certain engine, and see parts that are compatible with that engine (and further filter in top bar by brand, part number, etc), along with current stock, and pricing. I can't do a script report as I don't know how, and don't have access to backend. 

Here's what I've gotten done so far: 

SELECT
   tabWebsite Item Group.item_group as "Engine:120",
   tabItem.item_code as "Part Number:Link/Item:150",
   tabItem.brand as "Brand:120",
   tabItem.item_group as "Item Group:Link/Item Group:120",
   tabItem Price.price_list as "Price List:120",
   tabItem Price.ref_rate as "Rate:Currency:120",
   tabItem.actual_qty as "Actual:80",
   tabItem.projected_qty as "Projected:80"
FROM
   tabWebsite Item Group, tabItem, tabItem Price
WHERE
   tabItem.is_purchase_item = "Yes"


It's notable that this doesn't work both with and without single quotes around the tables - but when I do a test-table, with SELECT 'tabItem'.item_code (note quotes) FROM 'tabItem', it does not work. When the quotes are removed, it works. So I don't know how to handle tables with a space in their name, as if I try to make a table with only price_list from tabItem Price, it does not work. 

I've also experimented with using a UNION command but have failed there as well. 

Thank you for your help and time,
Alec Ruiz-Ramon




Note:

 

If you are posting an issue,

  1. We should be able to replicate it at our end. So please give us as much information as you can. Please see it from the point of view of the person receiving the communication.
  2. Paste your code at http://pastebin.com or http://gist.github.com and send only the URL via email
  3. For sending images, use http://imgur.com or other similar services. Do not send images as attachments. Links are good. Same goes for any file you are going to send.

     

    End of Note



    You received this message because you are subscribed to the Google Groups “ERPNext Developer Forum” group.

    To unsubscribe from this group and stop receiving emails from it, send an email to erpnext-developer-forum+un…@googlegroups.com.

    To view this discussion on the web visit https://groups.google.com/d/msgid/erpnext-developer-forum/ccd62967-6050-4046-b989-321ad701b8b3%40googlegroups.com.

    For more options, visit https://groups.google.com/d/optout.



Note:

 

If you are posting an issue,

  1. We should be able to replicate it at our end. So please give us as much information as you can. Please see it from the point of view of the person receiving the communication.
  2. Paste your code at http://pastebin.com or http://gist.github.com and send only the URL via email
  3. For sending images, use http://imgur.com or other similar services. Do not send images as attachments. Links are good. Same goes for any file you are going to send.

     

    End of Note



    You received this message because you are subscribed to the Google Groups "ERPNext Developer Forum" group.

    To unsubscribe from this group and stop receiving emails from it, send an email to erpnext-developer-forum+un…@googlegroups.com.

    To view this discussion on the web visit https://groups.google.com/d/msgid/erpnext-developer-forum/E30396A3-3286-4E36-9925-2C921E344435%40gmail.com.

    For more options, visit https://groups.google.com/d/optout.
You have to use tilde and not quotes to enclose table names and field names separately. 

For example
`tabSales Order`.`customer`

Sent from my phone

On 28-May-2014, at 8:08, Rushabh Mehta <rm...@gmail.com> wrote:

1. Lookup SQL help on how to join tables in queries - you have not put enough conditions and this is off topic for this forum.
2. Check how other reports are written

for table names with spaces, use `tabItem Price`



@rushabh_mehta

On 28-May-2014, at 12:47 am, Alec Ruiz-Ramon <al...@gmail.com> wrote:

Hi, 

I'm trying to create a query report for use with multiple tables and can't seem to get it to work. I'm not extremely well versed in MySQL, and might be able to figure it out but there isn't an error console when creating a query report, it just will say "Loading Report" and never load when something is broken.

Brief background : For sale of parts, one part can fit in many engines. The different engines that a part is compatible with are stored in the website item group table, so that a customer can select their engine and see available parts on the ERPNext website. 

I need a query report that allows me to type in a certain engine, and see parts that are compatible with that engine (and further filter in top bar by brand, part number, etc), along with current stock, and pricing. I can't do a script report as I don't know how, and don't have access to backend. 

Here's what I've gotten done so far: 

SELECT
   tabWebsite Item Group.item_group as "Engine:120",
   tabItem.item_code as "Part Number:Link/Item:150",
   tabItem.brand as "Brand:120",
   tabItem.item_group as "Item Group:Link/Item Group:120",
   tabItem Price.price_list as "Price List:120",
   tabItem Price.ref_rate as "Rate:Currency:120",
   tabItem.actual_qty as "Actual:80",
   tabItem.projected_qty as "Projected:80"
FROM
   tabWebsite Item Group, tabItem, tabItem Price
WHERE
   tabItem.is_purchase_item = "Yes"


It's notable that this doesn't work both with and without single quotes around the tables - but when I do a test-table, with SELECT 'tabItem'.item_code (note quotes) FROM 'tabItem', it does not work. When the quotes are removed, it works. So I don't know how to handle tables with a space in their name, as if I try to make a table with only price_list from tabItem Price, it does not work. 

I've also experimented with using a UNION command but have failed there as well. 

Thank you for your help and time,
Alec Ruiz-Ramon




Note:

 

If you are posting an issue,

  1. We should be able to replicate it at our end. So please give us as much information as you can. Please see it from the point of view of the person receiving the communication.
  2. Paste your code at http://pastebin.com or http://gist.github.com and send only the URL via email
  3. For sending images, use http://imgur.com or other similar services. Do not send images as attachments. Links are good. Same goes for any file you are going to send.

     

    End of Note



    You received this message because you are subscribed to the Google Groups “ERPNext Developer Forum” group.

    To unsubscribe from this group and stop receiving emails from it, send an email to erpnext-developer-forum+un…@googlegroups.com.

    To view this discussion on the web visit https://groups.google.com/d/msgid/erpnext-developer-forum/ccd62967-6050-4046-b989-321ad701b8b3%40googlegroups.com.

    For more options, visit https://groups.google.com/d/optout.



Note:

 

If you are posting an issue,

  1. We should be able to replicate it at our end. So please give us as much information as you can. Please see it from the point of view of the person receiving the communication.
  2. Paste your code at http://pastebin.com or http://gist.github.com and send only the URL via email
  3. For sending images, use http://imgur.com or other similar services. Do not send images as attachments. Links are good. Same goes for any file you are going to send.

     

    End of Note



    You received this message because you are subscribed to the Google Groups “ERPNext Developer Forum” group.

    To unsubscribe from this group and stop receiving emails from it, send an email to erpnext-developer-forum+un…@googlegroups.com.

    To view this discussion on the web visit https://groups.google.com/d/msgid/erpnext-developer-forum/E30396A3-3286-4E36-9925-2C921E344435%40gmail.com.

    For more options, visit https://groups.google.com/d/optout.



Note:

 

If you are posting an issue,

  1. We should be able to replicate it at our end. So please give us as much information as you can. Please see it from the point of view of the person receiving the communication.
  2. Paste your code at http://pastebin.com or http://gist.github.com and send only the URL via email
  3. For sending images, use http://imgur.com or other similar services. Do not send images as attachments. Links are good. Same goes for any file you are going to send.

     

    End of Note



    You received this message because you are subscribed to the Google Groups "ERPNext Developer Forum" group.

    To unsubscribe from this group and stop receiving emails from it, send an email to erpnext-developer-forum+un…@googlegroups.com.

    To view this discussion on the web visit https://groups.google.com/d/msgid/erpnext-developer-forum/819BE04F-DC57-4985-B0B4-7006C869BD59%40iwebnotes.com.

    For more options, visit https://groups.google.com/d/optout.
The tildes fixed everything I needed. The joining of tables is now working as expected...can't believe I thought it was single quotes. 

Many many thanks.

On Tuesday, May 27, 2014 10:43:57 PM UTC-4, Anand Doshi wrote:
You have to use tilde and not quotes to enclose table names and field names separately. 

For example
`tabSales Order`.`customer`

Sent from my phone

On 28-May-2014, at 8:08, Rushabh Mehta <rm...@gmail.com> wrote:

1. Lookup SQL help on how to join tables in queries - you have not put enough conditions and this is off topic for this forum.
2. Check how other reports are written

for table names with spaces, use `tabItem Price`



@rushabh_mehta

On 28-May-2014, at 12:47 am, Alec Ruiz-Ramon <al...@gmail.com> wrote:

Hi, 

I'm trying to create a query report for use with multiple tables and can't seem to get it to work. I'm not extremely well versed in MySQL, and might be able to figure it out but there isn't an error console when creating a query report, it just will say "Loading Report" and never load when something is broken.

Brief background : For sale of parts, one part can fit in many engines. The different engines that a part is compatible with are stored in the website item group table, so that a customer can select their engine and see available parts on the ERPNext website. 

I need a query report that allows me to type in a certain engine, and see parts that are compatible with that engine (and further filter in top bar by brand, part number, etc), along with current stock, and pricing. I can't do a script report as I don't know how, and don't have access to backend. 

Here's what I've gotten done so far: 

SELECT
   tabWebsite Item Group.item_group as "Engine:120",
   tabItem.item_code as "Part Number:Link/Item:150",
   tabItem.brand as "Brand:120",
   tabItem.item_group as "Item Group:Link/Item Group:120",
   tabItem Price.price_list as "Price List:120",
   tabItem Price.ref_rate as "Rate:Currency:120",
   tabItem.actual_qty as "Actual:80",
   tabItem.projected_qty as "Projected:80"
FROM
   tabWebsite Item Group, tabItem, tabItem Price
WHERE
   tabItem.is_purchase_item = "Yes"


It's notable that this doesn't work both with and without single quotes around the tables - but when I do a test-table, with SELECT 'tabItem'.item_code (note quotes) FROM 'tabItem', it does not work. When the quotes are removed, it works. So I don't know how to handle tables with a space in their name, as if I try to make a table with only price_list from tabItem Price, it does not work. 

I've also experimented with using a UNION command but have failed there as well. 

Thank you for your help and time,
Alec Ruiz-Ramon




Note:

 

If you are posting an issue,

  1. We should be able to replicate it at our end. So please give us as much information as you can. Please see it from the point of view of the person receiving the communication.
  2. Paste your code at http://pastebin.com or http://gist.github.com and send only the URL via email
  3. For sending images, use http://imgur.com or other similar services. Do not send images as attachments. Links are good. Same goes for any file you are going to send.

     

    End of Note



    You received this message because you are subscribed to the Google Groups “ERPNext Developer Forum” group.

    To unsubscribe from this group and stop receiving emails from it, send an email to erpnext-developer-forum+un…@googlegroups.com.

    To view this discussion on the web visit https://groups.google.com/d/msgid/erpnext-developer-forum/ccd62967-6050-4046-b989-321ad701b8b3%40googlegroups.com.

    For more options, visit https://groups.google.com/d/optout.



Note:

 

If you are posting an issue,

  1. We should be able to replicate it at our end. So please give us as much information as you can. Please see it from the point of view of the person receiving the communication.
  2. Paste your code at http://pastebin.com or http://gist.github.com and send only the URL via email
  3. For sending images, use http://imgur.com or other similar services. Do not send images as attachments. Links are good. Same goes for any file you are going to send.

     

    End of Note



    You received this message because you are subscribed to the Google Groups “ERPNext Developer Forum” group.

    To unsubscribe from this group and stop receiving emails from it, send an email to erpnext-developer-forum+un…@googlegroups.com.

    To view this discussion on the web visit https://groups.google.com/d/msgid/erpnext-developer-forum/E30396A3-3286-4E36-9925-2C921E344435%40gmail.com.

    For more options, visit https://groups.google.com/d/optout.



Note:

 

If you are posting an issue,

  1. We should be able to replicate it at our end. So please give us as much information as you can. Please see it from the point of view of the person receiving the communication.
  2. Paste your code at http://pastebin.com or http://gist.github.com and send only the URL via email
  3. For sending images, use http://imgur.com or other similar services. Do not send images as attachments. Links are good. Same goes for any file you are going to send.

     

    End of Note



    You received this message because you are subscribed to the Google Groups "ERPNext Developer Forum" group.

    To unsubscribe from this group and stop receiving emails from it, send an email to erpnext-developer-forum+un…@googlegroups.com.

    To view this discussion on the web visit https://groups.google.com/d/msgid/erpnext-developer-forum/795da85e-e12b-458c-9eef-b8d1bbbf9568%40googlegroups.com.

    For more options, visit https://groups.google.com/d/optout.