[Solved] Why isn't my triple join working?


SP,

I'm trying to join three tables. I want some items from table "item_inventory" to be displayed if they are appear in the right category and are also set as a "best seller". The best seller is set in a table called item_field_data. If there is a record in item_field_data with the column `item_field_id` set to 85 and the column `item_field_value` set to 1 then it is a "best seller". Also, there a huge schwack of categories it can appear in. Here is my query.

Code: SELECT DISTINCT i.item_inventory_id, i.item_inventory_title, i.item_type_id, i.item_inventory_description, i.item_inventory_price FROM item_inventory i LEFT JOIN item_field_data ifd ON ifd.item_inventory_id = ifd.item_inventory_id LEFT JOIN item_category_data icd ON i.item_inventory_id = icd.item_inventory_id WHERE i.item_type_id = "1" AND i.item_inventory_public = "1" AND ifd.item_field_id = "85" AND ifd.item_field_value = "1" AND ( icd.item_category_id = "3094" OR icd.item_category_id = "10" OR icd.item_category_id = "2883" OR icd.item_category_id = "2884" OR icd.item_category_id = "2885" OR icd.item_category_id = "2886" OR icd.item_category_id = "2887" OR icd.item_category_id = "2888" OR icd.item_category_id = "2891" OR icd.item_category_id = "2892" OR icd.item_category_id = "2893" OR icd.item_category_id = "2894" OR icd.item_category_id = "2918" OR icd.item_category_id = "2988" OR icd.item_category_id = "2989" OR icd.item_category_id = "1411" OR icd.item_category_id = "2677" OR icd.item_category_id = "2680" OR icd.item_category_id = "2890" OR icd.item_category_id = "2949" OR icd.item_category_id = "2681" OR icd.item_category_id = "2683" OR icd.item_category_id = "2773" OR icd.item_category_id = "2774" OR icd.item_category_id = "2783" OR icd.item_category_id = "2784" OR icd.item_category_id = "2788" OR icd.item_category_id = "2789" OR icd.item_category_id = "2700" OR icd.item_category_id = "2698" OR icd.item_category_id = "2701" OR icd.item_category_id = "2766" OR icd.item_category_id = "2778" OR icd.item_category_id = "2785" OR icd.item_category_id = "2875" OR icd.item_category_id = "2899" OR icd.item_category_id = "2908" OR icd.item_category_id = "2753" OR icd.item_category_id = "2754" OR icd.item_category_id = "2180" OR icd.item_category_id = "2755" OR icd.item_category_id = "2780" OR icd.item_category_id = "2795" OR icd.item_category_id = "2807" OR icd.item_category_id = "2767" OR icd.item_category_id = "2961" OR icd.item_category_id = "2962" OR icd.item_category_id = "2963" OR icd.item_category_id = "3044" OR icd.item_category_id = "3045" OR icd.item_category_id = "3046" OR icd.item_category_id = "3102" OR icd.item_category_id = "3103" OR icd.item_category_id = "3047" OR icd.item_category_id = "2768" OR icd.item_category_id = "2870" OR icd.item_category_id = "2871" OR icd.item_category_id = "2897" OR icd.item_category_id = "2772" OR icd.item_category_id = "2776" OR icd.item_category_id = "2786" OR icd.item_category_id = "2790" OR icd.item_category_id = "2792" OR icd.item_category_id = "2793" OR icd.item_category_id = "2794" OR icd.item_category_id = "2791" OR icd.item_category_id = "2854" OR icd.item_category_id = "2855" OR icd.item_category_id = "2856" OR icd.item_category_id = "2857" OR icd.item_category_id = "2858" OR icd.item_category_id = "2898" OR icd.item_category_id = "2907" OR icd.item_category_id = "3023" OR icd.item_category_id = "3025" ) ORDER BY i.date_created desc LIMIT 15 The problem I am having is that the query seems to not care whether or not the item is set as a best seller or not. If I remove the extra table join for the category matching the query executes as expected. So I figure I'm just doing the wrong kind of join or I have the joins out of order I'm not really sure, but I've tried a whole bunch of things and none of them gave me the expected result, so now I turn to trusty old sitepoint for an answer.

Posted On: Monday 31st of December 2012 01:44:50 AM Total Views:  469
View Complete with Replies

RELATED TOPICS OF MYSQL PROGRAMMING LANGUAGE




requirements changed in join...now...how to do it?

Hi I have 2 tables, faq and faq_categories...i have a join that work, and so far, I was a happy camper. But...requirements change, and i have to change the join, but i don't know how to do it Here is the current code that works just fine: Code: SELECT faq.* , faq_categories.categoryname FROM faq JOIN faq_categories ON ( faq.catid = faq_categories.catid) So far, all faq belongs to one category...but from now on, there will be faq which will not belonng to any category....and that complicate things, at least for me. How should I change this code in order to display the faq which does not have catid Here are my tables: Code: CREATE TABLE IF NOT EXISTS `faq_categories` ( `catid` int(11) NOT NULL AUTO_INCREMENT, `parentid` int(11) DEFAULT NULL, `categoryname` varchar(255) NOT NULL, `categoryname_en` varchar(255) DEFAULT NULL, `description` text, `description_en` text, `metatags` text, `metatags_en` text, `sorder` int(11) NOT NULL, `visible` tinyint(4) NOT NULL, `categoryphoto` varchar(255) DEFAULT '', PRIMARY KEY (`catid`), KEY `parentid_fk` (`parentid`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=204 ; CREATE TABLE IF NOT EXISTS `faq` ( `faqid` int(11) NOT NULL AUTO_INCREMENT, `catid` int(11) DEFAULT NULL, `question` text NOT NULL, `question_en` text NOT NULL, `answer` text, `answer_en` text, `metatags` text, `metatags_en` text, `sorder` tinyint(4) DEFAULT NULL, `visible` tinyint(4) DEFAULT NULL, PRIMARY KEY (`faqid`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=6 ;
VIEWS ON THIS POST

