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 Complete Forum Thread with Replies
Related Forum Messages:
LIKE % Operator
I want to perform a simple search for a query such as: SELECT * FROM SOME_TABLE WHERE SOME_TABLE.SOME_COLUMN LIKE '%SOME_KEYWORD%' The same statement if implemented in a stored procedure would look similar like this: CREATE PROCEDURE sp_FindKeyword (IN SOME_KEYWORD AS VARCHAAR(55)) BEGIN SELECT * FROM SOME_TABLE WHERE SOME_TABLE.SOME_COLUMN LIKE '%' + SOME_KEYWORD + '%' ; END But this does't work .... I know I could manipulate the '%' before passing the parameter using the interface language such as php, asp, ..etc. but is there a way this can be done using MySql 5 itself?
View Replies !
OR Operator
Is the below correct. Trying to select items with typeid of 1 or 5. WHERE ooer.shipdate >='$from' AND ooeer.shipdate <='$to' AND equipment.typeid='1' OR equipment.typeid='5'
View Replies !
% Operator
I have a big database, and I need to change some of the items in a table, the stuff I need to change is in the middle of the sentance. EX: images/apparel/aprons/toppers_9324.jpg I want to just change the toppers to top, only problem is I need to chage it about about 40 columns and about 12 rows. If I do it by hand thats about 2hours wasted so I was wondering if there was a way to do it with some sort of query. And this question is a little out there but I thought %toppers% would let me select anything that had toppers anywhere in it but Im getting this error and here is what I put in: Query: SELECT * FROM `color` WHERE color1 = %toppers% Error: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 '% toppers % LIMIT 0, 30' at line
View Replies !
LIKE Operator
This is my query: $result = mysql_query("SELECT * FROM `index` WHERE firstname LIKE '$s_firstname'"); But LIKE doesn't want to work; it's functioning as an = (equals), instead. So, for example, if $s_firstname is "max" or "Max", and if 'firstname' in the database is "Max", then it displays the record info. However, I need it to also display the record info when $s_firstname is typed as "Ma" or "Maxus". It's not doing that right now; how do I make it do this?
View Replies !
BETWEEN Operator
I'm having some trouble understanding how to use the BETWEEN Operator. As far as I know, the folloving statement works: SELECT column_name(s) FROM table_name WHERE column_name BETWEEN value1 AND value2 But, it is possible to assign variable values to "value1" and "value2". For instance, I need to match the previous and following records of a given result: my_table ID -- test 1 -- a 2 -- b 3 -- c 4 -- d 5 -- e 6 -- f 7 -- c 8 -- h 9 -- i thus, at first I match my pattern, "c" for instance, and only later I match the range with the following query: SELECT ID, test FROM my_table WHERE ID BETWEEN 2 AND 4 or ID BETWEEN 6 AND 8 Is there any smarter solution to obtain the same result? and moreover, how many OR operator can join the query?
View Replies !
The ^ Operator
I just wondered if anyone out there knows what the ^ operator does in a mathematical / logical context, NOT as a regular expression. The only reference I can find to it in the manual is in the page '12.1.1. Operator Precedence'
View Replies !
!= Operator In Queries
I have a query with the following syntax to find students that haven't voted: SELECT * FROM Students WHERE Students.StudentID != Voted.StudentID The thing is, the results I'm getting are basically the same as if has just used 'SELECT * FROM Students' alone. The StudentID list of 'Voted' is shorter than that of 'Students', as some students haven't voted yet. Anyone know what I'm wording wrong here?
View Replies !
Truncate-operator (*)
I want to be able to search for *load* and want my boolean search to return stuff like downloads, shareloads, bogload etc. The truncate-operator works only at the end of the word though. I can only think of, when the searchterm is submitted, using php to strip the *term* out of the rest, and then adding OR|AND|AND NOT (depending on *term, +*term*, -*term*) tablefield LIKE ''%term%' Is there a better way, without the use of LIKE?
View Replies !
IN Operator Value Handling
I have a small question on IN operator. What is the maximum count of values an IN operator can handle properly in a query. Code: SELECT * FROM table WHERE id IN (1,2,3,5.....) The values that i get are close to a million and i want to backup them up using mysqldump using the IN operator.
View Replies !
Operator Question
Is it possible to tell mysql to use '*' instead of '%' while using the like operator? to: select count(*) from customers where name like 'Mart*';
View Replies !
Operator Enquiry
Can anyone tell me why I keep getting an error for the below code..? select content_id,weight from mapping where node_id in (select node_id from concept where parent_node_id='n001') The inner loop works fine and results in a set of 2 values.
View Replies !
SELECT With LIKE Operator
I'm using the following query to search my database after matching records. But whenever the search contains a space, the records doesn't show up. I'm using a GET method from the search page, btw. $query9="SELECT b_id,b_name,b_lan,b_description,b_category FROM bands WHERE b_name LIKE '%$search_b_name%' OR b_description LIKE '%$search_b_name%'"; Maybe I have to split up the search keywords and do multiple searches for each one?
View Replies !
Logical Operator Hit
I have a rather complex query (about 17'000 characters long) being glued together from a php-script. It is basically a select over a few tables using joins with some "where"-clauses and alot of "OR" clauses. Example: and t6.fir="480" or t6.sod1a=8329 and t6.fir="480") or (t6.soc1a=11071 and t6.fir="490" or t6.son1a=11071 and t6.fir="490" or t6.sod1a=11071 and t6.fir="490") or (t6.soc1a=5009 and t6.fir="666" or t6.son1a=5009 and t6.fir="666" or t6.sod1a=5009 and t6.fir="666") or (t6.soc1a=4677 and t6.fir="450" or t6.son1a=4677 and t6.fir="450" or t6.sod1a=4677 and t6.fir="450") or ... basically the problem can be reduced to this sample query Select A from table where B=1 and C=2 or D=4 or E=5 or F=6 Now as it already takes quite some time to run the query, I would like to know which of the hundreds of OR-clauses did trigger. Is there any way to find out which field (if for the above example the values from the DB were B=1,C=2,D=9,E=5 and F=6) triggered the success? (so that it would be returning A and some hint that B,C,E and F were positive). The alternative would be to loop through all the or-clauses for the result found which would take ages to complete the query.
View Replies !
Assignment Operator
What exactly does the operator := do/mean? It is used frequently in MySql documentation, but no information exists about it except in the order of precedence section, where it is listed at the top, but not explained.
View Replies !
Using REGEX With AND Operator
I've already viewed the Reference manual but didn't quite see an example that suits my case. This is what I have so far: $in_list = "'".join("','",$cen_chiefs)."'"; //$cen_chiefs is an array $query_cen_chiefs = "SELECT * FROM central WHERE CONCAT(strName,' ',strCity,' ',strState) IN({$in_list})"; How would I go about adding an AND clause to this query to return all rows where strName column values are in $in_list array Should I be using strName as a pattern, because strName would be a partial value of $in_list array values? Something like: REGEXP '^.....$' "SELECT * FROM central WHERE CONCAT(strName,' ',strCity,' ',strState) IN({$in_list}) AND (REGEXP '^strName$') IN({$in_list}) ORDER BY conName"; //This query doesn’t work though
View Replies !
<= Operator, MySQL 4.1.12
I have a float 13,2 column "balance' in my MyIsam table. One of the values in that column is 122.41. The query "select balance from master where balance >= 122.41" returns all the rows with values greater than or equal to 122.41, including 122.41. However, the query "select balance from master where balance <= 122.41 does NOT include 122.41 in the resultset. Nor can I establish equality on 122.41. "select balance from master where balance = 122.41" returns an empty result set. What am I missing here?
View Replies !
Using LIKE Operator In Two Fields.
i have made a database search form...with two inputs one...requires the name two....requires address i want to search either by name or address..... i has useed select * from table where name LIKE "%$name%" or address like '%$address%'"; but it didnt work............
View Replies !
How To Do Operator AND In Regexp?
I am trying to use operator AND in Regexp in mysql, but I can't find a way to do it. For examples: SELECT id FROM mytable WHERE row REGEXP "a & b"; so the data in row can be "a c e b" or "b e a c", etc. I can't do SELECT id FROM mytable WHERE row REGEXP "a" AND row REGEXP "b"; because I am storing "a & b" as a single string somewhere else in the DB. How do I do it?
View Replies !
Between Operator For Two Columns?
I'm trying to get the query working. I have one value that i want to search two column with. I have two columns: ProductYear_From, ProductYear_To. Both are int. I want to search 2003. What is the best way to search 2003? Does between operator take two columns?
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 !
Multiple LIKE Comparison Using IN Operator
Is there anyway to compare a field with multiple string values using LIKE? Here's the statement I have now: SELECT * FROM list WHERE email LIKE CONVERT( _utf8 'hotmail@hotmail.com' USING latin1 ) OR email LIKE CONVERT( _utf8 'john@hotmail.com' USING latin1 )"; I know how to do it using the IN operator, SELECT * FROM list WHERE email IN ('hotmail@hotmail.com', 'john@hotmail.com') but I need to be able to use the LIKE operator with the CONVERT function. The reason I am asking is because I am trying to match 50+ different email addresses.
View Replies !
Simulating MINUS Operator
Here is the short example of what I want to accomplish. I wish to have the difference between two different select queries. So if one query pulls records 1,2,3 and 4 and the second pulls records 1 and 4 I wish to have only the records 2 and 3. How can I accomplish this easily. In case that doesn't make sense here is the long version. I have two tables that are keyed together through an id field. However this is not a one to one relationship, it is a one to many relationship. The following is an example of the table Table 1 Table 2 ------------------ ---------------- ID relid rid vid Table 1 and table two are linked through the columns id and rid. There can be many links between id and rid so the link is further refined through a vid field. What I need is all records in table 1 that will not link to table 2 such that relid=rid and vid=46
View Replies !
MySQL 'IN' Operator Syntax
I'm learning about MySQL but i'm having problems with the 'IN' operator. For example: SELECT * FROM TABLE_NAME WHERE ID = IN ('9','19','3','10'); ....gives me a syntax error. I've also tried it with (9,19,3,10) but it gives me the same problem.
View Replies !
Can't Execute Delete With The IN Operator
Lately I have been started using MySQL. I managed to create my database and tables. When I wanted to execute the following query: delete from adminpages where parentid IN ( select DISTINCT A.id from adminpages AS A where A.name='galeries' ); I have received the following error message: Error Code : 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 'select DISTINCT A.id from adminpages AS A where A.name='galeries' (0 ms taken).
View Replies !
FULLTEXT Default Operator
I am implementing a FULLTEXT search in my website. The thing is... if you use "ipod cable" on the search engine, it will return rows that have "ipod" or "cable" but no necessarily both. What I want, is MySQL to use "AND" as a default operator. So, if I search "ipod cable" it will actually look for "+ipod +cable", throwing results that contain both words. So far, I haven't been able to find a solution on the MySQL side, it seems like I will have to develop a parser in PHP for these strings. I am running the FULLTEXT search IN BOOLEAN MODE.
View Replies !
MySQL Index And LIKE Operator
I have a table with two fields education with BLOB field type and work_experience with BLOB type. I have created index for both. If I run select * from table where education like '%xxxx%' or work_experience like '%xxxxxx%'. Then index is not working since the LIKE '%XXX%'. But I have around 500,000 records in the table and I want to find a fastest way to retrieve data since this index support for '%%'. I dont like to avoid %XXX% since I am running a keyword search from both fields. Please help me to sort out this or are there any other way to achieve the records faster if index not support.
View Replies !
Syntax Error With OR Operator
I'm getting an SQL syntax error when trying to retrieve from my database. Below is the code. I am passing a variable from a form to a page with a record set, I want to check if the variable exists in two of my database columns. The code works when checking a single column but fails when I add the additional OR operator. This works: SELECT column1, column2, column3, column4, column5, column6, column7, column8 FROM database.table WHERE column3 LIKE %MMColParam% ORDER BY column5 ASC This fails: SELECT column1, column2, column3, column4, column5, column6, column7, column8 FROM database.table WHERE column3 LIKE %MMColParam% OR column7 LIKE %MMColParam% ORDER BY column5 ASC
View Replies !
Using The NOT Operator To Exclude Fields
I want to be able to select all fields from a table except certain fields. I came across the NOT operator in the MySQL ref. manual, and I wanted to check about the syntax of that operator. Would this syntax work: SELECT NOT id1, id2, id3, id4 FROM table Or would this work: SELECT NOT id1, NOT id2, NOT id3, NOT id4 FROM table If neither, what is the shortest way that would work?
View Replies !
Fulltext Search And OR Operator
Well, I have a problem. I have to match games by their titles. I use fulltext search for this. It works sufficient, but sometimes gives match to the following games: Jedi Knight and Jedi Knight II To make it correct, I should use IN BOOLEAN MODE flag and write expression like that: Jedi Knight +II This will find only the second part of this game. But there may be a situation, when it is called Jedi Knight 2, and in my DB I have it as Jedi Knight II. So is there any way to compose expression in the way like this: [pseudo code] Jedi Knight +(2|II) So that in included only rows with 2 or II in the title. Is there any way to do so?
View Replies !
LIKE Operator Searching Country
why following query does not return a result PHP Code: $country="South Korea" $query="SELECT * FROM flags WHERE country LIKE "%Korea South""; but This works fine. PHP Code: $country="South Korea" $query="SELECT * FROM flags WHERE country LIKE "%South Korea""; but i need the 1st one. coz in one db table it is saved as "Korea South" and in another table it is saved as "South Korea". Note:- in actual code "%Korea South" is like "%$country" and which holds "Korea South" and matching to "South Korea" as i sad ealier.
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 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 !
|
TRACKER
