Tech in 876 Designing & Programming Web Backend (Scripting and Programming)
Help with Complex sql (1 viewing) (1) Guests
Post Reply
Help with Complex sql
by jahflasher 6 Months, 3 Weeks ago
Hey i have been struggling for days to get a some data from four tables in one query. I have attached an image of table relations.


What i am trying to accomplish is this:
1. Select only unique values from the products table.
2. Count all the merchants that enter the same product . (example : 6 merchants selling fruta)
3. Get the cheapest price for in the data_base_ for all products of the same name.(example: Fruta price starts from $70.00
Last Edit: 01/31/2008 07:01pm By cyberpunk.
The administrator has disabled public write access. | Report to moderator   Logged Logged  
Re:Help with Complex sql
by cyberpunk 6 Months, 2 Weeks ago
I had a lot of work to do yesterday and today as well. I'll take a look at it and have a reply by tonight tomorrow morning latest all being well.

sorry man
The administrator has disabled public write access. | Report to moderator   Logged Logged  
Re:Help with Complex sql
by Spaz 6 Months, 2 Weeks ago
Not sure if I understand the question, sounds like u need to use GROUP BY in ur qry.
quick search came up with http://www.tizag.com/mysqlTutorial/mysqlgroupby.php
I will update the post with an example using the tables u posted. when some more time opens up.
The administrator has disabled public write access. | Report to moderator   Logged Logged  
Re:Help with Complex sql
by cyberpunk 6 Months, 2 Weeks ago
The answer is actually a join.. a inner join or something of that nature should get it done.

edit

What kind of data_base_ structure are you working with btw? Mysql or mssql?
Last Edit: 02/01/2008 08:27pm By cyberpunk.
The administrator has disabled public write access. | Report to moderator   Logged Logged  
Re:Help with Complex sql
by jahflasher 6 Months, 2 Weeks ago
Well i am working with Mysql. I have been doing some reading up on join. But have not been able to put together a working _script_.
The administrator has disabled public write access. | Report to moderator   Logged Logged  
Re:Help with Complex sql
by jahflasher 6 Months, 2 Weeks ago
Well i have figured out the sql(not without much effort), so here is what worked for me:

Code: :

 
SELECT DISTINCT
       COUNT(DISTINCT `myp_merchant_mrt`.compName_mrt), MIN(`myp_products_prd`
       .price_pdt)
          , `myp_products_prd`.product_pdt, `myp_products_prd`.date_pdt,
       `myp_category_cat`.category_cat, `myp_category_cat`.id_cat,
       `myp_products_prd`.idmrt_pdt, `myp_menu_mnu`.name_mnu
  FROM    (   (   pricewatchers.myp_category_cat `myp_category_cat`
               INNER JOIN
                  pricewatchers.myp_products_prd `myp_products_prd`
               ON (`myp_category_cat`.id_cat = `myp_products_prd`.idcat_pdt))
           INNER JOIN
              pricewatchers.myp_merchant_mrt `myp_merchant_mrt`
           ON (`myp_merchant_mrt`.id_mrt = `myp_products_prd`.idmrt_pdt))
       INNER JOIN
          pricewatchers.myp_menu_mnu `myp_menu_mnu`
       ON (`myp_menu_mnu`.id_mnu = `myp_category_cat`.idmnu_cat)
 WHERE (`myp_category_cat`.id_cat = 3)
 GROUP BY `myp_products_prd`.product_pdt

Last Edit: 02/07/2008 06:22pm By jahflasher.
The administrator has disabled public write access. | Report to moderator   Logged Logged  
Re:Help with Complex sql
by cyberpunk 6 Months, 2 Weeks ago
nice so i was right about the inner join. Congratulations man.

next time when you writing your complex queries cut the size down a bit by setting reference names for the tables..

example

Code: :

 select...... from table1 as a, table 2 as b where a.field1 = value and b.field2 = to value



you get what i mean right?
Last Edit: 02/07/2008 06:26pm By cyberpunk.
The administrator has disabled public write access. | Report to moderator   Logged Logged  
Post Reply
 
     
Powered by FireBoardget the latest posts directly to your desktop
Numuni 1.0.3.0