104

Posted on:

Thursday 25th October 2012
View Replies!

join unique row in one table with a non unique row in another

Is it possible to do this: In a single query, join a row within a table based on a column with unique stock number, with a targeted row within within a group of rows with the same stock number in another table If so how do you target the non unique row
VIEWS ON THIS POST

186

Posted on:

Thursday 25th October 2012
View Replies!

Very slow joins table (EXPLAIN included)

I'm very confused about my dirty quires. So, I don't know what's wrong with my JOINS why they are so slow I have 2 table with large data: forum_messages :: 267,808+ row totalforum_users :: 332,042+ total Here is my Query: PHP Code: SELECTusers.type_id,\t\t\t\t\t\t\t\tusers.full_name,\t\t\t\t\t\t\t\tusers.user_id,\t\t\t\t\t\t\t\tmsgs.message_id,\t\t\t\t\t\t\t\tmsgs.message_body,\t\t\t\t\t\t\t\tmsgs.msg_is_read,\t\t\t\t\t\t\t\tmsgs.user_send_id,\t\t\t\t\t\t\t\tUNIX_TIMESTAMP(msgs.sent_date)ASsnt_date\t\t\t\t\t\tFROM\t\t\t\t\t\t\t\t`forum_messages`ASmsgs\tINNERJOIN`forum_users`ASusersONmsgs.user_send_id=users.user_id\t\t\t\t\t\tWHERE\t\t\t\t\t\t\t\tmsgs.user_receive_id=1\t\t\t\t\t\t\t\tANDmsgs.msg_del_receive=1\t\t\t\t\t\t\t\tANDmsgs.is_shows=1\t\t\t\t\t\tORDERBYmsgs.sent_dateDESCLIMIT10 forum_messages Indexes: 1.jpg forum_messages EXPLAIN: 2.jpg forum_users Indexes: 3.jpg Is there any idea please
VIEWS ON THIS POST

155

Posted on:

Thursday 25th October 2012
View Replies!

getting 2 counts from 1 table using join??

Im trying to get stats from a table. It holds hits and emails sent. This is what i tried, but its not correct: Im almost certain the "Dress" table doesnt need to be in there. Only added it thinking it would help! PHP Code: SELECT DATE_FORMAT(hits.stat_date,'%D%b')asdate, COUNT(hits.stat_id)AStotal_hits, COUNT(emails.stat_id)AStotal_emails FROMdress_listingasdress LEFTJOINlisting_statsashitsONdress.dress_id=hits.dress_idandhits.stat_type='hit' LEFTJOINlisting_statsasemailsONdress.dress_id=emails.dress_idandemails.stat_type='email' GROUPBYDATE_FORMAT(hits.stat_date,'%Y-%m-%d') Any suggestions would be appreciated!
VIEWS ON THIS POST

113

Posted on:

Thursday 25th October 2012
View Replies!

mysql privilege: does "select" allow me to join

After I changed my privileges to just select, this query ceased to function: Code SQL: SELECT base_SalesTable.* ,buckets.width ,buckets.teeth FROM base_SalesTable LEFT OUTER JOIN buckets ON base_SalesTable.name = buckets.name WHERE base_SalesTable.category = :category AND base_SalesTable.mincap = :mincap ORDER BY weight Happy Holidays
VIEWS ON THIS POST

141

Posted on:

Thursday 25th October 2012
View Replies!

Join sintax help - not even know if I really need a join - please advice. :)

all, This is a common scenario but I'm struggling here. I have 3 tables. The middle one relates table 1 and 3 by having a Fk from both tables. I would like to list some data from table 1 and same data from the table 3. But that data should be organized by ordering a given column in table 2. Can I have an example of the above, so that I can study and try to apply it to my code If you prefer, I can well provide the create tables here, I was just thinking that, like this, I can properly learn. K.
VIEWS ON THIS POST

145

Posted on:

Thursday 25th October 2012
View Replies!

How to join multiple tables?

how to inner join with multiple tables in mysql...
VIEWS ON THIS POST

102

Posted on:

Thursday 25th October 2012
View Replies!

Can someone help with a join SQL statement

Hi I've got a Wordpress powered site but I've also got a few php pages outside of my WP installation which I need to query the database that powers my WP site. Everything is fine apart from the fact that I don't really know how to write a particular SQL statement which involves a join. I was wondering if someone could help me. Basically what I want is to be able to get the ID, post_title, post_excerpt, post_content from the wp_posts table and then the meta_value from the table wp_postmeta table but where the meta_key = "large_download_button" and the ID from wp_posts matches the post_id on wp_postmeta Of course wp_posts.ID and wp_postmeta.post_id are the common fields between the two tables. These are the two tables wp_posts and wp_postmeta wp_posts ID post_author post_date post_date_gmt post_content post_title post_excerpt post_status comment_status pint_status post_password post_name to_ping pinged post_modified post_modified_gmt post_content_filtered post_parent guid menu_order post_type post_mine_type comment_count wp_postmeta meta_id post_id meta_key meta_value many
VIEWS ON THIS POST

