Nested links table

I'm actually totally lost and confused at this.
In the past I have created a table for nested link's with a table with a PARENT ID and CHILD ID.

But now it's broken down into table, like CATEGORY ID and SUBCATEGORY ID.
That bit is fine, but I get stuck with this part, SOME subcategory id's have sub-subcategory id's.

But what is the best way to create this these tables to ensure they run at their best and all data is brought back how you want it

Posted On: Friday 12th of October 2012 03:24:12 AM Total Views:  117
View Complete with Replies

Related Messages:

Query and compare first 8 characters in two tables/fields   (73 Views)
I'm comparing two tables and need to compare the first 8 characters of one field in table A to the first 8 characters in another field in table B. So instead of where 'John Smith' = 'John Smith' it would compare where 'John Smi' = 'John Smi' I know I've done this before but can't find a good reference. It's something like this when calling a query within a WHILE loop: SELECT * FROM blah WHERE FID = '".$row['FID']."' AND LEFT('TRACK', 8) = LEFT('".$row['TRACK']."',8) Am I close
PHP-DB - Delete from multiple tables with 1 query   (71 Views)
im creating a member post section. The system has a few tables. I want to make a delete query that deletes the rows from the 3 tables where the post_id is the number of the post. ie; 1, 2, 3, 4 etc... I would make 3 nested queries but im worried it will delete from 1 and not the other. Thats why I prefer to have it all in 1 query.
Tricky table design   (39 Views)
I have 3 shipping types: UPS USPS FedEx I have 3 ranges: Under $25 $25-$50 $50-$100 And I have a price for each. So... +--------------+----------+-----------+------------+ |..range/type..|....UPS...|...USPS....|....FedEx....| +--------------+----------+-----------+------------+ |...up to $25...|...10.00...|....5.00....|....15.00....| +--------------+----------+-----------+------------+ |.....26 - 50....|...15.00...|....8.00....|....20.00....| +--------------+----------+-----------+------------+ |....51 - 100...|...20.00...|...10.00....|....30.00....| +--------------+----------+-----------+------------+ I want the shipping type to be as dynamic as possible, but I can only think of one way to handle this: 1 table with a description of the service and an id, and 1 table for each service (with id from the first table), range, and cost. But this just ain't right: +---------+----------+-----------+------------+ |...type...|.....low....|....high....|.....cost.....| +---------+----------+-----------+------------+ |....-1.....|.......0.....|....25.00..|....10.00....| +---------+----------+-----------+------------+ |.....1.....|...26.00...|....50.00...|....15.00....| +---------+----------+-----------+------------+ |.....1.....|...51.00...|...100.00..|....20.00....| +---------+----------+-----------+------------+ |.....2.....|......0......|...25.00....|.....5.00....| +---------+----------+-----------+------------+ |.....2.....|...26.00...|...50.00....|.....8.00....| +---------+----------+-----------+------------+ |....etc....|.....etc....|....etc......|.....etc.......| +---------+----------+-----------+------------+ How else can I build normalized relational tables to do this
Need help getting right data from table   (79 Views)
Example of data is as follows... Question | Order | Answer | Valid 102 | 1 | 1 | 1 104 | 2 | 1 | 1 105 | 3 | 1 | 0 106 | 4 | 0 | 1 108 | 5 | 1 | 1 104 | 6 | 0 | 1 I need to... select Question where Answer = 1 and Valid = 1 However, it should only base the WHERE clause on entries with the highest order number. For example, in the case of Question 104, which is listed twice... nothing should be returned because the Question 104 with the higher order number (6 vs 2) does not pass the WHERE Answer=1 and Valid=1 clause. The Query should return... 102 | 1 | 1 | 1 108 | 5 | 1 | 1
Complex select query with nested if constructs, query insert and query delete.   (106 Views)
I have this mysql query with maybe clear comments. Please can you help me to correct it I think that it is a comprehensible code but it requires many corrections. and also forgive me for my bad english. this is my query with the commented code: Code MySQL: SELECT log_uname IF ( log_uname = 'new_username', /* if true that already exists but it is not a verified username */ ( SELECT flag_verified, log_uname /* nested if */ IF ( (TIMESTAMPDIFF(MINUTE, NOW(), SELECT date_registered FROM users WHERE flag_verified = 0 AND log_uname = 'new_username')
Show a list of users with advanced calculations inside query (nested queries?)   (69 Views)
I have a league where players can signup to scheduled online games. When they sign up they are presented on a list which I want to be sorted in any way possible, e.g. online name, league rank, number of games participated in before, average score etc. I can present the list without problems, but my problem is that to sort by how many games they have participated in or their average score I have to query the database for EACH USER and then have the result displayed. So far I have done this by first extracting a sorted list and then doing some other queries that output e.g. average score. But I cannot sort it this way. So I have this basic query: Code: SELECT civs.*, civ_user.* FROM ( SELECT @rownum:=@rownum+1 AS no, civ_signups.* FROM ( SELECT @rownum:=0 ) r, civ_signups WHERE civ_signups.id_battle = $_GET["send_battle_id"] AND signup_status = 1 ORDER BY no_signed_up ASC ) civs JOIN civ_user ON civs.id_user = ORDER BY $sort $method This returns the chronological order of signups so I know who would be reserve if too many has registered. But my question is now: How do I include extra queries so I can calculate a range of different values for EACH PLAYER (civs.id_user / An example of the next thing I want to include ($players refer to the first query): Code: SELECT SUM(score) AS league_points FROM civ_scores WHERE id_user = $players["id_user"] AND id_league = $battle_data["idleague"] GROUP BY id_user This returns the overall league score in the specific league. But I cannot easily order this query as it is the query before that is the overall query. I have thought about using a SELECT name.*, @user = FROM civ_user, SELECT (...query...)name - inside the query, but it does not seem to use. I REALLY need some help with this. I will provide extra information if needed.
Multiple parents in nested set hierarchy   (106 Views)
I have a database of destinations that I need to store in some hierarchical way and the best solution I've found is the nested set hierarchy. I need to be able to search by all descendants or all ancestors and since I don't know how to make a MySQL recursive query ( I don't think one can make a recursive query in MySQL ) - and not a recursive php method - I thought that this would be the best solution. However, there are times when I need to have multiple parents to a destination. In my example results below I have 'Peru' as a child of the 'Amazon' and 'South America', and 'Amazon' is a child of 'South America'. This works for when I need to find all descendants or ancestors, but I haven't found a good way to getting just the immediate parents/ancestors or the immediate children. I haven't even started to work with adding and removing records from the nested set yet Any guidance or suggestions would be appreciated. Code: -- Get all Ancestors SELECT d.`NAME`, a1.`lft`, a1.`rgt` FROM `destination` AS d , `destination_assoc` AS a1 , ( SELECT * FROM `destination_assoc` WHERE `NAME` = 'Cusco' ) AS a2 WHERE a1.`lft` < a2.`lft` AND a1.`rgt` > a2.`rgt` AND d.`NAME` = a1.`NAME` ORDER BY a1.`lft` -- Get immediate Ancestors -- Get all Descendants SELECT d.`NAME`, a1.`lft`, a1.`rgt` FROM `destination` AS d , `destination_assoc` AS a1 , ( SELECT * FROM `destination_assoc` WHERE `NAME` = 'Earth' ) AS a2 WHERE a1.`lft` > a2.`lft` AND a1.`rgt` < a2.`rgt` AND d.`NAME` = a1.`NAME` ORDER BY a1.`lft` -- Get immediate Descendants Code: +---------------+-----+-----+ | NAME | lft | rgt | +---------------+-----+-----+ | Earth | 0 | 27 | | North America | 1 | 4 | | Alaska | 2 | 3 | | South America | 5 | 26 | | Amazon | 6 | 11 | | Peru | 7 | 10 | | Cusco | 8 | 9 | | Ecuador | 12 | 21 | | Galapagos | 13 | 20 | | Bartolome | 14 | 15 | | Isabela | 16 | 17 | | Santa Cruz | 18 | 19 | | Peru | 22 | 25 | | Cusco | 23 | 24 | +---------------+-----+-----+ 0[Earth]27 / \ 1[North America]4 5[South America]26 / / | \ 2[Alaska]3 / | \ / | \ 6[Amazon]11 | 22[Peru]25 / 12[Ecuador]21 \ 7[Peru]10 | 23[Cusco]24 / 13[Galapagos]20 8[Cusco]9 / | \ / | 18[Santa Cruz]19 14[Bartolome]15 | 16[Isabela]17
Alphbetical links   (118 Views)
I'm sure this is a common occurrence, so there must be a common approach... I have a table with a column that contains a word. I'd like to have a row of 26 letters representing the first letter of the word. If the table contains any words that begin with that letter, the letter will link to a page that queries the db and presents all those words that begin with that letter. A B C D E F G H I J ... So, the question how to populate the row of letters. Most obviously, I could run 26 queries to see which letters are represented. Is this the best or only way
Cannot figure out statement with multiple nested joins   (95 Views)
Hi folks, I am trying to make a nice tidy SQL statement using joins which does the following: Code: SELECT functionNumber FROM SystemFunction WHERE functionId in (SELECT functionId FROM SystemRoleFunction WHERE roleId in (SELECT roleId FROM SystemUserRole WHERE userId = (SELECT userId from SystemUser where userName = 'cmiuser'))) I've actually tried a bunch of different statements with joins, I tried taking out the final select statement for simplicity and doing the following "Statement Attempt One" Code: SELECT sf.functionNumber FROM (SystemRoleFunction srf JOIN SystemUserRole sur USING (roleId)) INNER JOIN SystemFunction sf USING (functionId) WHERE sur.userId = 109 "Statement Attempt Two" Code: SELECT sf.functionNumber FROM SystemFunction sf JOIN (SystemRoleFunction srf JOIN SystemUserRole sur ON sur.roleId = srf.roleId) AS mj ON mj.functionId = sf.functionId WHERE sur.userId = 109 but they both died with a SQL syntax error - and I'm not even making the the full statement in those examples! Yeek. My MySQL version is 4.1. Can anyone point me in the right direction
Remove nested MAX aggregation from join   (95 Views)
. I have a query here that's giving me a lot of trouble and I would appreciate some help, if possible. I have a table that contains forum posts. These are the important attributes in the said table: "id" is the primary key "thread" contains the thread number - posts with the same thread number belong to the same thread "flag_head" is an auxiliary boolean value used to mark the first post in a thread "ownerid" is a foreign key for the user accounts table "timestamp" contains the date/time at which the post was made. In order to generate the list of threads in the forum, I need a select query that returns not only all the first posts in the threads (this can be accomplished with a simple query for flag_head = 1) but also the timestamp and ownerid of the LATEST post in each thread - not only for display purposes but also because I want the results sorted by the timestamp of the latest post, so threads with recent replies show first (like in almost all forum systems). I can return the data I need if I select the head posts joined with a second copy of the table where id IN (SELECT MAX(id) ...) - note that the maximum id is always the latest post in the thread, because this forum system does not support any method for inserting new posts in the middle of a thread. While this works perfectly in theory, in practice I have observed that with only 100 posts in the table the query takes over 13 seconds to run in the server while fully using its 3ghz processor, and this is only with one single user in the forum. When I removed the nested SELECT MAX(id), the query time dropped to 0.08 seconds. Unfortunately, it no longer returns all the data I need. So my question is this - how can I, without using any nested query, return for each thread all the fields of the head post plus the timestamp and ownerid of the latest reply Please feel free to ask for details if you need.
Advice on links database structure   (243 Views)
I'm creating a 'Favourite Links' function on our Intranet, using PHP/MySQL. I'd really like some advice on the most efficient way of setting this up. Users login to the site and are either identified by a cookie or by a session. Their favourite links (ID, Title, URL, Description) will be displayed on their personalised homepage. We have about 230 users. I could set up a a table to hold the links for each user but that seems excessive. I could create the one table of links but with a field that records the owner/users ID but perhaps that would be a very memory-hungry query once the table gets really large. If anyone's had experience with this and can share some advice, that'd be great. Nick
nested select on same table   (79 Views)
hello I'm stuck with an SQL query. i want to fetch 16 username and avatar from my table where 8 entries should follows one condition and remaining 8 follow another one. more descriptively, 8 avatar- selected images by user. 8 avatar- default one. i was trying something like this i know this is totally wrong. please give me some suggesion. thank you. Code MySQL: ( SELECT username, avatar WHERE avatar != 'images/avatar/avatar.gif,83,83' ORDER BY id DESC LIMIT 0 , 8 ) UNION ( SELECT username, avatar FROM author WHERE avatar = 'images/avatar/face_9.gif,83,83' ORDER BY id DESC LIMIT 0 , 8 )
optimizing queries using nested set model   (82 Views)
i'm experimenting with using the nested set model of organizing and displaying my category information. i have over 3,000 categories and 40,000 products. but, when i test and EXPLAIN my queries via phpMyAdmin, i'm not noticing big improvements. (i've posted my table structure below). 1. My first question is; should the lft and rgt columns of the category table be indexed the article never says one way or the other but it seems like they should be indexed. 2. Another strange thing is that my query seems to be faster if there are more results in my query. For example: Showing rows 0 - 29 (10,747 total, Query took 0.0363 sec) Code MySQL: SELECT,, product.price, product.description, FROM test_products AS product, test_categories AS node, test_categories AS parent WHERE node.lft BETWEEN parent.lft AND parent.rgt AND = product.category AND = 'pc' LIMIT 0 , 30 Showing rows 0 - 2 (3 total, Query took 11.5648 sec) Code MySQL: SELECT,, product.price, product.description, FROM test_products AS product, test_categories AS node, test_categories AS parent WHERE node.lft BETWEEN parent.lft AND parent.rgt AND = product.category AND = 'pc_accessories_anti-glare-filters_display-anti-glare-filters' LIMIT 0 , 30 3. Lastly, if I run an EXPLAIN on both of the queries, they still seem to be processing all 44,000+ rows of products. Here are the results of the EXPLAIN of the previous 2 queries: Code: id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE parent ref PRIMARY PRIMARY 602 const 1 Using where 1 SIMPLE product ALL NULL NULL NULL NULL 44962 1 SIMPLE node ref PRIMARY,lft PRIMARY 602 func 1 Using where Code: id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE parent ref PRIMARY PRIMARY 602 const 1 Using where 1 SIMPLE product ALL NULL NULL NULL NULL 44962 1 SIMPLE node ref PRIMARY,lft PRIMARY 602 func 1 Using where And finally, as promised, here are my 2 test tables: Code MySQL: CREATE TABLE test_categories ( id varchar(200) NOT NULL, name varchar(100) NOT NULL, description mediumtext, lft int(11) NOT NULL, rgt int(11) NOT NULL, PRIMARY KEY (id), KEY parent (parent), KEY `name` (`name`), KEY lft (lft,rgt) ) CREATE TABLE test_products ( id varchar(50) NOT NULL default '', name varchar(200) NOT NULL default '', description mediumtext NOT NULL, price decimal(9,2) NOT NULL default '0.00', category varchar(200) NOT NULL default '', image varchar(255) NOT NULL default '', thumbnail varchar(255) NOT NULL default '', visible tinyint(1) NOT NULL default '1', PRIMARY KEY (id), KEY category (category), KEY order_sort_index (name,price), FULLTEXT KEY product_search (name,description) )
nested set get multiple paths   (86 Views)
Lets say we are using the nested set structure to store some categories. We can get the path of a category like: SELECT * FROM categories AS node INNER JOIN categories AS parent ON node.left_ BETWEEN parent.left_ AND parent.right_ WHERE = 7 ORDER BY parent.left_; However lets say we have a set of ID numbers like 3,4,5,6,23,434,453,22,34 how could we lookup all of the paths in one query and is this even possible Otherwise we would have to loop through them doing the above query N times where N is the number of ID's to lookup.
Calling links from a database   (339 Views)
Can anyone tell me how I links should be inputed into a MySQL database Also, how do I call the link in a web page I do not want the URL displayed, but want something like the example below done in HTML: Click here!
Problem with using nested-querry to achieve info   (64 Views)
, I'm a total newbie with SQL. There's one question I've spent a week on it but I can not get it done. So if someone could do me a big favour, please read this question :"For each keeper, show the number of cages cleaned by the keeper, show the average number of animals in the cages cleaned by the keeper and the total cost of the cages cleaned by the keeper." The data table is shown on the picture. i75.photobucket. com /albums/i297/lovebipbo/SIT103.jpg if someone write the code, plz tell me the idea of how it should be done like this. So I can learn and do some similar task myself Thank you in advance, HQ ps: sorry for the inconvenient. As a newbie I cannot post hyperlink. So plz join it and have a look.
Help with nested select?   (67 Views)
, this query works fine except for the Nested Select statement. Any idea on how to get this query into the bottom query, as its own column (SELECT count(id) as faults from sale_info where de_error='T' AND date(date_entered) = CURDATE() AND deid = Data_Entry) as Faults SELECT ss.deid as Data_Entry, COUNT(DISTINCT as Sales, COUNT(DISTINCT as Leads, rOUND((AVG(TIME_TO_SEC(TIMEDIFF(de_end_time, de_start_time)))/60),2) as Avg_Entering FROM sale_info AS ss INNER JOIN lead_info as ll ON = ll.sale_id WHERE (ll.type = 'web' or ll.type = 'axd') AND (ss.claimed NOT LIKE '%Y%') AND (ll.status = 'Entered') AND date(ss.date_entered) = date(CURDATE()) GROUP by ss.deid I tried doing it the way i thought would work - by just adding it into select, give alias, and run. however it takes literally MINUTES to execute. Overall, i just want the columns to be be, Data_entry, sales, leads, avg_entering, Faults. Ideas
Alternative to nested subqueries when accessing many-to-many table   (67 Views)
This is my first time posting a question. I have tried to follow all the posting rules but apologies if I have done anything wrong. I have a database with 3 tables. The table named listKeywords acts as a many-to-many relationship between the other two tables. [MYSQL]CREATE TABLE `lists` ( `listId` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT 'primary key', `title` varchar(200) NOT NULL, PRIMARY KEY (`listId`), ) INSERT INTO `lists` (`listId`, `title`) VALUES (1, 'firstTitle'), (2, 'secondTitle'), (3, 'thirdTitle'), (4, 'fourthTitle') CREATE TABLE `keywords` ( `keywordId` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `keyword` varchar(200) NOT NULL, PRIMARY KEY (`keywordId`), UNIQUE KEY `keyword` (`keyword`) ) INSERT INTO `keywords` (`keywordId`, `keyword`) VALUES (1, 'apple'), (2, 'orange'), (3, 'banana'), (4, 'grape'), (5, 'melon') CREATE TABLE `listKeywords` ( `listId` bigint(20) unsigned NOT NULL, `keywordId` bigint(20) unsigned NOT NULL, PRIMARY KEY (`listId`,`keywordId`) ) INSERT INTO `listKeywords` (`listId`, `keywordId`) VALUES (1, 1), (1, 2), (1, 3), (2, 1), (2, 2), (3, 4)[/MYSQL] So based on the data above, listId 1 has 3 keywords of 'apple', 'orange', 'banana' and listId 2 has 2 keywords of 'apple', 'orange' and listId 3 has 1 keyword of 'grape'. Now, I want to create a query that returns lists that have ALL of a selected set of keywords. E.g. I want to know which lists have 'apple' AND 'orange' keywords So if I run the following query, I should get back 'firstTitle' and 'secondTitle' [MYSQL]SELECT DISTINCT lists.title FROM lists, listKeywords WHERE lists.listId = listKeywords.listId AND lists.listId IN ( SELECT listKeywords.listId FROM listKeywords, keywords WHERE listKeywords.keywordId = keywords.keywordId AND keywords.keyword = 'apple' AND lists.listId IN ( SELECT listKeywords.listId FROM listKeywords, keywords WHERE listKeywords.keywordId = keywords.keywordId AND keywords.keyword = 'orange' ))[/MYSQL] This query is working correctly but I have two questions: a) I need to create queries that could look for lists that match up to 10 keywords and am worried that having 10 levels of nested subqueries won't be efficient. Is this correct b) Can anybody recommend a better or more efficient way of doing the same thing
Joins/nested queries help   (84 Views)
i'm having some difficulty with a mysql query which i am trying to write. I am writing a content management system from scratch, and all my sql is self-taught, so forgive me if this seems like a simple question. i have two tables: t1: CatID CatTitle 1 Water 2 Land 3 schools etc and a second table t2: AID Title Description 1 Sports centre this is a very... 2 Blackfriars centre this is a very... 3 Gym centre this is a very... I then have a 3rd table. this third table collects what categories (from table 1) have been assigned to which attractions (table 2). Each attraction can have multiple categories assigned to it. (as you can see below, attraction 3 has three categories assigned to it - categories 1, 2 and 7) t3: catID AttID 1 3 2 3 3 5 5 3 7 3 **** What I want to do is to select all the category ids and titles from the 'categories' table, except for any categories which have been already assigned to a specific attraction ID. **** I can see why the below would not work, but this is something like what I am attempting: SELECT t1.Cattitle, t1.CatID FROM t1 where CatID NOT IN (SELECT CatID from t3 where AttID = 3) I have tried joins too, but i'm not really getting anywhere with it. If anyone could point me in the right direction it would be much appreciated.
Access fields called into asp page--how do I make hyperlinks?   (101 Views)
Originally posted by : Austin ( am designing a webpage for a project in one of my classes. We are setting up a database using MSAccess for a movie theater and we are going to create a website that will show movie titles, listings, and capacity in the different theaters. I figured out how to call the movie titles into the page but I can't figure out how to get those movies to link to another asp page that just shows the listings for that movie. Ex. If Cast Away is a movie, I want it to be linked to Cast Away.asp which has its listings and so on. Can anyone help me out