Pull Related Info Using Subqueries
I'm creating a tool to browse a database of movie listings. The browser
pulls up 25 results at a time, and you can page through them using 'Next'
and 'Prev' tools. Pretty basic stuff.
Here are my tables:
movies
directors
comments
movies_directors
movies_comments
etc...
(primary key is movies.id)
But here's where I'm stuck: for each film, I need to pull the movie info in
'movies', plus any related data from other tables, like this:
The Lord of the Rings | Peter Jackson | 3 comments
Episode II | George Lucas | 0 comments
Indiana Jones | Steven Spielberg | 15 comments
I seem to run into problems when I try to join info from all of these
related tables. If there's a match, great. But, if not (like a movie with no
comments), the movie is excluded from the result set.
I've tried all sorts of SQL queries to make this work, but nothing seems to
do the trick.
SO, HERE'S MY QUESTION, IS THERE ANY WAY TO DO THIS QUERY WITHOUT
SUBQUERIES/MYSQL 4.0? Or would the best approach be to use PHP to do all the
subquery lookups?
View Complete Forum Thread with Replies
Related Forum Messages:
Pull Info From One Database To Save In A Session To Push To A Different DB
I'm building a dynamic site that has 3 data bases. One DB contains all of my store info ID#, Name, Address, Phone. Another DB contains member info ID, Name, address, email,phone,etc... The 3rd DB Tracks when a user logs in and logs out at a store. This DB has the following columns Store ID, Member ID, log in time,log out time, time in store. The stores log in in the morning that starts a session-I need the session to pull the store's info ID#, Name,etc... so when a user logs in, in the store. The login page inserts the store ID, Store name, and user ID into the 3rd DB. There are a lot of stores how do I get the store variables to be inserted into the DB?
View Replies !
Count Related Records, But Show Records With NO Related Records Also
cl_items ======== it_id (pk) it_ownerid it_name cl_offers ======== off_id (pk) off_itid (fk) -> to it_id off_whoid My query needs to output ALL of the records in cl_items AND still show how many offers are on each item (from cl_offers) I can't get what I want through the GROUP BY because I want to show the records in cl_items which DO NOT have any offers on them yet also. Is there any way to do this with mysql only?
View Replies !
Pull All Records
if it were possible to write a statement in SQL where one could select several fields from a table and then limit the records returned by only displaying those where, say, FieldA (a varchar) contains a given short string which could be only a part of the field's contents. As long as a record has this short string in Field A, it should be included in the resulting set of records.Something like this: SELECT FieldA, FieldB, FieldC FROM TABLE Table1 WHERE FieldA *contains string* Is there anything in SQL that could examine the contents of a field for a partial string, and then return the records that have that partial string in that field?
View Replies !
Multiple DB Pull
Lets say there is a database of special users and a database of favorite movies SPECIAL USERS (username) bob jim FAVORITES (username - movie_title) bob - movie 1 kim - movie 3 jim - movie 1 larry - movie 2 jane - movie 2 How would I write a query that lists the most popular movies of special users without explicitly doing: SELECT * FROM FAVORITES WHERE username = 'bob' or username = 'jim' GROUP BY movie_title I want something like this: SELECT * FROM FAVORITES WHERE username = [all the usernames from the SPECIAL USERS DB] GROUP BY movie_title
View Replies !
Mysql Pull
I got this in my mysql databe table. a:1:{i:0;O:19:"test_Enclosure":4:{s:4:"link";s:36:"http://grabber/v/CAcpLiw4qt4.swf";s:4:"type";s:29:"application/x-shockwave-flash";s:6:"length";N;s:10:"javascript";s:2:"js";}} I want to pull out only the http://grabber/v/CAcpLiw4qt4.swf part?
View Replies !
Can Input Japanese, Can't Pull It Out
I figured out how to store Japanese characters by setting the collation of a particular field to utf8_bin. I can input Japanese characters, and I can see them just fine under PHPMyAdmin. The problem is that when I execute a query and print them to a web page, all I get is question marks instead of Japanese characters. This is while using the same browser that allows PHPmyAdmin to display the characters correctly. I added the following metadata to the <head> portion of the document but it didn't help: <meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
View Replies !
Populate A Pull Down Menu From Db
how do I populate a pull down menu from a MySQL table? Using PHP/MySQL 4.0 Have table1 with names and ID and tableb with ID and statistical info.wnt to be able to scroll names and have users able to click on the name to follow a link to a page created using tableb data for each name listed. Table examples: tablea ID l Name --------------------- 1 Person, Joe 2 Smith, Joe tableb ID l units l margin -------------------------------- 1 5023 123 2 2536 72
View Replies !
Code To Pull Amount
I'm certain there is a function to do this but I forget what it is... Say I am pulling values from the database, but I only want to pull like the first 10 chars of the value.. how do I do that?
View Replies !
Pull Hosting Plans
I am currently creating a web hosting website for a client of mine, for the packages and the order process we are using a script called "Auto-Host (http://www.idevspot.com/AutoHost.php)", which stores all the hosting plans into a mysql database. This is a great script and it does exactly what we want it too, the only thing missing is it doesnt create a page where people can view the hosting plans and click a sign-up button. Can someone please give me guidance on how i can get a page with all the hosting plans on please?, alternatively i am willing to pay somebody for doing this on my behalf
View Replies !
Pull The Last 10 Records From A Transactions From A Table
I'm trying to pull the last 10 records from a transactions from a table using this query: SELECT * FROM transactions ORDER BY timestamp DESC LIMIT 10 But I want to display the rows in ascending order by timestamp. I can't get the subquery below to work and not sure why: SELECT * FROM (SELECT * FROM transactions ORDER BY timestamp DESC LIMIT 10) ORDER BY timestamp ASC;
View Replies !
Pull Similar Data From Two Tables
I need a query to search two seperate tables for similar data and return them in one result. I know you can do multiple select statements in one query but I dont know how to exactly organize them into one result. Does anyone know how to accomplish this.
View Replies !
Result Pull And Rating Sort
I have two databases. What I would like to happen is when I have a summary of products, I would like the query to pull the info from the product table and based on the id from that table cross reference with the reviews for that particular product and calculates all the reviews for that product into a single percentage. So when I access the assoc array from PHP I would like to see : Array(Array(['id'] => '2', ['prod_name'] => 'gummy bears', ['rating'] => '72'), Array(['id'] => '57', ['prod_name'] => 'socks', ['rating'] => '52')).
View Replies !
How To Pull Data From Multiple Tables
I have a db, with several tables in it, I have now reached to the point where I need to pull data from several tables. What is the best way to do this? Should I put a column in each table, and then insert a unique key in all of them at the time the record(row) is being created? If this is the way to do it, when I am putting the unique key in each table would I just do an insert in each of the tables?
View Replies !
Pull Data From Multiple Tables
Can you pull data from 2 tables in the same database onto the same page? What about more than 2 tables? Here is my dilema: I currently have a database with 1 table, and a ton of rows. The information is property information, and each property has multiple empty suites. As of now, I have columns for Building Name, Address, and then columns for Unit Type A (for the first unit), Rent A, Type B, Rent B, etc.... I was wondering if I would be able to put the listings we have into thier own property table, and then one bigger table of all the properties individual information. Keep in mind I need to call all tables together on my portfolio pages, so I would be connecting to quite a few tables at once. Or my other option I was thinking about was to just have 2 tables: 1 Buildings info, and 1 units info. Which of these would you do, or how would you organize this data.
View Replies !
Regular Expression :: Pull 3 Or 4 Digit Numbers
I would like to pull all the model number from my products table that are strictly only a 3 or 4 digit number ie: 034 or 3542 or 0243 or 934 etc Can someone advise me on how to construct a regular expression to give me the data I need?I have tried the following but it wasnt 100% accurate: Code: products_model REGEXP "[[:<:]][0-9][0-9][0-9][0-9][[:>:]]" or products_model REGEXP "[[:<:]][0-9][0-9][0-9][[:>:]]"
View Replies !
Pull Data From 2 Database Tables Into 1 Table On My Site.
I got 2 tables: a dev_tasks and a task_history table. The task table has to record information about the task I am working on AND the history table is for the task history logs. I am trying to display info from both tables into 1 table on my site. Most of the info will come from the dev_tasks table BUT i want to dedicate on column of the displayed info for the latest log made to the task_history table. The common column to both tables is the 'taskid' column. What would the query look like to pull the info from both tables, knowing that the taskid is common to both and that I want to display the last log per tasks from the task_history table?
View Replies !
Trying To Pull Id, Count And Title But Lose Rows When I Add Extra Join
i'm trying to extract some information from my database, the query being PHP SELECT grps_c.catid, grps_c.title, COUNT(grps.groupid) AS COUNT FROM grps RIGHT JOIN grps_category grps_c ON (grps_c.catid = grps.catid) GROUP BY grps_c.catid ORDER BY grps_c.title which works fine. however some of the groups (grps.groupid) are hidden and i don't want to count them, so my thinking was add PHP LEFT JOIN grps_setting grps_s ON (grps_s.groupid = grps.groupid AND grps_s.hidden_group != Ƈ') however adding that removes the rows that have a 'count' or NULL or Zero.
View Replies !
Subqueries On 4.0.25 Without Subqueries
i have 2 tables. knowing subqueries cant be performed on this version how would i write the following statement? i have 2 tables table.a, table.b I want to select all records from table.a that match table.a.id = table.b.id WHERE table.b.varchar = 'value' once the table.b.varchar is found, get table.b.id and match it to any records in table.a.id that match it. Can this be done in 1 statement. I'd hate to have to run a loop here. I've tried without success JOIN statements. here is one of my attempts: "SELECT table.a.* FROM table.a, table.b WHERE table.a.id = table.b.id AND table.b.varchar = '$value' GROUP BY table.a.sortkey ORDER BY table.a.is_primary DESC "; this does return 1 row, and joins the 2 tables accordingly, however i need all rows in table.a that match the table.b.id where table.b.varchar = 'value'
View Replies !
Use Of Subqueries With IN
I'm trying to do the following query: select * from table1 where col1 in (select col1 from table2); I keep getting a message to check my query after "in". But I've cut and pasted that query and it works. This is a very simple query so I don't understand why it doesn't work.
View Replies !
Subqueries With ALL
I got following statement: SELECT * FROM ip WHERE id <> ALL (SELECT ipid FROM pcip); Like I can see in the documentation, this should be correct, but SQL drops a SyntaxError near "ALL (SELECT ipid FROM pcip)" What it dows should be ovious, select all IPs that IDs are not noticed in the PCIP table under IPID The Tables are set up correct, means both tables exist and got values for the mentioned collums My MySQL version is 4.0.21 if that is part of the problem...
View Replies !
Subqueries For V3.23
An upgrade is definately coming, as some some SQL practice, but I'm trying to figure out how to do a subquery and not entirely certain where to turn to. From my limited understanding of SQL, what I want to do should look like: SELECT (SELECT count(SurveyData.Choice) WHERE SurveyData.Choice='0') as a, (SELECT count(SurveyData.Choice) WHERE SurveyData.Choice='1') as b, (SELECT count(SurveyData.Choice) WHERE SurveyData.Choice='2') as c FROM SurveyData The SurveyData table contains 3 fields: SID (Int), Name (Varchar 100), Choice (Int) I'd like to use it as the basis for a reusable/scalable web survey app (perl CGI), but all I'm getting is a syntax error.
View Replies !
Subqueries With NOT IN And <> ALL
I am having troubles with the following on 4.0.22: select * from question as q where q.id NOT IN (select distinct question from choice ); choice.question ---> question.id Apparently there's a syntax error, at (select distinct ....) <> ALL yields the same result.
View Replies !
Possible Without Subqueries
I'm trying to merge the result of 2 queries but I can't use subqueries. The first query gets all the sign-ups from a particular referer, the second query gets how many of those sign-ups are still active. So the first query does a count, group by ref and the second one does count where active group by ref - I know I could do this easily with subqueries but without them I'm lost.
View Replies !
Subqueries?
I'm using MySQL Control Center 0.9.2-beta. Will this take subqueries? I'm trying to extract data from tables in two different databases, and I'm not all familiar with JOINS. What is the syntax to do this in a subquery? In a JOIN?
View Replies !
Max() & Subqueries
Cant seem to get what i want Missing something so fundamental. Did it with subqueries on server at home just cant seem to get my head round it without using subqueries I have 3 tables ....
View Replies !
Related Item
im currently working on a simple lyric page: I have 2 data : title and keyword when a user view a particular lyric, i want a related lyric by title & keyword to be display example data in database title Green Day Walking Alone Keywords(separated by commas) Green Day, walking alone, tabs, midi i use this code below but the result display only one title which is the same title as the currently viewed lyric SELECT title, keywords FROM lyrics WHERE title LIKE '%".$arr[title]."%' AND keywords LIKE '%".$arr[keywords]."%' how to fix this is my sql format correct ?
View Replies !
Addition Related
I have a table called "members" which has a field called "id". Now I would like to run a query to add exactly 6000 to every number in the "id" field. What would be the easiest way of going about with this?
View Replies !
Display Related Value
I'm displaying personnel actions data in a details page based on the ID of the personnel action record, but would like to display a value (Employee Name) from a related table (tblemployees) based on a payroll_num field in the details page. These two fields are related, payroll_num and payrollID(primary in tblemployees). How do I add both tables in my query or is this the right way to get this accomplished?
View Replies !
Slecting Related
I need to select 5 related products.... it should work like this: I have product with name Website Ezine Make Easy now I need to find 5 products based on words from product and based on that product category and if there is no 5 then to take rest of products from main category.... so far I have this: PHP SELECT * FROM products WHERE fullname LIKE "Website" OR fullname LIKE "and" OR fullname LIKE "Website and" OR fullname LIKE "Ezine" OR fullname LIKE "Website Ezine" OR fullname LIKE "and Ezine" AND section = ཉ' OR section IN (1,2,3,4,5,8,9,10,11,12,13) ORDER BY CHAR_LENGTH( fullname ) DESC LIMIT 0,5 unfortunately this is not good, I get some products which shouldn't be there.
View Replies !
UNIONS In Subqueries
I have a problem using UNIONs inside subqueries. I have simplified my query to make it more readable. The question is about the right syntax.....
View Replies !
How To Rewrite This Without Subqueries?
i have a table that contains the fields order_id, canceled, and captured. the same order_id can appear many times in the table. i want to retrieve all the order_ids for which NO record exists that has both canceled and captured = N. i am not able to upgrade my version of mysql at the moment, so i would like to write a query that does that with no subqueries.
View Replies !
SQL, Count And Subqueries
I have one table that lists a number of courses available (including CourseID, CourseName and MaxPositions), with CourseID being auto_increment and unique I have another table which has CourseRegistration (Containing CourseID and CustomerID), with no unique fields. Customer information is stored elswhere. Without having a field in the Courses table holding the number of people currently registered, how would I return a result set which included: CourseID, CourseName, MaxPositions and the Number of people registered to take that course. Preferably this would not use features in MySQL 4.1, but anything is fair game at this point.
View Replies !
Theory Of Subqueries For 4.1.7
While converting SQL statements for a database change, I discovered a big performance hit in MYSQL with subqueries vices Sybase. I'm hoping that someone might be able to help me understand why? I have two tables USERS (2200 records) and JOB Decriptions (163 records). I wanted to retrieve all the job description not in the USER table. (No Indexes on JOBDESC currently) select JOBDESC from JOBS where JOBDESC not in ( select JOBDESC from USERS where JOBDESC not NULL ) In Sybase the query returns in about .56 secs In Mysql 4.1.7 query returns in about 8.78 secs The funny thing is if I run the query like so select JOBDESC from JOBS where JOBDESC not in ('President','Vice President','Treasaur','Secretary') returns in .03 secs. Those four descriptions are returned by the inner select. All select statements individually return in .03 secs. I created indexes on both tables for JOBDESC and reduced the time to ..97 secs. I used explain command to help understand what is going on hence the indexes, but why the difference in speed?
View Replies !
Removing Subqueries
the query i want execute is: select email from avalia where email in (select * from avalia where nome=´img/im1.jpg´) and email in (select * from avalia where nome=´img/im2.jpg´) but mysql 4.0 don't acept subqueries.
View Replies !
Optimization :: Subqueries
i have a little complex query that involves sub queries upto three levels. now thing is that , i think, mysql evaluates the sub queries every time that query is evaluated, whereas, i know that results for the third and 4th level queries are same for some number of queries. can we force mysql to store the result of the subqueries to be used later, instead of reexecuting the query.i studied mysql query optimization but, finally concluded is that things over there are just how mysql optimizes queries, not how can we optimize the query to be performed better. any resources for query optimization, i mean good resources ?
View Replies !
Subqueries And The IN Operator
hey all, i am having serious problems with getting my head around why this will not work. i am trying to return a number of games played with a count of the number of people in it. the table structures are as follows: games gameref | gamepot | gamedate | gamelength | ..1...... | .... 50.... | 12.12.04 | ... 03.00.00 | winnings gref | pref | amountwon | stake | 1 . .| 1 .. | ....20 ...... | 10...| i have used the dots to try and keep the table in some sort of form. i have only entered one row of data for each just to show the format of it, if this is not enough please ask for more. the games table holds each game that has been played, the total pot available to be won the date played and the duration of the game. the winnings table is teh winnings of each player in each game. the gref is the game it refers to, the pref is teh player it refers to, (there is a player table but is not used in this query at all). the amount that player won and the stake they entered into the pot. if ya didnt gues its a poker database. my problem is as follows. Quote: $sql = ("SELECT games.*, COUNT(winnings.gref) as playercount, winnings.gref FROM games, winnings WHERE gameref = gref group by gameref order by gamedate"); this query works a treat. it counts the amount of players in each game and outputs it all nicely.. but i wanted to edit the query to be able to show just all the games a single player had been in. Quote: $sql = ("select games.*, COUNT(winnings.gref) as playercount, winnings.gref from games, winnings where gameref in (select gref from winnings where pref ='$pid') group by gameref order by gamedate"); $pid is passed into the page by Quote: pt_register('POST','pid'); but this throws an error Quote: Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in /home/ghoxmfjz/public_html/database/poker/allgames.php on line 32 Query failed! line 32 is: Quote: $nrows = mysql_num_rows($result); i can get it to work if i substitute the subquery for (1,2,3) being the first 3 games played. this would assume a certain player would have been in these 3 games. the problem i get is that the COUNT function just counts every player in everygame not the individuals. if i group it by gref it counts them (sort of.. it doubles the count) but then does not return any of the other information for the query. i am very confused any and all suggestions of things to look at or consider or a straight ur doing XXX wrong would be grand.. thx all Jon oh btw if you wish to look at the output if that helps the URL is: http://www.happygolucky.me.uk/database/poker/allgames.php with the variable $pid as the reference to try the player.
View Replies !
Subqueries Clarification
im using MySQL 4.1 the one the comes with XAMPP installer.. I have a question regarding correlated subqueries..in this version of MySQL, are correlated subqueries supported? as in like.. $query1= select field from table where field = 1; $query2= select field from table4 where field= 4; Insert into tmpTable ($query1,$query2)
View Replies !
SubQueries Prior
Does anybody know how to rewrite the following query to run on mysql 4.0.20. Is it possible to do this with JOINS? (I don't really have to join 2 tables... SELECT * FROM OAK_photos WHERE cat_id = (SELECT cat_id FROM OAK_photos WHERE id=$id) AND order_nr >= (SELECT order_nr FROM OAK_photos WHERE id=$id) ORDER BY order_nr ASC LIMIT 0 , 5 or should I run the 2 subqueries first as seperate queries and pass on the outcome via variables to the third query, the above main query?
View Replies !
Using CONCAT In Subqueries
SELECT tblmonths.fldMonth, tblyears.fldYear, tblmonths.fldID, CONCAT(tblmonths.fldID," ", tblyears.fldYear) FROM tblmonths, tblyears where CONCAT(tblmonths.fldID," ", tblyears.fldYear) NOT EXISTS (SELECT CONCAT(fldMonth, " ", fldYear) from tblexpensesclaims) GROUP BY tblyears.fldYear, tblmonths.fldID
View Replies !
Subqueries In The FROM Clause
I'm using 1&1 web hosting services with MySQL as database (Ver 14.12 Distrib 5.0.16, for pc-linux-gnu (i686) using readline 5.0). When I ran an example in Chapter 13.2.8.8 from MySQL 5.0 Reference Manual: CREATE TABLE t1 (s1 INT, s2 CHAR(5), s3 FLOAT); INSERT INTO t1 VALUES (1,'1',1.0); INSERT INTO t1 VALUES (2,'2',2.0); SELECT sb1,sb2,sb3 FROM (SELECT s1 AS sb1, s2 AS sb2, s3*2 AS sb3 FROM t1) AS sb WHERE sb1 > 1; I always get the following error message: ERROR 1064 (00000): 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 'SELECT s1 AS sb1, s2 AS sb2, s3*2 AS sb3 FROM t1) AS sb
View Replies !
Problem With Subqueries
I am trying to find the rows in table1 that do not have matching rows in table2 in order to set the value of a field in table1. I can get a query to return the rows that do have matches with this query: select item1 from table1 where item1 in (select item2 from table2); However, the following queries return no data: select item1 from table1 where not(item1 in (select item2 from table2)); select item1 from table 1 where item1 <> all(select item2 from table2); My understanding of the first query is that the not operation would be applied after evaluating whether item was in the result of the subquery. With the second query, I thought it should return true if item is not equal to every result in the subquery. If I try to use a left join and then simply find the rows that have a null value coming from table2, I get the correct resultset but I can't use that as a subquery for my update statement. Since table1 is what is being updated MySQL tells me that table1 cannot be used in the subquery for the update statement. I have this working in a stored procedure by importing the result of the join into a temporary table, but I'd rather not have to write all that data and then delete it.
View Replies !
Slow Subqueries
I know (by internet) that mySQL is very fast. Problem is that my subqueries that are very fast in Access or SQL Server but they are very slowly in mySQL - since I have to restart my computer because mySQL freeze all the processes. (the resources where used at maximum - 2 Gb RAM, 2,5 Ghz processor) This is the query : SELECT NPL, PP FROM P_A INNER JOIN ACTIONS ON P_A.NA =ACTIONS.ACT_N WHERE P_A.NA in (SELECT NA FROM P_A WHERE NP ='ABC') P_A has 5 columns and 12000 rows Actions has 5 columns and 770 rows Any suggestions ?
View Replies !
Column Subqueries
to the best of my knowledge, a column subquery is a single column resultset with many rows, i'm assuming joined by some previous column / row value. can anyone show me a column subquery, manybe even a multiple column subquery, with a very small discussion on what they did and what it would return? I'm looking to return a query like username, company name, company location, application, application role, application persission where I do not get the "normal" repetitive SQL resultset. note: column subqueries may not be my direct answer but I still wanted to see what column subqueries was all about.
View Replies !
Subqueries In MySQL 4.0?
I've got the following query. I just found out that subqueries are not supported by MySQL 4.0... How would I replicate this to work with MySQL 4.0? SELECT DISTINCT CategoryID, tblCategory.Category FROM tblCategory INNER JOIN (SELECT Cat FROM ( SELECT tblCategory.Category AS Cat FROM tblEntries) UNION SELECT Cat FROM ( SELECT Category2 AS Cat FROM tblEntries) UNION SELECT Cat FROM ( SELECT Category3 AS Cat FROM tblEntries)) As A ON tblCategory.CategoryID = A.Cat; Table tblCategory CategoryID = index key Category = Text entry describing the Category Table tblEntries Category = key link to tblCategory Category2 = key link to tblCategory Category3 = key link to tblCategory Basically, I want a list of Categories that are used in tblEntries.
View Replies !
|
TRACKER