112

Posted on:

Thursday 25th October 2012
View Replies!

Strange mysql query output from 3 table join

TableA (Author Table) author_id author_name TableB (Post Table) post_id author_id TableC (Earning Table) post_id (post id is not unique) post_earning I wanted to generate a report consists of per author total earning. author_id author_name total_earning (sum of earnings of all the posts by author) The SQL Query used: Code: SELECT a.author_id, a.author_name, sum(post_earning) as total_earnings FROM TableA a Inner Join TableB b on b.author_id = a.author_id Inner Join TableC c on c.post_id = b.post_id Group By a.author_id, a.author_name The Result I got is this : Code: ID user_login total_earnings 2 Redstar 13.99 7 Redleaf 980.18 10 topnhotnews 80.43 11 zmmishad 39.27 13 rashel 1248.34 14 coolsaint 1.66 16 hotnazmul 9.83 17 rubel 0.14 21 mahfuz1986 1.09 48 ripon 12.96 60 KHK 27.81 The sum of the total earning is actually 2863.22. But if i add all the values of the result table I get 2415. Where is the problem What I am missing I am sure SQL here can help me out. Here are the 3 tables i am using http://bit.ly/cNKS6k (TableA.csv) http://bit.ly/cJxRRN (TableB.csv) http://bit.ly/bNv9hs (TableC.csv)
VIEWS ON THIS POST

193

Posted on:

Thursday 25th October 2012
View Replies!

Inner our left join?

Hi all. I need your help, this is my tables in db mysql: do_tableA PHP Code: ID\t\tNAME 1\t\tKEVIN 2\t\tLAURA 3\t\tCINDY 4\t\tROBERT do_tableB PHP Code: ID\t\tNAME 1\t\tHUMPREY 2\t\tKEVIN 3\t\tLAURA 4\t\tMARGOT 5\t\tCINDY 6\t\tLAUREN 7\t\tROBERT 8\t\tFREDDIE I need this output: PHP Code: 1\t\tHUMPREY 4\t\tMARGOT 6\t\tLAUREN 8\t\tFREDDIE
VIEWS ON THIS POST

146

Posted on:

Thursday 25th October 2012
View Replies!

Query matching join to the right table

I'm trying to match comments to resources in different tables. Each comment has a resource_type and resource_id The resource type is the name of table that the resource is part of, and the id is the id from that table. So it might look like: Comments resource_type | resource_id | content ------------------------------------------- photos | 21 | blah blah blah documents |63 | blah blah blah photos id | title documents id | title Is it possible to write a query that would select the title from the right resource table for each comment
VIEWS ON THIS POST

112

Posted on:

Thursday 25th October 2012
View Replies!

using int vs using varchar with join

I have tables cities and users. Does it make sense to use for a search int together with join instead of varchar Does it make any huge difference Example: Code: SELECT userId FROM users WHERE cityName LIKE '%searchterm%' vs Code: SELECT userId FROM users INNER JOIN on cities users.cityId=cities.cityId WHERE cities.cityName LIKE '%searchterm%' Which one is better or is it about the same Tnx!
VIEWS ON THIS POST

141

Posted on:

Thursday 25th October 2012
View Replies!

mysql joining 2 tables (default values)

.. I came to situation where I have to join 2 tables on some other table: some_table: id domain table domains: id domaintype_id domain table domaintypes: id type This is how I join: domains ON some_table.domain = domains.domain domaintypes ON domaintype.id = domains.domaintype_id Now it happens that sometimes there is no domain for some_table row (so the domaintype will be NULL) and I want to be able to set this column default value 0 (when that happens).. How is it possible to do that Many
VIEWS ON THIS POST

223

Posted on:

Thursday 25th October 2012
View Replies!

optimizing multi table join

Getting more comfortable with mysql but this seems to be the best place to go when i'm stumped... so here i am. Below is the code for a multi table join that I'm trying to optimize Code SQL: SELECT pu.USER, mgs.countyName, mgs.stateAbbr, mgs.cityName,mgs.latitude, mgs.longitude , mu.userType, mu.privacy , mt.certification, mt.availability, mt.wage FROM morris_geoSecondaryData mgs INNER JOIN phpfox_user pu ON pu.id = mgs.id INNER JOIN morris_users mu ON mu.id = mgs.id INNER JOIN morris_teachers mt ON mu.id = mt.id WHERE mgs.latitude >= 40 AND mgs.latitude = -74 AND mgs.longitude
VIEWS ON THIS POST

120

Posted on:

Thursday 25th October 2012
View Replies!

join problem

I have 2 tables that has the many to many relation. I want to have all the records of the first table and only one of the second table in a query, how can I do that Dimis
VIEWS ON THIS POST

108

Posted on:

Thursday 25th October 2012
View Replies!

join after removing the last 2 digit

