Home News Contact Us Forum About Us Demos Products F.A.Q.
Shopping Cart
You currently have 0 items in your cart.


Recent Events
  • 31/12/2023 New Year SALE

    We are glad to announce New Year SALE. 25% discount for all our extensions. Use NY24 coupon code. Hurry up the discount is valid till 7 January.

  • 21/11/2023 BLACK FRIDAY 23 is coming

    BIG SALE, 35% discount for all our extensions. Use BF23 coupon code. Hurry up the discount is valid till 27 November.


2Checkout.com, Inc. is an authorized retailer of goods and services provided by ARI Soft. 2CheckOut




Follow us on twitter



Welcome, Guest
Please Login or Register.    Lost Password?

Stumped - Adding Data From Multiple Tables
(1 viewing) (1) Guest
Go to bottomPage: 1
TOPIC: Stumped - Adding Data From Multiple Tables
#14293
Stumped - Adding Data From Multiple Tables 12 Years, 11 Months ago Karma: 0
Hello,

Just have to say first for what I have done this extension is great! My question..

I would like to combine information from 2 tables like what is in this tutorial:
www.w3schools.com/Sql/sql_join_full.asp

I need to pull data from two Virtuemart tables in the Joomla database to show:

User ID | Order ID | First Name | Last Name | State | Product Purchased

Here are the two tables and what each have:

jos_vm_order_item - USER_ID, ORDER_ID, ORDER_ITEM_NAME

jos_vm_order_user_info - USER_ID, ORDER_ID, FIRST_NAME, LAST_NAME, STATE

The common column that I would like to match up is USER_ID. This will help me show in the report the user then all their orders.

I would also like to ORDER by state..

Any suggestions?

Thanks!

John
The administrator has disabled public write access.
 
#14303
Re:Stumped - Adding Data From Multiple Tables 12 Years, 11 Months ago Karma: 746
Hello,

Try to use the next SQL query:

Code:


SELECT
 OUI.user_id, OI.order_id, OUI.first_name, OUI.last_name, OUI.state, OI.order_item_name
FROM
 #__vm_order_item OI INNER JOIN #__vm_order_user_info OUI
   ON OI.order_id = OUI.order_id
ORDER BY
 OUI.state DESC



Regards,
ARI Soft
The administrator has disabled public write access.
 
#14335
Re:Stumped - Adding Data From Multiple Tables 12 Years, 11 Months ago Karma: 0
WOW! Thanks you so much! You are definitely getting 5 stars!
The administrator has disabled public write access.
 
#19382
Re:Stumped - Adding Data From Multiple Tables 12 Years, 6 Months ago Karma: 0
I have the same issue, need to pull data from two databases as follows.

jos_series
* factory
* factory 2
* series
* power
* convection
* dimensions

jos_part
* part
* v1
* a1
* Efficiency

The tables both have a field named series for which I want the data related, so if I follow your query above. My query should look like:

SELECT
OUI.factory, OUI.factory2, OUI.series, OUI.power, OUI.convection, OUI.dimensions, OI.part, OI.v1, OI.A1, OI.Efficiency
FROM
#_part OI INNER JOIN #_series OUI
ON OI.series = OUI.series

To complicate the matter, I also need a where statement for both jos_series and jos part like these:

SELECT * FROM `jos_series` WHERE `type` = 'open frame'

and

SELECT * FROM `jos_part` WHERE `V1` = '5'


Thanks in advance for help with what is a great module.
The administrator has disabled public write access.
 
#19383
Re:Stumped - Adding Data From Multiple Tables 12 Years, 6 Months ago Karma: 0
. . . and working a little while longer and adding a few more fields, I got my query working as follows with a few Where statements.

SELECT
OUI.factory, OUI.factory2, OUI.series, OUI.power, OUI.convection, OUI.dimensions, OUI.type, OI.part, OI.V1, OI.V2, OI.A1, OI.Efficiency
FROM
jos_part OI INNER JOIN jos_series OUI ON OI.series = OUI.series
WHERE OI.V1 = 3.3 AND OUI.type = 'Open Frame' AND OI.V2 = '0'

The administrator has disabled public write access.
 
#19406
Re:Stumped - Adding Data From Multiple Tables 12 Years, 6 Months ago Karma: 746
Hello,

Do you need any assistance from us?

Regards,
ARI Soft
The administrator has disabled public write access.
 
Go to topPage: 1