2 tables one search


I have a problem, that effects my navigation system, I have three tables:

1 which lists all my product info



1 that is a contents map of all my navigation links



And one that sort of links the two


product_id (this is the same product_id the in the product_tbl)
product_link_id (this relates to one of the sub_id(number) in the navigation_tbl)

what i want to do is create a sql statement that when I have selected all the products I have, it will get all the links i need.

So i have a sql statement to get the products

select * from product_tbl which sport='football'

which will get all my products

but then I need to get the navigation ids relating to the products within that search from the category_tbl and then use that to get all the links from the navigation_tbl

Background is I have set it up like this as I need to add more than one category to each product and the old system I had was very processor heavy, so i am looking for a better way, and think this to be it.

Any advice would be appriciated, I am programming in asp and the DB is mysql.

Posted On: Sunday 11th of November 2012 09:01:27 PM Total Views:  172
View Complete with Replies

Related Messages:

to get data from two tables with one query   (141 Views)
is it possible to get data from two tables by one query in such a case: Code: mysql> describe Item; +----------------+--------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +----------------+--------------+------+-----+---------+----------------+ | id | bigint(20) | | PRI | NULL | auto_increment | | name_en | varchar(255) | | | | | | description_en | text | YES | | NULL | | | slug | int(11) | YES | | NULL | | +----------------+--------------+------+-----+---------+----------------+ and Code: mysql> describe Slug; +-------+------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------+------------------+------+-----+---------+----------------+ | id | int(10) unsigned | | PRI | NULL | auto_increment | | value | varchar(255) | | UNI | | | +-------+------------------+------+-----+---------+----------------+ Item table has got a foreign key (Item.slug) to Slug table ( the query shouldn't have where clause as i'd like to get the whole reultsets just limiting them by portions for pagination.
Getting the create statements of tables to save in text file.   (79 Views)
y'all. I have created some simple tables in mysql and i am wondering... is there any way of generating the create statements for the tables and saving them in a txt file. To make sense I want to know if mysql has any automated features that can output the create statements of any given table into a txt file.
joining two tables (I know, not again! but I can't find the answer)   (86 Views)
here we go - should be simple for you ! I have a products table and an images table. I want to select the top 4 products based on price and display the first image posted based on its unique ID. Can you give me an example of how this can be done in one SQL statement I am seeking a RS with 4 imagenames - one for each product. (Is this enough info to work with)
Get records that exist in only in one table and not in other linked tables   (87 Views)
I have 3 tables: 1. customer_order c 2. external_customer_order e 3. external_invoice_header h I want to get all the information in c for the orders that exist in c but do not exist in e and h (key for all records is order_id). Does anyone has a suggestion about how to do this
Update query join tables and update field   (116 Views)
I get the following error when I run the query below Msg 4104, Level 16, State 1, Line 1 The multi-part identifier "dbo.JOB40115_Cost_Actindx.CostCategorie" could not be bound UPDATE dbo.JOB40115 set dbo.JOB40115.CostCategorie = dbo.JOB40115_Cost_Actindx.CostCategorie SELECT JOB40115.ProjctID, JOB40115.SubLevel1, JOB40115.SubLevel2, JOB40115.CostCategorie, JOB40115.DepartementID, JOB40115.JOB_Cost_Index, JOB40115_Cost_Actindx.ACTINDX, JOB40115_Cost_Actindx.ProjctID AS Expr1, JOB40115_Cost_Actindx.SubLevel1 AS Expr2, JOB40115_Cost_Actindx.SubLevel2 AS Expr3, JOB40115_Cost_Actindx.DepartementID AS Expr4, JOB40115_Cost_Actindx.CostCategorie AS Expr5 FROM JOB40115 INNER JOIN JOB40115_Cost_Actindx ON JOB40115.ProjctID = JOB40115_Cost_Actindx.ProjctID AND JOB40115.SubLevel1 = JOB40115_Cost_Actindx.SubLevel1 AND JOB40115.SubLevel2 = JOB40115_Cost_Actindx.SubLevel2 AND JOB40115.CostCategorie = JOB40115_Cost_Actindx.CostCategorie AND JOB40115.DepartementID = JOB40115_Cost_Actindx.DepartementID
Joining multiple tables   (64 Views)
Okay I have a MAIN table with two smaller tables which are just look-up tables in Access (eg unique-id and description) I want to JOIN the two to the main table so instead of showing the id numbers in output, it shows the description. I can "inner join" okay with one smaller table but I can't get ALL three going properly. SELECT * FROM org INNER JOIN orgloc ON org.orgtown = orgloc.orglocid INNER JOIN orgtype ON orgtype.orgtypeid = org.orgsector; org is the main table. orgloc and orgtype are the two lookups.
retrieving values from multiple tables!   (106 Views)
! I have a three tables. 1. Material_catalog -------------------- MatID (pk) | MatName 2. Supplier_Catalog -------------------- SupID (pk) | SupName 3. Material_Cost ----------------------------------- MatID (pk) | SupID (pk) | UnitPrice I want a query to display this output: --------------------------- MatID | MatName | UnitPrice the material_cost table has a composite key. I know the supid, and i want it to find the matid and use THIS matid to find their respective name from their table (material_catalog). I know i that i can get multiple matids through: select matid from material_cost where supid='sup1' But i want a single query to find the matname from the materialcatalog table. So that i can display the result in a datagrind ( I would really appreciate if anyone would help me. Thank you Zeeshan
joining tables   (125 Views)
i need a query to include all records in TABLE1 and if TABLE1's primary key matched with TABLE2's primary key, then the information on TABLE2 will be attached else, the fields will display nothing. pls help...
connecting to 2 tables in a mdb   (109 Views)
Originally posted by : andrew ( until now I've successfuly connected to a table in a file mdb using the following code:------------------------------------------------------------sql="select * from users where cat like '%"&cat&"%' and city like '%"&city&"%' and title like '%"&key&"%'Or text like '%"&key&"%'"Set OBJdbConnection = Server.CreateObject("ADODB.Connection")OBJdbConnection.Open "driver={Microsoft Access Driver (*.mdb)};dbq=e:users .com .mdb"Set RS=OBJdbConnection.Execute(sql)%>-----------------------------------------------------------What I want to know is how can I connect to more than one table in a mdb in order to display information from both
Select * from multiple tables   (216 Views)
hi...i have 2 tables 'Respondent' and 'Respondent-Pref', with 'Respondent-Pref' table having a 1 to 1 relationship with 'Respondent' table....'Respondent-Pref' table has a FK called Resp-ID to link it with the 'Respondent' table...What I would like to know is if '1 Select * statement' can be used to retrieve all values from both the tables. Such as: ' Select * from Respondent, Respondent-Pref where Respondent-Pref.Resp-ID = Respondent.Resp-ID ' So my question is that will the above SQL statement work If it does, then in which order will the query return result And if it doesn't, then how can this be achieved any help would be much appreciated..
Get first row from duplicates and all other fields in joined tables   (79 Views)
Hi all,
Select multiple tables..   (89 Views)
PHP Code: details="SELECT weeklyreport.Supp_name,weeklyreport.PartNumber,weeklyreport.Description,cbom_filtered.comCode,cbom_filtered.UploadTime,supplier_feedback2.DateNowfromweeklyreport,cbom_filtered,fa,supplier_feedback2whereProjectName='"&pro&"'" error on the ProjectName is ambiguous... how do i select the value from those 4 tables based on the projectName
Random data from tables   (174 Views)
I have a query which will fetch datas from two tables and display it.I don't want to display all the values because there may be 1000s to 2000s of records.Is there a way that i can select a random number of records from the query say around 10% or 25% of records from that particular query.Please help.
Joining tables on different SQL Servers   (64 Views)
How would you go about joining two tables from different databases loaded on two different SQL Servers
query between 2 tables   (94 Views)
, how do i do a query between two tables where i want to get rows from table 2 that cannot be found in table 1. example table1 name age jim 12 jason 13 jack 14 table2 name age jim 12 jane 13 jack 14 jason 13 As u can see Jane is the extra one. How do i retrieve the record out
Connecting two tables for crystal report   (66 Views)
I have two tables in one Database I want to show this information in one crystal report using SQL . I can do it using access but I can't do in SQL database.Please let me know the way of creating crystal report.pls give a source code Thank you
ASP on ACCESS tables link to Oracle tables   (70 Views)
Originally posted by : Francois ( to use ASP on Microsoft Access tables link with oracle tables
Finding Unique records in multiple tables   (67 Views)
Total 3 tables in a database (Students, Results1, Results2) Results1 & Results 2 have more than 50000 records Now I want to compare ID of students (Students Table) to the Results1 and Results2 and show the records which are matching StudentID in Results1 & Results2 only. If the corresponding result of a particular field is not present in Result1 or Result2, it must print an empty value in the query results Students StudentID Fullname Results1 StudentID Fullname R1 R2 Results2 StudentID Fullname R1 R2 I want the results as Students.Student ID Students.Fullname Results1.R1 Results1.R2 Results2.R1 Results2.R2
Delete data in several tables   (74 Views)
I want to delete data in some tables. I have one major table and some below. I use the primary key as a reference (and is indexed) in the tables below. Must I first delete all data in the tables below before I delete the data in the major table or can I do it in 1 command line thx in advance...
Joining four tables   (57 Views)
hi i have four tables, given the sample data for the relevant field of the tables here under table company: company_id comp_parent_org_id 241 238 457 279 225 200 478 200 10 279 table representative: rep_id rep_parent_org_id 238 234 279 170 200 234 table mobile_unit : rep_id company_id msisdn creation_date 238 241 91944400000 1-Sep-08 279 457 91944400005 1-Sep-07 200 225 91944400010 10-Sep-08 200 478 91944400100 5-Jul-08 279 10 91944410100 5-Jul-08 table organization : organization_id name 234 chennai 170 kerala 238 al 279 da 200 sd now i want to join all these table to get the output as below table output required: between creation_date 1-jan-08 and 10-sep-08 in table mobile_unit name no of companies no of msisdns chennai 3 3 kerala 2 1 the organization table contains the name for all the ids in the table company, representative, etc name field should reflect the name of the distinct rep_parent_org_id companies column should count the number of company_id in company table w.r.t comp_parent_org_id (same as rep_id in representative table) which in turn should lie under the rep_parent_org_id for example here rep_parent_org_id 170 , its name is kerala, then under this id we have got 279 as the rep_id (same as comp_parent_org_id). with 279 as comp_parent_org_id we have got two company_id so no of companies =2 no of msisdns column rep_parent_org_id 170 , its name is kerala, then under this id we have got 279 as the rep_id. for this 279 rep_id , we have got one msisdn (i.e 91944410100) which satisfies the creation_date so count is 1 i tried in this way as per the answer from mehere for my previous question, but am not able to count companies select DISTINCT, count(mu.msisdn) FROM RTMS.ORGANIZATION O INNER JOIN RTMS.REPRESENTATIVE R ON O.ORGANIZATION_ID=R.PARENT_ORG_ID INNER JOIN RTMS.MOBILE_UNIT MU ON R.REP_ID=MU.REP_ID GROUP BY O.NAME i used the actual column names in my query but added the short form of table name to the parent_org_id in the examples i hav given for clear understanding. e.g in table company, representative the actual second column name is parent_org_id but i hav added comp_ & rep_ in front of that for clear understanding. (you should not assume the parent_org_id in other tables also same as here.) can anybody help me out