Code: data in myTable3 (id) city (132) Boston (291) New York (569) Paris (837) London (1295) Berlin (3659) Seoul (17292) Montreal I have data in myTable3 like the above. The last two digits in red are just check digits. So the real id of Boston is 1. the real id of New York is 2. the real id of Paris is 5. the real id of London is 8. the real id of Berlin is 12. the real id of Seoul is 36. the real id of montreal is 172. I like to join myTable4 on the real id of myTable3 = myTable4.id. The following doesn't work correctly, but I hope you understand what I want by the would-be code below. Code: would-be code left join myTable4 on reverse(removeCharacter(reverse(myTable3.id),from 1, to 2))=myTable4.id I am using mySQL 5.
VIEWS ON THIS POST

121

Posted on:

Thursday 25th October 2012
View Replies!

SQL inner join question

, I am trying to write a query but am having difficulty. I need to find every person in table A that has a certain sport selected. My boss wants some fields displayed in 2 other tables for his report that I match on the person's id. But they may or may not have a record in the other 2 tables. My query isn't working if the person doesn't have an adjoining record in the other 2 tables. How do I fix this Here is my sql code.. SELECT A.jersey_num, A.first_name, A.last_name, A.position, A.height_ft, A.weight_lbs, A.dob, A.academic_year_prog, A.academic_year, A.city, A.province, B.HighSchool, C.LastYrSchool, C.YrsOfEligUsed FROM basic A INNER JOIN athletebio B on A.MacID = B.MacID INNER JOIN eligform C on A.MacID = C.MacID WHERE sport1 = '$sportID' OR sport2 = '$sportID'
VIEWS ON THIS POST

186

Posted on:

Thursday 25th October 2012
View Replies!

selecting one table from joined table.

I want to select a table where is currently joined (has foreign key). is this possible or do i need to join select both the tables first before i can get the value of a field inside a table joined i just want to select one table (where is currently has foreign key). is this possible
VIEWS ON THIS POST

158

Posted on:

Thursday 25th October 2012
View Replies!

Need help writing a query to join multiple fields

I am trying to write a query for a single table where each record contains five part number fields. I've never done this type of query before and could use some help. Here is the format of the table: Code: Table Name: MyTable Fields: widget_1_partnum widget_2_partnum widget_3_partnum widget_4_partnum widget_5_partnum widget_1_description widget_2_description ... I want a query that will merge and return all distinct widget part numbers in a single result set field called partnum, independent of whether the part numbers are related to widget_a, widget_b, etc. Basically I want to lump all the widget part numbers (from all five fields) into a single group and then have it return distinct part numbers from that group in a single result variable that I can index through in PHP (let's call it "partnum"). Anyone know how to do this Here is the code I'm trying to generate with this query: Code: $result=mysql_query(""); $num_rows=mysql_num_rows($result); for ($i=0;$i
VIEWS ON THIS POST

144

Posted on:

Thursday 25th October 2012
View Replies!

Using a comparison variable from one join to another

Am I doing this correctly The first join ($query) searches through my team table and finds the number of conferences. In this case there are six of them. This part seems to be working fine. The second one I am trying to pull all the teams out that belong to that conference and list them under it. Each conference doesn't have the same number of teams within it. I seem to be having troubles with this comparison... WHERE conference = " . $info->conference . ""; I get this error: Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in C:\Server\Files\list.php on line 46 PHP Code: $db=mysql_select_db($database,$link);$query="SELECT*".\t\t\t\t"FROMteamsASt".\t\t\t\t"LEFTOUTER".\t\t\t\t"JOINteams_coachesAStc".\t\t\t\t"ONtc.team_id=t.team_id".\t\t\t\t"LEFTOUTER".\t\t\t\t"JOINconferencesAScf".\t\t\t\t"ONcf.conference_id=tc.conference_id".\t\t\t\t"LEFTOUTER".\t\t\t\t"JOINsportsASs".\t\t\t\t"ONs.sport_id=tc.sport_id".\t\t\t\t"WHEREtc.year='2008'".\t\t\t\t"ANDtc.sport_id='1'".\t\t\t\t"GROUPBYconference".\t\t\t\t"ORDERBYconference";$result=mysql_query($query);if(mysql_num_rows($result)){\twhile($info=mysql_fetch_object($result)){\t\techo'Conference:'.$info->conference.'';\t\techo'';\t\t\t$standings="SELECT*".\t\t\t\t\t\t\t\t\t"FROMteamsASt".\t\t\t\t\t\t\t\t\t"LEFTOUTER".\t\t\t\t\t\t\t\t\t"JOINteams_coachesAStc".\t\t\t\t\t\t\t\t\t"ONtc.team_id=t.team_id".\t\t\t\t\t\t\t\t\t"WHEREconference=".$info->conference."";\t\t\t$sql=mysql_query($standings);\t\t\tif(mysql_num_rows($sql)){team_name.'';\t\t\t\t}\t\t\t}\t}}
VIEWS ON THIS POST

102

Posted on:

Thursday 25th October 2012
View Replies!

Where should I define a table alias if im calling it in a outer join?

Code: SELECT clientes_alias. * , SUM( CASE WHEN cotizaciones_alias.estado > 2.00 THEN items_de_cotizaciones_alias.costo_total ELSE 0 END ) AS ventas_a_cliente FROM clientes clientes_alias LEFT OUTER JOIN clientes ON cotizaciones_alias.cliente = clientes_alias.id LEFT OUTER JOIN cotizaciones cotizaciones_alias ON items_de_cotizaciones_alias.cotizacion = cotizaciones_alias.id GROUP BY clientes_alias.id SQL SAYS: Code: #1109 - Unknown table 'items_de_cotizaciones_alias' in field list
VIEWS ON THIS POST

