Merge two queries into one. [SOLVED]

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
Stryks
Forum Regular
Posts: 746
Joined: Wed Jan 14, 2004 5:06 pm

Merge two queries into one. [SOLVED]

Post by Stryks »

Hi guys,

I have another one of these problems, trying to get one query doing the job of two. Most of the time I have been able to get a solutio, but this one is complex because I think it needs to use two aliases of the same table to work.

The two aparts are as follows:

To list the items belonging to user 8, of type 12.

Code: Select all

SELECT 'tbl_master_items'.'name', 'tbl_vendor_product'.'VI_PK' FROM 'tbl_vendor_product' INNER JOIN 'tbl_master_items' ON ('tbl_vendor_product'.'MI_ID'='tbl_master_items'.'MI_PK') WHERE ('tbl_master_items'.'MI_ID'='12') AND ('tbl_vendor_product'.'_ID'='8')
To see how many times a specifi item has been referenced in a recipe.

Code: Select all

SELECT COUNT ('tbl_vendor_recipe_ingredients'.'VRI_PK') AS 'count' FROM 'tbl_vendor_recipe_ingredients' INNER JOIN 'tbl_vendor_product' ON ('tbl_vendor_recipe_ingredients'.'VR_ID' = 'tbl_vendor_product'.'VI_PK') WHERE ('tbl_vendor_recipe_ingredients'.'VI_ID'='271') AND ('tbl_vendor_product'.'V_ID'='8')
Basically, what I am hoping to wind up with is a list of the different options belonging to a specific user, in order of the most used items to the least / unused items.

I know it is probrably difficult to work all this out from these queries ... god knows I cant ... but if you can see any way to get both of these tasks done it would be most appreciated.

Using mySQL if it helps.

Cheers.
Last edited by Stryks on Wed Apr 13, 2005 1:23 am, edited 1 time in total.
User avatar
CoderGoblin
DevNet Resident
Posts: 1425
Joined: Tue Mar 16, 2004 10:03 am
Location: Aachen, Germany

Post by CoderGoblin »

Code: Select all

SELECT tbl_master_items.name,
       tbl_vendor_product.VI_PK,
       count(tbl_vendor_recipe_ingredients)
FROM tbl_vendor_product,
     tbl_master_items,
     tbl_vendor_recipe_ingredients 
WHERE 
     tbl_vendor_product.MI_ID=tbl_master_items.MI_PK AND
     tbl_vendor_recipe_ingredients.VR_ID=tbl_vendor_product.VI_PK 
     tbl_master_items.MI_ID=12 AND
     tbl_vendor_product._ID=8 AND
     tbl_vendor_recipe_ingredients.VI_ID=271
GROUP BY tbl_vendor_recipe_ingredients
May do it, cannot test. (may need to also add tbl_master_items.name,tbl_vendor_product.VI_PK to group I can never remember when you need to).

NOTE: I do not use INNER JOINS/ON, I simply use a WHERE condition. I find this easier to read but is my preference. The only time I specify JOIN's is on an OUTER JOIN.

I am also not sure if you need all the single quotes. I find it is better to ensure my database naming convention is all lowercase. That way all I ever to to quote are strings. (but then I use postgres)
User avatar
Stryks
Forum Regular
Posts: 746
Joined: Wed Jan 14, 2004 5:06 pm

Post by Stryks »

Thanks for the reply,

I'm away from my main computer for the next week or so and I'm trying to get some work done on the lappy. frustrating to be away from all the resources. Still, I'll give that a go on the lappy and when I manage to get online again, I'll let you know how it turns out.

Thanks again for the reply ... if anyone wants to give any other assistance, it is still appreciated.

Cheers.
User avatar
Stryks
Forum Regular
Posts: 746
Joined: Wed Jan 14, 2004 5:06 pm

Post by Stryks »

I finally got something working, although I did have to alter another table and use it in place of the duplicated table. I have to duplicate some data, but it's only one field, and that only a reference to the V_ID.

I tried to use the above example above, but for some odd reason I just couldnt get my head around how it works. Primarily, I cant see how relationships are drawn between the joined tables. As an example, I am trying to get the count of the number of times one item was referred to in a different table. If I do a where join, how can I tell it to include items which have no references. This is the purpose of the join to my mind.

Anyhow, I used a program to build my SQL when I first started out, and I just cant seem to get out of the format which it used.

This is the final solution:

Code: Select all

SELECT 
  `a1`.`name`,
  `a2`.`VI_PK`,
  count(`b2`.`VRI_PK`) AS `rank`
FROM
  `tbl_vendor_product` `a2`
  INNER JOIN `tbl_master_items` `a1` ON (`a2`.`MI_ID` = `a1`.`MI_PK`)
  LEFT OUTER JOIN `tbl_vendor_recipe_ingredients` `b2` ON (`a2`.`VI_PK` = `b2`.`VI_ID`)
  RIGHT JOIN `tbl_vendor_recipe` `b1` ON (`b1`.`VR_PK`=`b2`.`VR_ID` AND `b1`.`V_ID`='8')
WHERE
  (`a1`.`MT_ID` = '12') AND 
  (`a2`.`V_ID` = '8')
GROUP BY
  `a2`.`VI_PK`
ORDER BY
  `a1`.`name`
Thanks for the help.
Post Reply