Selecting Records Belonging To Multiple Records Through Join Table
I have is 3 tables, 'categories', 'levels' and 'categories_levels', the 'categories_levels' table is a join table, with the `id`, `category_id` and `level_id` fields. Categories can belong to many different levels, so records in the join table might look like this (assuming that the category has an ID of 5)
id, category_id, level_id
1, 5, 1
2, 5, 2
3, 5, 3
So you can see that category 5 belongs to 3 different levels (1, 2, and 3).
On a web page, I've got a multiple select box, which allows a user to filter the categories depending on which levels they belong to. What I need a query to do is use that join table to find all categories which belong to the selected levels. Now for the tricky bit. Only the categories which belong to every single selected level should be displayed. So in the example above, if I select level 1, then category 5 will be displayed, but if I select levels 1 and 4, then category 5 should not be displayed, because although it does belong to level 1, it doesn't belong to level 4 as well.
View Complete Forum Thread with Replies
Sponsored Links:
Related Messages:
All Records From Table A - All Records From Table B - Join Alike Records
I am by no means a SQl Jedi as will be apparent by my question, but I can usually figure out a select statement on my own. I have one today though that really has me stumped. I am working in MySQlL 5. In My first select statement I get all my records from Table B SELECT `table_A`.`ITEM`, `table_A`.`DECSCRIPTION`, `table_A`.`UM`, `table_A`.`PHASE`, `table_B`.`Qty`, `table_B`.`Calc` as calculated FROM `table_A` Inner Join `table_B` ON `req_itemlist`.`ITEM` = `table_B`.`ItemID` In my second statement I get my records that match in this case phase 401 in Table B and all my Table A records for phase 401. SELECT `table_A`.`ITEM`, `table_A`.`DECSCRIPTION`, `table_A`.`UM`, `table_A`.`PHASE`, `table_B`.`Qty`, `table_B`.`Calc` as calculated FROM `table_A` Left Outer Join `table_B` ON `req_itemlist`.`ITEM` = `table_B`.`ItemID` Where table_A.PHASE In ('401' ) Now I need to combine the Data of both recordsets. I need EVERYTHING in Table B, but I also need All Table A records that match the phase selection.... Can I write this one query or do I need to use a Temp table?
View Replies !
View Related
Selecting Records Containing Multiple Values
I want to query a database for records with which the field 'network_letter' contains G,R,M, and Q and display those results back. Is there an easier way than doing the following: $query1 = mysql_query("SELECT * FROM global_pops WHERE network_letter='G'"); $query2 = mysql_query("SELECT * FROM global_pops WHERE network_letter='R'"); $query3 = mysql_query("SELECT * FROM global_pops WHERE network_letter='M'"); $query4 = mysql_query("SELECT * FROM global_pops WHERE network_letter='Q'"); and then merging those results into one variable and printing them?
View Replies !
View Related
Join Records Form Same Table
I have results from a mySQL database that I am exporting to a CSV. These records are sales form an eCommerce store (ZenCart). What happens is that attributes for each part of an order make a new row for each attribute. Like this: .....
View Replies !
View Related
How To Find Records That Are NOT Referenced In A Join Table?
Table structure... CREATE TABLE category ( id TINYINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, name VARCHAR(40) NOT NULL ); INSERT INTO category (id, name) VALUES (1, 'CSS'), (2, 'HTML'), (3, 'XML'), (4, 'Javascript'), (5, 'PERL'); CREATE TABLE category_j_article ( category_id TINYINT UNSIGNED NOT NULL, article_id INT UNSIGNED NOT NULL, PRIMARY KEY (category_id, article_id) ); CREATE TABLE article ( id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, name VARCHAR(40) NOT NULL ); Let's say I want to find all articles that aren't filed under multiple categories? For example, if I want to find articles that aren't filed under the CSS and HTML categories?
View Replies !
View Related
Mysql, Simple Table Join - How To Loop Through Records
I've been gradually learning and using PHP for about a year and I've now decided to start learning MySQL I've got my first database up and running, and I can display records, edit/delete/search and sort records from that database. But I want to make things a bit more complicated and start making related tables. I've decided to re-invent the wheel and make a very simple content management system for myself, just to see if I can. I have a table called posts, which is what I enter my table content into, consisting of headline, creation date, post text and an id field. I've also set up a table called comments which will allow users to add comments to my posts. In the comments table there's a field called id_post - which is the id of the post that the comment relates to in the posts table I've already got my loop set up to loop through my posts records, but I'm confused about how I go through looping to get the comments records. How do I do the loop? I thought about doing an SQL query to the comments table nested inside my loop through my posts results SELECT * FROM comments WHERE id_post='$id' ORDER BY stampcreate But I assume this isn't the most efficient way of doing things....
View Replies !
View Related
Selecting Known Records
Say I have a big list of IDs of the records I want to select. What is the best way to do this? 1. A select statement with multiple OR statements eg. SELECT * FROM table WHERE ID = 2 OR ID = 5 OR ID = 10 etc... 2. Multiple separate mysql queries each selecting an ID each. 3. A select statements like obove but all the queries sent in a single query using union. I dont know how important it is but with lots of queries a second it could make a difference. If there is other better way please tell.
View Replies !
View Related
Selecting Many Different Records
I have table A_1 which has one of its fields called ID (a unique integer field). Now if I wanted to select let say the records with the ID matching these numbers 5, 7, 8, 9, 15, 22, 26, 29 how would I do this? I would imagine there is a better way to do this than using many AND statements to do this?
View Replies !
View Related
Selecting Records NOT IN
I have one table containing itmes each having its own id, and a another table containing some of these numbers in one of its columns. I need to to build a query that returns the itmes from the first tables whose numbers are not present in the second table. (SELECT t1.* FROM t1, t2 WHERE < the value in t1.item_id is nowhwere in t2.item_id >) How do I do that?
View Replies !
View Related
Selecting Top Records
I was posed a question in an interview that required me to select the top 3 records of table after doing a sort. I have been studying MySQL for the last 6 months or so and don't recall that capability in MySQL. I know it is available in Oracle and SQL Server. Is this possible in MySQL?
View Replies !
View Related
Selecting Date Records
What is the most efficient way to select records based on a field called NOW which contains a date or a datetimestamp I use: SELECT * FROM `TBL` WHERE DATE_ADD( `TBL`.`NOW` , INTERVAL 3 MONTH ) > CURDATE( ); Is this the best way? TIA [color=blue] > Nicolaas[/color]
View Replies !
View Related
Selecting From Two Tables Random Records
I have two trivia tables of questions and answers that I want to select from. I need to select 6 random questions from one, and 2 random questions from the other. I tried variations of: (select * from table1 order by rand() limit 6) union (select * from table2 order by rand() limit 2)
View Replies !
View Related
Selecting Past 12 Weeks Records
Im trying to grab the past 12 weeks records in a table. here is my code: $query1 = "SELECT * FROM table2 WHERE compdate > DATE_SUB(NOW(), INTERVAL 12 WEEK) ORDER BY compdate"; if($result1 = mysql_query($query1)) { $amount = 0; while($row = mysql_fetch_array($result1)) { $compdate = $row['compdate']; $compdate = substr($row['compdate'],8,2); $compdate = ltrim ($compdate, "0"); $days_array[$compdate] = 0; } } else { echo mysql_errno().": ".mysql_error()."<br>".$query1; } And here is the error: 1064: You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'WEEK) ORDER BY compdate' at line 1 SELECT * FROM table2 WHERE compdate > DATE_SUB(NOW(), INTERVAL 12 WEEK) ORDER BY compdate
View Replies !
View Related
Selecting A Subset Of Records From Database
I have a table with column of Strings which can take any thing from the array [ABC, BAEFD, ADS, AFD, XYZ]. for examle the entries in the column can look Search IDTAG 1ABC;BAFD;XYZ 2BAFD;ABC;ADS 3AFD;XYZ;ABC;BAFD Now, the user can give any combination of strings like, ABC,XYZ or AFD,XYZ Now i need to retrieve the records, whose TAG has all these tags.i.e, First set should fetch me records 1 and 3 and second set should fetch me only record 3 What will the sql qurey look like?
View Replies !
View Related
Selecting Non Duplicate Records Without Using DISTINCT
how not to display duplicate records without using DISTINCT. Say i have a database: ID Channel Description 41 Africa News Latest news from africa 42 Technology News Latest Tech News 43 PC News Latest PC related news 44 Africa News Latest news from africa I run the query 'SELECT DISTINCT * FROM Mytbl' and obviously get the results as shown above with 'Africa News' appearing twice as the primary key ID makes it unique. How can i return only one of the Africa entries in the results while still having the primary key value returned as well? Unfortunately with this particular app it is inevitable that duplicate values will occasionally be added to the database but i never want duplicates to be returned from a search query. Can this be done?
View Replies !
View Related
Selecting All Records Without Dups Based On One Field?
I have a table with 5 fields, of which several sometimes have duplicates. example; | id | item | day | hour | ip_address | | 1 | 3 | 2 | 11 | 204.156.33.78 | | 2 | 7 | 2 | 15 | 122.165.177.211 | | 3 | 1 | 3 | 1 | 205.52.79.122 | | 4 | 7 | 3 | 9 | 122.165.177.211 | | 5 | 11 | 3 | 9 | 177.15.99.111 | | 6 | 24 | 3 | 17 | 122.165.177.211 | As you can see the ip address may obviously have dupe entries (as do other fields but I don't care if they are dupes) and I want to query the table and get all the fields but only one where there is dupe ip_address 's. So that I would get these results; | id | item | day | hour | ip_address | | 1 | 3 | 2 | 11 | 204.156.33.78 | | 2 | 7 | 2 | 15 | 122.165.177.211 | | 3 | 1 | 3 | 1 | 205.52.79.122 | | 5 | 11 | 3 | 9 | 177.15.99.111 |
View Replies !
View Related
Selecting Values For This Record Based On Other Records
Is it possible in a select to reference the contents of a column in a different record to the one currently being assessed? Kind of like :- Select id,prev_id,title,flag FROM my_table WHERE flag != "S" AND flag(prev_id)="S"; What I'd like it to do is select records which have flag set "S", but the record referred to in prev_id do not have flag set "S". If it matter, I'm stuck on 4.0.
View Replies !
View Related
Selecting All Records In A Parent/child Tree Structure
I have a tree menu and I am filtering it so that it shows only nodes in the tree that have products against them. I have two tables: 1. Tree table Name: 'shopCategory' Fields: cat_pk category_text parent_pk url 2. shop items Name: 'shopItems' many fields but key field of TDCategoryID links to cat_pk in the shopCategory table I have got as far as: SELECT * from shopCategory WHERE cat_pk in (Select TDCategoryID from shopItems); This gives me all the nodes that have items against it: +--------+--------------------------+-----------+------------------------------- | cat_pk | category_text | parent_pk | url +--------+--------------------------+-----------+------------------------------- | 89 | Food and drink | 1 | ***** | 93 | Vitamins and supplements | 89 | ***** | 94 | Wine | 89 | ***** | 100 | Other wine | 94 | ***** | 121 | Home and garden | 1 | ***** +--------+--------------------------+-----------+------------------------------- What I need to do now is follow the parent/child relationship and return all the records to the recordset until parent_pk is '0' i.e. the top of the tree. THe problem is the length of the tree varies so it may be folder|items or folder|folder|items is this possible?
View Replies !
View Related
Selecting Records Between Two Dates Occurring In Certain Month Of A Year
I have trouble with querying events for my calendar. It consists of as simple fields as these (only relevant fields to my question): event_id (INT10) event_date_begin (DATE) event_date_end (DATE) now, an example row: event_date_begin has a value of 2008-12-28, event_date_end has a value of 2009-01-04 How can I retrieve this row, knowing only the current month and year, say I want to display all the events in my calendar happening / continuing in January 2009 (1/2009). So this query should be able to fetch my example row, because the event is still occurring in January (ending date in 2009-01-04). I can display one month / year at a time, so the query always has those, for example 01, 2009. I think what I need, is some kind of "function" to check, if certain month and year exist between those two date fields..? The closest I've got so far is: 'SELECT event_id, event_date_begin, event_date_end FROM events WHERE 12 BETWEEN MONTH(event_date_begin) AND MONTH(event_date_end) OR MONTH(event_date_begin) = 12 ORDER BY event_id ASC'; This query returns all the necessary rows, also if the event continues to the next month, but when the year changes it stops working correctly..
View Replies !
View Related
Join Sum To Records
Am trying to get a list of all vacation time used by each employee since 1-1-2002. I can do it with 2 separate queries. The first looks like... SELECT e.employee_pk, e.first_name, e.last_name, a.rate,a.effective_date, ((((YEAR(CURRENT_DATE)-YEAR(effective_date))*12))+(MONTH(CURRENT_DATE)-MONTH(effective_date))) AS month_count, ((((YEAR(CURRENT_DATE)-2002)*12))+(MONTH(CURRENT_DATE)-1)) AS max_count FROM employee e, hr_vacation_accrual a WHERE e.employee_pk = a.employee_uid AND e.active=1 AND exempt_status != 'H' AND exempt_status != 'C' ORDER BY e.last_name, a.effective_date What the above is doing is getting the employee's name, their accrual rate (we accrue at various numbers of hours per month based on seniority), the date the new seniority kicks in, and the number of months at this rate, as well as how many months there are since 1-1-2002. Code:
View Replies !
View Related
Select Unique Records From A Join?
I have two tables, both tables contain a matching JobID. Some records in the job table are not present in the joblocation table. I need to Select only those records. I thought I could just do a left join on the two tables with a not equal to where clause? SELECT job.JobLocation1, job.City, job.Province, job.SiteName1, job.Code, job.JobID, joblocation.JobID FROM soileng.joblocation joblocation LEFT OUTER JOIN soileng.job job ON joblocation.JobID = job.JobID WHERE job.JobID != joblocation.JobID limit 100 Any ideas on how to get this to work?
View Replies !
View Related
1 Record To Multiple Records
I'm looking for an efficient way of doing the following... table1 - groupName, firstGuy, secondGuy, thirdGuy,... sample - 'Red', 2, 6, 3,..... table2 - guyId, firstName, lastName sample - 2, 'Jim', 'Smith' ; 3, 'Bill', 'Adams' ; 6, 'Tim', 'Jones' If I queried groupName = 'Red', how do I get the result to be the names from table2. Subqueries?result to contain: red, Jim Smith, Bill Adams, Tim Jones I hope this is clear.
View Replies !
View Related
Multiple Records Single Value
I have been developing a realtor intranet system. I am currently writing a php script that deals with 2 tables "property" and "photos" property table: id | address | etc.. ---------------------------- 1 56 My Road 2 389 Your Street photo table: id | file_id | default | property_id --------------------------------------- 1 3434... 0 1 2 343c... 1 1 3 3udu... 0 1 I have added a field "default" in the "photos" table. The purpose of this is to set the default photo for the property listing. My problem is: I want to perform a single update query as I currently only know how to perform this operation with two. eg: $sql1 = "UPDATE `photos` SET `default` = 0 WHERE `property_id` LIKE {$var}"; $sql2 = "UPDATE `photos` SET `default` = 1 WHERE `file_id` LIKE {$var}"; Any way of doing the above 2 queries in a single query?
View Replies !
View Related
Obtaining Multiple Records
I am having a lot of difficulty in trying to access multiple rows in a table. The scenario goes like this: 'config' Table conf_id (primary key) storage_id cont_title conf_descript sort_order visible Each of the 'conf_id' is different. The table above as the information I want in it under the mentioned fields. I have set up 2 tables called 'multiconfig' containing: multiconfig_id (primary key) storage_id (index) And 'storage' containing: storage_id (primary key) field1 field2 field3 field4 field5 The idea is to link the 'storage' table to the 'config' table via 'multiconfig'.
View Replies !
View Related
Multiple Records As Columns
I have a table of Sessions and a table of Speakers. Each Session could have several Speakers and each Speaker could speak on several Sessions. I have a link table containing just SessionID's and SpeakerID's. All this is working fine on a successful website. I am now trying to write a query that will return records for each Session with each of the Speakers' details for that Session. Each record could have a different number of fields according to how many Speakers were assigned to that Session - this is the bit that makes it seem difficult.
View Replies !
View Related
|
TRACKER