345

Posted on:

Thursday 25th October 2012
View Replies!

joining a table on 1 of 2 columns

I'm a bit stuck on how to achieve the following: I have 3 tables: members members_contacts members_business members_contacts consists of: member_id contact_member_id approved member_id and contact_member_id are the primary key I want to retrieve a list of contacts for a given member. The members table needs to be joined in order to get the members name. I'm having trouble retrieving the correct name as the member_id to join on could either be member_id or contact_member_id depending upon who added who. So, somehow I need to JOIN on member_id if its not equal to the given member id or JOIN on member_contact_id if thats not equal to the given member_id. So far, I have this: Code: SELECT m.firstname, m.lastname, mc.*, mb.business_id, mb.company FROM members m, members_contacts mc LEFT JOIN members_business mb ON mb.member_id = mc.contact_member_id WHERE ( mc.member_id = $memberid OR mc.contact_member_id = $memberid ) I need to add something to the where clause above to get the correct name or perhaps something completely different. Any ideas Many
VIEWS ON THIS POST

161

Posted on:

Thursday 25th October 2012
View Replies!

3 table join in a php function help?

Hi , I wrote this function a while ago and it works fine but now I want to make it better by moving the query out of the while loop because I think this is causing alot of mysql overhead. If you can give me some idea as to how I would do this or even just some suggestions that would be great. Also any ideas on how to better write the code in general is greatly appreciated. Well here is the code. Code PHP: function GetGalleries ($user_id) { //this gets the categories that belong to the current user $get_cats = "SELECT id, user_id, cat_name, cat_desc from cats where user_id = '$user_id'"; $cats_res = mysql_query($get_cats)or die(mysql_error()); $num_of_cats = mysql_num_rows($cats_res); while ( $category = mysql_fetch_array($cats_res) ) { $cats_id = $category['id']; $cats_name = stripslashes($category['cat_name']); //then this gets the category name and the first picture to use as the thumnail link on the page $get_cat = "SELECT cat_id, photo_id from cat_photo JOIN photos on cat_photo.photo_id = photos.id WHERE cat_id = ".$cats_id." ORDER by photos.date limit 1"; $cat_res = mysql_query($get_cat)or die(mysql_error()); $cat = mysql_fetch_array($cat_res); $pid = $cat['photo_id']; $cats[] = array( 'cat_id' => $cats_id, 'cat_name' => $cats_name, 'pid' => $pid); } //then it returns an array of the galleries return($cats); }
VIEWS ON THIS POST

156

Posted on:

Thursday 25th October 2012
View Replies!

LEFT OUTER is faster than INNER joins?

Maybe someone can explain this to me real quick. I have two identical queries, except that the join type changed. One uses inner joins: Code: SELECT OrderItems.product_id AS product_id , OrderItems.code AS product_code FROM Orders INNER JOIN OrderItems ON Orders.id = OrderItems.order_id INNER JOIN Products ON OrderItems.product_id = Products.id WHERE Orders.orderdate >= UNIX_TIMESTAMP(DATE_SUB(NOW(), INTERVAL 24 HOUR)) AND Products.active = 1 GROUP BY OrderItems.product_id and the other uses the basic LEFT OUTER JOIN: Code: SELECT OrderItems.product_id AS product_id , OrderItems.code AS product_code FROM Orders LEFT OUTER JOIN OrderItems ON Orders.id = OrderItems.order_id LEFT OUTER JOIN Products ON OrderItems.product_id = Products.id WHERE Orders.orderdate >= UNIX_TIMESTAMP(DATE_SUB(NOW(), INTERVAL 24 HOUR)) AND Products.active = 1 GROUP BY OrderItems.product_id When I run the two queries on the same data (using phpmyadmin), the left outer joins only take 0.12-0.13 seconds whereas the inner joins take a whopping 2.5+ seconds. I kinda thought that an inner join would be faster. I'm still bad with determining join types.... Is there a fundamental reasoning for this that I am missing
VIEWS ON THIS POST

132

Posted on:

Thursday 25th October 2012
View Replies!

Using a left join with 2 values linked to the same tables

, I have a table that has two values, author_id and approver_id. Both of these refer to the value id in my users table. Is it possible to use a left join and have the query get the author and approver name (also in the users table) Or will I have to use multiple queries (Can't use nested selects, this particular server is still running MySQL3 -sigh-).
VIEWS ON THIS POST

197

Posted on:

Thursday 25th October 2012
View Replies!

join vs. inner join vs. implied join = different results ??

I SUM() only on the order table in all queries below. Here's a set of queries that I thought would/should yield the exact same results: QUERY 1: SELECT COUNT( o.orderID ) FROM order o WHERE DATE( o.orderDATE ) = '2007-01-04' AND o.orderSTATUS = 300 yields 161 QUERY 2: SELECT COUNT( o.orderID ) FROM order o LEFT OUTER JOIN credit_card cc ON o.orderID = cc.orderID WHERE DATE( o.orderDATE ) = '2007-01-04' AND o.orderSTATUS = 300 yields 175 QUERY 3: SELECT COUNT( o.orderID ) FROM order o, credit_card cc WHERE o.orderID = cc.orderID AND DATE( o.orderDATE ) = '2007-01-04' AND o.orderSTATUS = 300 yields 157 ANY ideas why they are different
VIEWS ON THIS POST

200

Posted on:

Thursday 25th October 2012
View Replies!

Self join (?) to get data for multiple users from one table

I am trying to generate a report containing ebay feedback scores for multiple users. Here is the database structure for the table that contains the scores: Code: TABLE: feedback identities_id int(10)
VIEWS ON THIS POST

139

Posted on:

Thursday 25th October 2012
View Replies!

Simple left join

This should be obvious but I am at a loss at the moment. Why does this query return an error Code MySQL: 1054 - Unknown column 'd.products.id' in 'on clause' SELECT p.products_id, p.manufacturers_id, d.products_name FROM products as p LEFT JOIN products_description as d ON (p.products_id=d.products.id) WHERE p.manufacturers_id = '10'
VIEWS ON THIS POST

139

Posted on:

Thursday 25th October 2012
View Replies!

Help with structure - primary key - inner join (i think)?

version 4.1 / using php admin Hi all I basically have a no. tables storing photographs and need to understand how I can get this structure right I think a inner join is the right way What I want is to be able to select the 'date' and 'photo_set' and display all the photos What I have so far: gallery_information - date / date, primary key - title / varchar 90 photo_information - photo_id / primary key - photo_set gallery_photo_relation - date - photo_set
VIEWS ON THIS POST

348

Posted on:

Thursday 25th October 2012
View Replies!

Inner join going wrong

I have this sql Code: SELECT P2.product_Name , P2.product_ID , R3.sec_category_id , R3.sec_product_id , R2.prdt_scnd_rel_ScndCat_ID FROM tbl_secondary as R1 INNER JOIN tbl_products as P2 ON P2.product_ID = R1.sec_product_id INNER JOIN tbl_secondary as R3 ON R3.sec_category_id = R2.prdt_scnd_rel_ScndCat_ID INNER JOIN tbl_prdtscndcat_rel as R2 ON P2.prdt_scnd_rel_ScndCat_ID = R3.sec_category_id WHERE R2.prdt_scnd_rel_Product_ID = '#url.prodid#' It gives me: Unknown column 'R2.prdt_scnd_rel_ScndCat_ID' in 'on clause' This is definately the correct column name!
VIEWS ON THIS POST

121

Posted on:

Thursday 25th October 2012
View Replies!

Outer join where there is no relationship?

Is an Outer join where there is no relationship between the two tables being joined possible I am using Access and have 3 tables. I have a query as follows :- SELECT F1, F2 FROM Tbl_A WHERE Tbl_A.ID = (SELECT Tbl_B.TblA_ID FROM Tbl_B WHERE Tbl_B.F1 = 'xxxxxx') I want to join on another table, Tbl_C and get the value of a field, Tbl_C.F1, however there is no relationship between Tbl_C and any other table, and I want to get the Tbl_C.F1 value even if there is no value returned from the other part of the query. So I basically want one row that consists of Tbl_A.F1,Tbl_A.F2,Tbl_C.F1 Is what I want to achieve possible
VIEWS ON THIS POST

102

Posted on:

Thursday 25th October 2012
View Replies!

Quick question regarding joins

When doing multiple left joins in one table: for each join, which table is considered to be the "left" table (ie: all results must be returned).
VIEWS ON THIS POST

239

Posted on:

Thursday 25th October 2012
View Replies!

MS Access SQL Query nesting problem with joins

I hope this is placed in the right place. Basically I have a lookup table in my access database. I'm refering to it 4 times, so I need to do 4 inner joins to the same column. I have it working with 2, but there seems to be a nesting problem (I don't know how to nest) with more than two statements, any tips appreciated. This works Code: SELECT p.Name, l.lookup_value, l2.lookup_value FROM ( project AS p INNER JOIN lookup AS l ON l.lookup_id=p.Status ) INNER JOIN lookup AS l2 ON l2.lookup_id=p.Health This doesn't work: Code: SELECT p.Name, l.lookup_value, l2.lookup_value FROM ( project AS p INNER JOIN lookup AS l ON l.lookup_id=p.Status ) INNER JOIN lookup AS l2 ON l2.lookup_id=p.Health INNER JOIN lookup AS l3 ON l3.lookup_id=p.Stage
VIEWS ON THIS POST

213

Posted on:

Thursday 25th October 2012
View Replies!

SQL and IF Statement

I need some help getting the following SQL statement working. What is suppose to happen in the IF is to check there is a seller_default_address_id that matches one in the address_book_id Code: IF can_seller_profiles.seller_default_address_id= address_book.address_book_id = NULL THEN SELECT can_seller_profiles.seller_customers_id, address_book.entry_company AS billing_compnay, address_book.entry_firstname AS billing_first_name, address_book.entry_lastname AS billing_last_name, address_book.entry_street_address AS billing_entry_street_address, address_book.entry_suburb AS billing_entry_suburb, address_book.entry_state AS billing_entry_state, address_book.entry_city AS billing_entry_city, address_book.entry_postcode AS billing_entry_postcode, address_book.can_phone_1 AS billing_can_phone_1, address_book.can_phone_2 AS billing_can_phone_2 FROM can_seller_profiles , customers , address_book WHERE can_seller_profiles.seller_customers_id = '1' AND customers.customers_id = can_seller_profiles.seller_customers_id AND address_book.customers_id = customers.customers_default_address_id ELSE SELECT can_seller_profiles.seller_customers_id, address_book.entry_company AS billing_compnay, address_book.entry_firstname AS billing_first_name, address_book.entry_lastname AS billing_last_name, address_book.entry_street_address AS billing_entry_street_address, address_book.entry_suburb AS billing_entry_suburb, address_book.entry_state AS billing_entry_state, address_book.entry_city AS billing_entry_city, address_book.entry_postcode AS billing_entry_postcode, address_book.can_phone_1 AS billing_can_phone_1, address_book.can_phone_2 AS billing_can_phone_2, address_book.entry_company AS shipping_compnay, address_book.entry_firstname AS shipping_first_name, address_book.entry_lastname AS shipping_last_name, address_book.entry_street_address AS shipping_entry_street_address, address_book.entry_suburb AS shipping_entry_suburb, address_book.entry_state AS shipping_entry_state, address_book.entry_city AS shipping_entry_city, address_book.entry_postcode AS shipping_entry_postcode, address_book.can_phone_1 AS shipping_can_phone_1, address_book.can_phone_2 AS shipping_can_phone_2 FROM can_seller_profiles , customers , address_book WHERE can_seller_profiles.seller_customers_id = '1' AND customers.customers_id = can_seller_profiles.seller_customers_id AND address_book.customers_id = customers.customers_default_address_id END IF
VIEWS ON THIS POST

48

Posted on:

Monday 29th October 2012
View Replies!

Finding endpoints of sub-ranges

Hi , I have the following table: seq_gb: seq_id int unsigned not null primary key, gb_id varchar(15) not null unique gb_id is an identifier assigned by an outside service. seq_id is my internal identifier for a sequence object. I submit my sequences in batches to the outside service. So each batch has a continuous series of gb_id's. However, the entire list of gb_id's is not continuous. I'd like to identify continuous ranges of gb_id's by their end points when I select a set of seq_id's. example data: Code: seq_id gb_id 1 ES788184 2 ES788185 3 ES788186 4 FA100103 5 FA100104 6 FA100105 Deed Results: Code: start end ES788184 ES788186 FA100103 FA100105 I appreciate your help. Cheers, Jen
VIEWS ON THIS POST

41

Posted on:

Monday 29th October 2012
View Replies!

Return ordered by sum of secondary table

I want to order by "amount" where amount is a column in a "grant" table PLUS ANY amounts in a "supplements" table which references that grant. For example (very dumbed down): Grants table ID | amount -------------- 1 | 500 2 | 600 3 | 400 Supplements table grant_ID | amount ------------------ 1 | 100 1 | 300 3 | 250 The query should return the grant_ID by order of the TOTAL amount of the original grants plus all relevant supplements to that grant: i.e: Query Result (echo): Grant_ID | total ------------------ 2 | 600 3 | 650 1 | 900
VIEWS ON THIS POST

95

Posted on:

Monday 29th October 2012
View Replies!

Adding up number of hours

Ok I have a diary table that looks like this did | dcompanyid | duid | ddate | dtime | dtimefinish did = p key dcompanyid = the companies unique id duid = the users unique id ddate = date format dtime = time format dtimefinish = time format I want to add up the number of hours in a particular day, or week an employee works for. so if he worked betweek 9 and 12, 12.30 and 3, 4 and 7 he would work 8.5 hours. I can do this using php and mysql but was wondering if it can be done soley in mysql.
VIEWS ON THIS POST

81

Posted on:

Monday 29th October 2012
View Replies!

What does show_db_priv do?

I find that if I deny show_db_priv to a mysql user, that user can still execute the command: Code: show databases; What can that user not do if he is denied the show_db_priv
VIEWS ON THIS POST

184

Posted on:

Monday 29th October 2012
View Replies!

Newbie insert record help

I have a table "brokers" with a field in the table "value_votes", I want to add new values to the "value_votes" field with the new number adding to the existing number and not creating a new row with the number. Apols if I am too vague with my question. xtianjs
VIEWS ON THIS POST

106

Posted on:

Monday 29th October 2012
View Replies!

Put 0 value when empty or null is returned

Hi anyone , please help - searched forum to no avail I have three columns say A , B C - when I do a query say select (sum( a ) sum(b) + sum(c)) as totl where error_code = x - at times based on the error_code there is no error hence the query returns no result which means I do not even see a column / field name with blank. can you show me a case statement I can use to say case when ( sum a,b & c ) = nothing THEN '0' ELSE sum( a,b & c) END as "at_least_show_0"
VIEWS ON THIS POST

83

Posted on:

Monday 29th October 2012
View Replies!

Help: Not unique table/alias Error

Hi and girls, I'm getting the following error and I'm puzzled as to what is causing it. As far as I can see I'm aliasing the table names correctly, but why is it complaining about p2c PHP Code: 1066-Notuniquetable/alias:'p2c' PHP Code: selectcount(distinctp.products_id)astotalfromproducts_typeaspt,categoriesascleftjoinproducts_to_categoriesasp2conp2c.products_id=p.products_idleftjoinp2conp2c.categories_id=c.categories_idleftjoinproductsponp.products_type_id=pt.products_type_idleftjoinproducts_descriptionpdonpd.products_id=p.products_idleftjoinmanufacturersmonm.manufacturers_id=p.manufacturers_idleftjoinspecialsassons.products_id=p.products_idleftjoincategories_descriptionascdonc.categories_id=cd.categories_idleftjoincategories_descriptionascd2oncd2.categories_id=c.parent_idleftjoincategories_printer_typesasc2pttonc2ptt.categories_id=c.categories_idleftjoinprinter_typesaspttonptt.printer_types_id=c2ptt.printer_types_idleftjoinproducts_coloursaspconpc.products_id=p.products_idleftjoincoloursascloncl.colours_id=pc.colours_idwherep.products_status='1'andpd.language_id='1'and(concat(cd2.categories_name,'',cd.categories_name)like'%test%')
VIEWS ON THIS POST

92

Posted on:

Monday 29th October 2012
View Replies!

Order

I have a table with $uid. When you login you are assigned your $uid in session, I am building drop down menu with s names and uid as values. my sql is simple PHP Code: $user=mysql_query("SELECTloguid,lognameFROMlogin \t\t\t\t\t\tWHERElogcompanyid=$companyid \t\t\t\t\t\tANDloguid=$uid"); however I want whoever has logged in to be at the top, ie. 2 Jason 3 Tom 4 Richard is my table, if I am Jason and logged in I want me to be at the top, So I figured this could be done in sql, ORDER BY (not idea) any ideas
VIEWS ON THIS POST

51

Posted on:

Monday 29th October 2012
View Replies!

mySQL saving help

, Im using mySQL 4.0.17. I have a piece of JDBC code that sends querys to the mySQL server to create a table add values to the tables etc. My problem is saving this information to a file so that when the sql server is disconnected or computer is restarted a txt file or something can be loaded to get me back to where i was when the restart happened. There are a few sites i have found that talk about saving, but most of them are talking about mySQL and PHP. Im just using mySQL not PHP or access. can anyone help
VIEWS ON THIS POST

265

Posted on:

Thursday 15th November 2012
View Replies!

wildcard around a 'word entered by a user'

In the same database as my previous thread I am not sure if it is possible to do this. Using the 'Message' field in my Access 2002 database contains messages from users from an online chat conversation. I would like the user to enter a word eg 'ontology' When this query has been created it does not work as there are other words in the same field. If I create the query saying : SELECT Message FROM Message_Table WHERE Message LIKE *ontology* THIS WORKS .. but the query should be data independent, I need the user to specify the word and then add wildcards around it to select the correct message, otherwise the query returns nothing. Any ideas
VIEWS ON THIS POST

80

Posted on:

Tuesday 20th November 2012
View Replies!

Access to MySQL .... what to look out for?

I am planning to add the possibility to use MySQL with my own E-Commerce and CMS script, but before I begin I was wondering if anybody has some tips to do it as fast and good as possible. I am thinking of things I should be carefull of. I am now using Access and I am noticing that access is not the perfect database if SQL queries get more complicated.
VIEWS ON THIS POST

139

Posted on:

Tuesday 20th November 2012
View Replies!

Query is adding up things that are not there

my query: Code: SELECT Engineer, SUM(HoursSpent) AS Total FROM tJob WHERE theDate BETWEEN #01/08/2006# AND #31/08/2006# AND Engineer = 2 GROUP BY Engineer now there is nothing in my db for August\t\t yet its getting a total of 27\t anybody know what is going on there
VIEWS ON THIS POST

65

Posted on:

Monday 26th November 2012
View Replies!

SQL statement to retrieve last two rows

! I am writing a program in vb.net ! Using an oledbdataadapter i generate a dataset and display dataset records in a datagrid The user will be able to add new records to this datagrid which will update the database(access) upon the click on the update BUTTON when the user clicks update i get the number of row added if there are any now what i want to do is retrieve that last rows added from the same table! I.E. if there were two rows added to the datagrid, then i want to retrieve these two rows later for a reason! Is there an SQL statement to retrieve the last two records (rows) from a table in Access pls help
VIEWS ON THIS POST

132

Posted on:

Monday 26th November 2012
View Replies!

T-SQL and Stored Procs

Originally posted by : Paul (pwhite@yahoo.com)i'm trying to do a query where one of the selected fields is the result of a stored procedure. The inputs of the stored procedure are one of the returned values from the query... confused yet\texampleI have the stored proced my_sp @in1I have a query that needs to work something like thisSelect A, B, C, D = ( exec @my_sp A )From SomeTableanyhow, this doesn't seem to work... the stored procedure is far too complex to just embed in the select clause... is there anyway to do this\t
VIEWS ON THIS POST

80

Posted on:

Monday 26th November 2012
View Replies!

retrieve emails and direct to db

Originally posted by : punu (punz52@hotmail.com)I want to retrieve emails from my mail server and direct them in to access under different heads such as Subject, message etc. please help
VIEWS ON THIS POST

153

Posted on:

Monday 26th November 2012
View Replies!

Trigger

. I need to create trigger that will run after select. I wrote this Code: DROP TRIGGER IF EXISTS `article_after_select`; DELIMITER $$ CREATE TRIGGER `article_after_select` AFTER SELECT ON `article` FOR EACH ROW BEGIN /*SOME ACTION HERE*/ END; $$ DELIMITER ; I need when article selected, update view count. But I can't create trigger for SELECT.
VIEWS ON THIS POST

103

Posted on:

Friday 28th December 2012
View Replies!