Creating Complex Query
its a booking form. user selects start date, finish date, and room type.
on my table i have roomInfo(roomNo, type, NoOfBeds);
occupancy(roomNo,DateStart,DateFinish,GuestNumber);
i am trying to do something like:
select roomNo from roomInfo where roomNO = single and roomNO not IN ...
cant do subselect its mysql 4.x version... would i have to do an outerjoin/innerjoin?
View Complete Forum Thread with Replies
Related Forum Messages:
Creating A Complex Primary Key
I'm creating a 'customers' table for a new version of our ecom store. In order to avoid duplicate unique fields, I'm trying to have MySQL use our existing system for generating customer numbers, rather than having it generate an int PK and then generate this number seperately. Either way, I need to have MySQL generate the information described below. Our customer numbers consist of two letters, a dash, then one letter and and four alpha-numeric characters EXCEPT the letters o, i and l (to avoid confusion with their numeric look-alikes.) Examples: EA-A002S FB-F39V9 FX-P1U2U The first two characters are the customer's initials. If only one name is given, we use X for the second character. We start with A0001 and run through AZZZZ then on to B0001. We hope not to run out of customer numbers for a REALLY long time. (When we have 30,000,000 customers with the same initials, we will probably have a different accounting system, or one that can handle more than seven characters -- the dash is irrelevant.) So here's my problem. I would like to have a field called customer_number. When I run my first insert for this customer, I would like to give MySQL the initials and have it generate the next valid customer number. How can I do this?
View Replies !
Complex SUM Query
I have the following query, it's working but was just wondering whether there is a way to trim it down/optimize it? SELECT `order`.*, computer.Price AS Price, price_discount.PDiscount, price_tax.PTax AS VAT, ROUND(SUM(Price-(price_discount.PDiscount/100)*Price) + (((price_tax.PTax)/100)*(Price-(price_discount.PDiscount/100)*Price)),2) AS SubTotal, shipping.SPrice, ROUND(SUM(Price-(price_discount.PDiscount/100)*Price) + (((price_tax.PTax)/100)*(Price-(price_discount.PDiscount/100)*Price) + shipping.SPrice),2) AS Total FROM `order`, price_discount, computer, price_tax, shipping WHERE price_tax.PTID = `order`.PTID AND computer.compID = `order`.compID AND price_discount.PDID = `order`.PDID AND shipping.SID = `order`.SID GROUP BY `order`.OrderID The second SUM (Total) is basically the first sum but adding the shipping.SPrice. I was wondering whether there is a way to save the first SUM, which ive declared AS SubTotal so that for the second one I just type: SubTotal + shipping.SPrice?
View Replies !
Complex SQL Query
Hi every1. In my domain I have two kinds of objects: Software and Enumerate. It is many-to-many relationship between Software and Enumerate objects. I use mysql database to store them. So I created 3 tables: software, enumerates and soft_to_enum to hold relationships between objects. software ------------- id name char(255) developer char(255) enumerates --------------- id type char(255) name char(255) soft_to_enum ------------------- soft_id enum_id I have no problem inserting and updating this kind of relationship. But when it comes to selecting, I feel confused. I want to select software objects which links to at least 3 enumerate objects with a type equal to string ‘os’. I write this SQL statement: SELECT s.id, s.name, s.developer, s2e.soft_id, s2e.enum_id, e.id, e.type, e.name FROM software s INNER JOIN soft_to_enum s2e ON s2e.soft _id = s.id INNER JOIN enumerates e ON s2e.enum _id = e.id WHERE e.type =’os’ GROUP BY s.id HAVING count(s.id)>3; It seems to be working. In a result I got several software objects, along with software name and software developer name. And I got column values for only one enum record. But the problem is that I want to load all the enum records, not only one, connected to selected software record. How can I make this by only one SQL call?
View Replies !
Help With Complex (I Think) Query Please
I am building a site with PHP and MySQL. I am writing a function to get data from a table, but I need to select distinct rows from within distinct rows, and more. The function I have written so far contains about three queries, and the way I see it, that is jut not good enough. I know this is not the PHP forum, but I am looking for help on writing a query which will incorporate all three, to save server load, and time - the fabled "let MySQL do all the work". Basically, I have a table of various client jobs. Each record has a unique id, as well as a numeric project_id, used to group records from the same job together. At the same time, each record has a job type (varchar) This could be "installation", "brochure", "website" etc. So, each project is made up of various job types. I need to select the distinct project_id, thus grouping all work from the same project together, at the same time, I need to select distinct job types from within each project. We could have two websites done in the same project, yet, I do not want to display "websites" twice on my page. There is also a client_id field and a client lookup table ("clients") I also need to join this table with the corresponding record in the clients table to get the client name. I am not sure if I have explained to well, but here is my PHP code to try and show what I am trying to achieve. PHP function showRecentWork($limit){ // get the projects from client_work table $s_getWork = "SELECT DISTINCT * FROM client_work GROUP BY project_id ORDER BY project_date DESC LIMIT ".$limit; $q_getwork = mysql_query($s_getWork); $numjobs = mysql_num_rows($q_getwork); while($recent_work_item = mysql_fetch_array($q_getwork)) { // get the client id $project_id = $recent_work_item['client_id']; // get the project id $project_id = $recent_work_item['project_id']; // get the various job types for this particular project $s_getjobtype = "SELECT DISTINCT * FROM client_work WHERE project_id=".$project_id ." GROUP BY job_type"; $q_getjobtype = mysql_query($s_getjobtype); $num_jobtypes = mysql_num_rows($q_getjobtype); // build the list of job types while($this_jobtype = mysql_fetch_array($q_getjobtype)) { $thistype = $this_jobtype['job_type']; $job_type .= " <a href="/work/".$thistype.".php">".$thistype."</a>"; } // get the client name from the clients table $s_getclient = "SELECT * FROM clients WHERE id=".$clientId; $q_getclient = mysql_query($s_getclient); $r_getclient = mysql_fetch_array($q_getclient); $client_name = $r_getclient['client_name']; .... //Print out all the data }}
View Replies !
Complex Query Over Two Databases..
Can someone please help me combine these two queries? Basically, there are 3 columns in the ebluecard table that refer to agent. I need to do this first query, but filter by the office which is in the agents table in a different database. Is this possible? This is the main query:
View Replies !
Sort A Complex Query
$query = "SELECT page.* FROM `page` LEFT JOIN `keywords` USING (`page_id`) WHERE MATCH (`keywords`.`keyword_txt`) AGAINST ('$radio_keyword' IN BOOLEAN MODE) UNION SELECT page.* FROM `page` WHERE MATCH (`title`, `descrip`) AGAINST ('$radio_keyword' IN BOOLEAN MODE) UNION SELECT page.* FROM `page` LEFT JOIN `url_pages` USING (`page_id`) WHERE MATCH (`url_pages`.`page_url`) AGAINST ('$radio_keyword' IN BOOLEAN MODE)"; How do I ORDER BY `page`.`title` for the entire query? ie. I want to get the whole query sorted by the page.title field, but I don't see a way to slap on an "ORDER BY" on the whole query.
View Replies !
Yet Another Complex Php/mysql Query
im trying a select, compare, update sota query thingy 'm' jig.. what i need to do is: i have a 2 rows in a table(table=users) called gcount and credits both rows are numeric. im trying to build a query that a) updates gcount by gcount = gcount + 1 b) if gcount = 5 reset gcount to 0 update credits = credits+1 so basically what im after is a query that adds 1 to the g count every time but if the gcount reaches 5... it is reset to zero and credits is reset to + 1 is this too much to ask from one query? am i totally bonkers and you have no idea what i mean?
View Replies !
More Complex Select Query
i tried to do this also looking at documentation from mysql site, but keep getting error messages: basically i got data on a table, there is a field 'views', and a field 'published_on', related to articles. what i need is a query that selects the articles and orders them according to views/day. so, i'd need some operation that first figures how many days the articles have been up: DATEDIFF(NOW(), published_on) then, i gotta divide that by published_on, and make the query return first the articles that have had the most views a day. any idea how i can accomplish this?
View Replies !
To Re-write Complex Query With JOINS
I have a problem with one of the JOIN query here. Below is a brief description of the problem. tablename : test RecordId EffectiveDateothertableidvalue -------- ------------- ------------ ----- 12004-01-10110 22004-01-20120 32004-01-20270 42004-01-10280 52004-01-15110 62004-01-25310 Output : RecordId EffectiveDateothertableidvalue -------- ------------- ------------ ----- 22004-01-20120 32004-01-20270 62004-01-25310 Now I want to use a single SQL query to find a result where there exist one record for each unique "othertableid" where the record selected for the "othertableid" should be the recent one with regard to "EffectiveDate". That is from the above records, I want to select Records with "RecordId" = 2 and 3 because they are the recent one for "othertableid" = 1 and 2 respectively. Please be sure that I want to retrive all fields including "RecordId". The result should not depend on any other fields but "EffectiveDate" only. I am using MySQL 4.0.12 and it does not support "SUBQUERIES" which is now given support in latest MySQL edition. But I have read in the manual of MySQL that any "SUBQUERY" SQL statement can be written with proper "JOINS".
View Replies !
Need Single COUNT Value For Complex Query
I have a moderately complex query and I want to page the results, limiting to 50 on a page. I know that in order to do so, I need to know the total number of records so that I know whether there are more records remaining so I can show a 'NEXT PAGE' link. I'm having trouble constructing a COUNT query from my original query because the query accesses 4 tables, two of which are many-to-one association tables. Here is the original query, without a LIMIT clause:
View Replies !
How To Create Complex Query With Group By And Count
I have a table usertags: usertags ------------ - usertagid - usertagsiteid - usertagtext I want to select the usertagtext and count of usertagtext grouped by usertagtext. I only want it to show usertagtext that also have a match in the usertagsiteid to a given value for usertagtext. Not sure if that makes sense so here is an example: ......
View Replies !
Creating An A To Z List From Query
I am creating an a to z list - basically a count of all results that start with the letter "A", "B", "C" .... and so on. I am pretty poor at SQL so I am sure some brains out there can do better than I have here. What I have is working, I just want to make sure that it is optomized. So let's assume I have some query "$query" that I want to run and get an A..Z list based on column "$column". Let's further assume that '$query" produces the following results, and that $column is equal to "last_name". last_name --------------- Anderson Bitmore brown Bogus My AZlist query would look like this: select * from (SELECT count(alist.$column) as a from ($query) as alist where alist.$column like 'a%' or alist.$column like 'A%' ) as a_result, (SELECT count(blist.$column) as b from ($query) as blist where blist.$column like 'b%' or blist.$column like 'B%' ) as b_result, .... (SELECT count(zlist.$column) as z from ($query) as zlist where zlist.$column like 'z%' or zlist.$column like 'Z%' ) as z_result; And this retuns the following result: a | b |...| z -------------- 1 | 3 |...| 0 Meaning that $query has 1 result where the first letter in $column is "A" or "a", 3 results where the first letter is "B" or "b" and 0 results where the first letter is "Z" or "z". What I am afraid of here is that "$query" is being executed 26 times (once for each letter of the alphabet) . Is there a way to refine this, or is MySQL (4.x and 5.x) smart enough to optomize this on its own?
View Replies !
Creating An Optimal Query
What I am doing at present is displaying a list of topics such as on a forum main page. That data is in a table say "my_topics" for example The list I am displaying also references data in another table say "my_images". Now each item in my_topics could reference zero or more items from my_images and they would need to be displayed alongside that topic. So each item in my_images has an id which is an index into the my_topics table. At present I am dumping the entire my_images table into an array so I can access it without having to do multiple sql queries. I can't use "left join" as far as I know because the relationship between my_topics and my_images is one-to-many rather than one-to-one.Obvious downside is that as my_images table gets larger - dumping it to an array increases the memory usage of the script which eventually leads to it not working.
View Replies !
Creating A Query From A Form
I'm struggling building a SQL query from the output of a form, i.e. the user inputs into a form which in turn decides the query. I have never done this before and was just wondering if anyone had any links tutorials of something like this!? I have searched but haven't found anything too useful yet. Basically all I want to do is for the user to pick from a drop down menu how they want a leaderboard displayed, i.e. top 50 results, bottom 50, 50 to 100 results, etc. Do I just tie a complete SQL statement with the corresponding LIMIT info inside to a variable. The variable being the value of the chosen item in the drop down menu. Pseudocode as follows:
View Replies !
Creating Table From A Query
I have just put a forum up on my web site. The database tables were readymade via phpbb. Because I want to send newsletters I need a table that just contains usernames and email addresses. The table for users in the phpbb forum is huge so i did sql query: SELECT user_id, username, user_email FROM USERS; Which gave me all the information I need to send the emails on mass when this table is linked to a newsletter application. however I am aware this is just a query. Can I create a new table from this query? So i just have a table of users and emails. If so how do I do it? I am using phpMyadmin.
View Replies !
Complex Query - UPDATE Within UPDATE?
Edit: Before anyone leaves this thread, don't be put off by the regular expressions! They are not the problem, so please stay and read. OK, this query has got my head spinning. I am basically creating a query that goes through each product in a table to update the stock for that particular item with that particular size (i.e. I am talking about shoes - different models and each model has different sizes (uk kids 12 -> uk 11). With each shoe it does (or is meant to do) the following: 1. The PHP script that runs the query is looping through every size outside of the query 2. So for each of these sizes it checks to see whether the product it is currently on matches the size it is on 3. When it finds the size it is on, it then deducts the correct number of units from the stock table 4. The final WHERE clause makes sure this subquery inside the UPDATE only happens when the StockUpdated field of the Product table equals 0 (in other words, the stock hasn't been counted before) Basically what I need to do, is first to make sure what I currently have got does the above correctly but also I need the query to UPDATE the StockUpdated field to 1 only when it has been updated successfully. How could I do this? Unfortunately I cannot just add an extra update entry to the end of the query as this would update the StockUpdated field regardless of whether it has been properly counted or not. Here is the query I have so far (with a little simple PHP around it doing the loop): PHP $shoesizes = array(1 => 'ukk12','ukk13','uk1','uk2','uk3','uk4','uk5','uk6','uk7','uk8','uk9','uk10','uk11'); $numshoesizes = count($shoesizes); for($i = 1; $i < $numshoesizes; $i++) { $stockupdate = " UPDATE heelys_stock,items_ordered SET heelys_stock.size_".$shoesizes[$i]." = (SELECT CASE WHEN SUBSTRING_INDEX(items_ordered.Product,',',-1) REGEXP '( )?( )?(Kids)?( )?( )?(UK)?( )?( )?(Kids)?( )?( )?[^0-9]12( )?(' -- if UK Kids 12 THEN heelys_stock.size_ukk12 - (items_ordered.Amount/items_ordered.Price) WHEN SUBSTRING_INDEX(items_ordered.Product,',',-1) REGEXP '( )?( )?(Kids)?( )?( )?(UK)?( )?( )?(Kids)?( )?( )?[^0-9]13( )?(' -- if UK Kids 13 THEN heelys_stock.size_ukk13 - (items_ordered.Amount/items_ordered.Price) WHEN SUBSTRING_INDEX(items_ordered.Product,',',-1) REGEXP '( )?(UK)?( )?[^0-9]1( )?(' -- if UK 1 THEN heelys_stock.size_uk1 - (items_ordered.Amount/items_ordered.Price) WHEN SUBSTRING_INDEX(items_ordered.Product,',',-1) REGEXP '( )?(UK)?( )?[^0-9]2( )?(' -- if UK 2 THEN heelys_stock.size_uk2 - (items_ordered.Amount/items_ordered.Price) WHEN SUBSTRING_INDEX(items_ordered.Product,',',-1) REGEXP '( )?(UK)?( )?[^0-9]3( )?(' -- if UK 3 THEN heelys_stock.size_uk3 - (items_ordered.Amount/items_ordered.Price) WHEN SUBSTRING_INDEX(items_ordered.Product,',',-1) REGEXP '( )?(UK)?( )?[^0-9]4( )?(' -- if UK 4 THEN heelys_stock.size_uk4 - (items_ordered.Amount/items_ordered.Price) WHEN SUBSTRING_INDEX(items_ordered.Product,',',-1) REGEXP '( )?(UK)?( )?[^0-9]5( )?(' -- if UK 5 THEN heelys_stock.size_uk5 - (items_ordered.Amount/items_ordered.Price) WHEN SUBSTRING_INDEX(items_ordered.Product,',',-1) REGEXP '( )?(UK)?( )?[^0-9]6( )?(' -- if UK 6 THEN heelys_stock.size_uk6 - (items_ordered.Amount/items_ordered.Price) WHEN SUBSTRING_INDEX(items_ordered.Product,',',-1) REGEXP '( )?(UK)?( )?[^0-9]7( )?(' -- if UK 7 THEN heelys_stock.size_uk7 - (items_ordered.Amount/items_ordered.Price) WHEN SUBSTRING_INDEX(items_ordered.Product,',',-1) REGEXP '( )?(UK)?( )?[^0-9]8( )?(' -- if UK 8 THEN heelys_stock.size_uk8 - (items_ordered.Amount/items_ordered.Price) WHEN SUBSTRING_INDEX(items_ordered.Product,',',-1) REGEXP '( )?(UK)?( )?[^0-9]9( )?(' -- if UK 9 THEN heelys_stock.size_uk9 - (items_ordered.Amount/items_ordered.Price) WHEN SUBSTRING_INDEX(items_ordered.Product,',',-1) REGEXP '( )?(UK)?( )?[^0-9]10( )?(' -- if UK 10 THEN heelys_stock.size_uk10 - (items_ordered.Amount/items_ordered.Price) WHEN SUBSTRING_INDEX(items_ordered.Product,',',-1) REGEXP '( )?(UK)?( )?[^0-9]11( )?(' -- if UK 11 THEN heelys_stock.size_uk11 - (items_ordered.Amount/items_ordered.Price) FROM items_ordered WHERE items_ordered.StockUpdated = 0) WHERE (heelys_stock.id = (SELECT heelys_stock.id FROM heelys_stock,heelys_shoe WHERE SUBSTRING_INDEX(items_ordered.Product,',',1) = heelys_shoe.full_shoe_name)) , items_ordered.StockUpdated = 1" // at the moment this last update of the items_ordered table happens to every record!!! even if the other part of query fails // update stock for size $i mysql_query($stockupdate); } Hope someone can see how I can do this? I've been working on this query for 2 or 3 hours now and I've been making reasonable progress but now I am really stumped.
View Replies !
Creating A Query Based On Dates
I am trying to write a query (in PHP) which selects from a database all of the items which are in the future. My query is as follows SELECT * FROM news WHERE ((news.date)>$today ORDER BY date where news is my database, news.date is the the field which holds the date for the item and $today will be replaced my current date. At the moment it seems to display all values, which suggest its not functioning properly.
View Replies !
Creating A Query That Will Only Return Records With Matching Counterparts
I'm using the table below as an example. I want to create a MySQL query that will return only records that have matching counter-parts where 'col1' = 'ABC'. Notice the 'ABC / GHI' record does not have a counter-matching 'GHI / ABC' record. This record should not be returned because there is no matching counter-part. With this table, the 'ABC / GHI' record should be the only one returned in the query. How can I create a query that will do this? id | col1 | col2 -------------------- 1 | ABC | DEF 2 | DEF | ABC 3 | ABC | GHI 4 | DEF | GHI 5 | GHI | DEF
View Replies !
Complex SQL
I'm using MySQL 3.23 (and no they won't let me upgrade). I have a table which has data for all the actions related to our cases. I need to construct a SQL query to find the most recent action of each type for a certain case. I've tried the following select ID, actn_type, date1, result from actions where case_no = "12345" This of course returned all of the actions. I only want one for each actn_type so I tried select ID, actn_type, date1, result from actions where case_no = "12345" group by actn_type This sorta worked in that I now got only one result for each actn_type, but it selected the ID and date1 from the oldest record. So I tried select ID, actn_type, date1, result from actions where case_no = "12345" order by date 1 group by actn_type I got some annoying syntax error, it wants the order by clause after the group by But when I did that I still got the same results as the previous query. I also tried order by date 1 ASC and DESC but that only changed the order of the results instead of giving me the result I want. At this point I think I'm on the totally wrong track and might have to just resort to sending a number of queries to the database to get the results I want. Just to recap, I need a query that will select ID, actn_type, date1, result for a specific case_no where I only get one record for each actn_type that exists and I get the most recent date1, ID, result for each actn_type.
View Replies !
Complex Using Like
I have a bad situation in using like i wana make a like statment that get the all records which start with char. or number , A-Z and 0-9.. do I have to write 36 or in my query or there is another way ???
View Replies !
Complex Problem
I have a problem I could use some help with. (I'm new to SQL). I have a table with 4000+ user details, incuding email addresses. I need to send out one email per domain except where the domain is an ISP, or email provider such as hotmail. The best way for me to do this would be to show all the records where a domain has more than one email address on it, and then manually go through and delete superfluous records from the domains which are not ISP's or email providers. I need suggestions on how best to tackle this. I've split up the email column into two seperate columns : username and domain. I've tried the following : SELECT count(*) AS num, id, firstname,lastname,username,domain FROM users GROUP BY domain HAVING num > 1; but this only returns one record per domain. I need to return all records where a domain has more than one email address so I can selectivly delete the records I don't need. I'm pretty sure the answer lies in creating a seperate 'domains' table and setting up a relationship between domains and users, but my attempts so far have been fruitless.
View Replies !
Complex Joins
Here is the problem. Table_1 (gl_Train_KeyIdeas) +----------------------+----------------+-------------------+---------------+ | KeyIdea_ID | Unit_ID | Group_ID | Title | +----------------------+----------------+-------------------+---------------+ | 1 | 1 | 27 | yada 1 | | | | | | | 2 | 1 | 27 | yada 2 | | | | | | | 3 | 1 | 27 | yada 3 | | | | | | | 4 | 1 | 27 | yada 4 | | | | | | +----------------------+----------------+-------------------+----------------+ Table_2 (gl_Train_Progress) +----------------------+----------------+----------------------+ | ID | User_ID | KeyIdea_ID | +----------------------+----------------+----------------------+ | 12 | 5 | 3 | | | | | | 11 | 5 | 2 | | | | | | 10 | 5 | 1 | | | | | +----------------------+----------------+-----------------------+ The following sql returns field KeyIdea_ID = 4 which is the only KeyIdea not in both tables. SELECT gl_Train_KeyIdeas.KeyIdea_ID FROM gl_Train_KeyIdeas LEFT JOIN gl_Train_Progress ON gl_Train_KeyIdeas.KeyIdea_ID = gl_Train_Progress.KeyIdea_ID WHERE (((gl_Train_Progress.KeyIdea_ID) Is Null) AND ((gl_Train_KeyIdeas.Unit_ID)=1)); What I seem to be having trouble with is specifying the User_ID in table 2. I need to specify the current user for example: an sql with a User_ID = 6 would return KeyIdea_ID of 1, 2, 3, and 4.
View Replies !
Complex Join
I have a table called users which has primary id 'username', and includes fields called jobpref1, jobpref2, jobpref3, jobpref4, jobpref5. These fields determine a user's rank of job preferences.Values in these columns are id's of another table, 'jobs', which also has a column called 'name'. So all the jobpref# columns' values correspond to 'jobs' primary key column. What I need is a select query to join the names of the jobs to each of the jobpref#s with a user's username. I'm befuddled as to the proper join I need. I think I could work with a group_concat result as well, if that will accomplish my goal.
View Replies !
Complex Record
I'm looking for any insight on how to store a record in MySQL (though it's probably not a very complicated record). Essentially I want to store information about a t-shirt. I only want to have one record for a particular t-shirt, but have all attribute information (size, color, style etc.) associted with that shirt to be in the same one record. They way I have it now is that the main table (shirt) would end up with several records for the same shirt simply due to the shirt having several colors and sizes. I simply want one record for the shirt regardless of the colors and sizes available.
View Replies !
Complex Delete
Can anyone help with a mysql delete command ? I have a table called Events with id =auto inc number startdate = datetime I need to delete the oldest 1000 records when the the count of (id)>31000 The following would delete the oldest 1000 events delete * from Events order by starttime limit 1000; I need this to only happen when you have more than 31000 ids. delete * from Events where count(id)>31000 order by starttime limit 1000;does not work.
View Replies !
Complex Select
I have a table with 6 fields: id, project, section, division, subdivision, title. Data like: 0 10 1 1 1 'Title111' 1 10 1 1 2 'Title112' 2 10 1 2 1 'Title121' 3 10 1 2 2 'Title122' 4 10 1 3 1 'Title131' 5 15 1 1 1 'Title111' 6 15 1 1 2 'Title112' 7 15 1 1 3 'Title113' 8 15 1 2 1 'Title111' 9 15 1 2 2 'Title112' As you can see project 10 has: 4 10 1 3 1 'Title131' and project 15 has: 7 15 1 1 3 'Title113' And there are 4 records that match section, division, subdivision and title in each project. 1 1 1 'Title111' 1 1 2 'Title112' 1 2 1 'Title121' 1 2 2 'Title122' I am looking for a select that will give me the following data: 1 1 1 'Title111' 1 1 2 'Title112' 1 1 3 'Title113' 1 2 1 'Title121' 1 2 2 'Title122' 1 3 1 'Title131' Basically, I want all records from both projects but only 1 of the records that have similar data.
View Replies !
Complex Ordering
I would like to order query results by a numeric column containing positive numbers and zeros at a descending order, except that results with a zero 0 should appear last (e.g.: 2, 10, 15, 16, 0). Can I do that in a single query, or do I need to use to successive queries or order my results in the programs that receives them before displaying them?
View Replies !
Complex UPDATE -
Here is code, that runs well on PostgreSQL and MS SQL Server: update TTable set summ=(select Count(1) from TTable t2 where t2.mybool=TTable.mybool); This means that each record (lets name it 'A') in TTable will have field summ filled with the number of the records in this table where the value of mybool field is the same as it is for record A. MySQL returns error 1093.....
View Replies !
Complex Index
I am trying to find out if there is any way of creating a complex index on a table. My data sample is: a bird can be banded once (coded as '1') the bird can be retrapped many times (coded as '2') the bird is recovered dead once (coded as 'X') How can I create a code that allows codes '1' and 'X' only once, but code '2' infinate times? I currently have a index on Band number, date and code. But this allows multiple code '1's if the dates are different. A uniue index on only the band number and code does not allow multiple code '2's Seperating the data into different tables is not an option, so currently I am doing the checks via a VB front-end. This is slow and cumbersome. I need a way to do this more efficiently.
View Replies !
Complex Statement
Hopefully you guys can help me out on this: the language is PHP, the database is MySQL Here is my PHP function getBounces($start,$end) { $profilecode = $this->PROFILECODE; $sql = "SELECT DISTINCT(sessionid) FROM `profiles_d` WHERE profilecode='".$profilecode."' AND tstamp >= ".$start." AND tstamp <= ".$end." ORDER BY sessionid ASC"; $query = mysql_query($sql, $this->DATABASE); $bouncers = 0; while($rs = mysql_fetch_assoc($query)) { $q2_sql = "SELECT * FROM profiles_d WHERE profilecode='".$profilecode."' AND sessionid='".$rs['sessionid']."' AND tstamp >= ".$start." AND tstamp <= ".$end." ORDER BY tstamp ASC"; $q2 = mysql_query($q2_sql, $this->DATABASE); if(mysql_num_rows($q2)==1) { $rs2 = mysql_fetch_assoc($q2); if($rs2['curpage']=="/index.html") { $bouncers++; } } } return $bouncers; } Little bit of background.. there is a table called: profiles_d in it is a record for each page hit. it has a sessionid and a page name. What I need is the number of sessions that only have 1 page hit for a given date. So if viewer 1 had 20 page hits, there would be 20 records in the database. it's part of a analytics system... From that, I use temp tables and clean it up to 1 record per session.... Is there a way to do that calc in ONE sql statement?
View Replies !
Complex MySQL/PHP
I have a MySQL/PHP project where I need to assign a Red (R) or Blue (B) store coupon to randomly selected contest winners. There are 2 tables (winners, stores). Each store has a region_code and contest winners also have region_code for where they live (both are CHAR(3). Here's where it gets tricky. Each store can only give out 3 coupons (either 3 Red or 3 Blue). Additionally, the winners location (winners.region_code) should be assigned a store coupon from the same region (stores.region_code), until the matches run out, and leftover coupons are assigned to winners that live anywhere. After all this, I'll write the final results to a table. I can randomly select correct # of winners, and display them in a repeat region just fine. However, I'm unsure about the logic behind assigning data from the stores table to those results, and how to iterate through and 'stop' assigning coupons from each store after 3 coupons. Not sure if this requires some sort of 'view'. Not looking to get out easy, just for some advice on how I should go about this.
View Replies !
Complex Limiting In A Join
How do I limit a certain amount of records from a join, ie (quick and idealist ***not for accuracy***) SELECT * FROM players p, games g LIMIT **first 5 games for each player, but return all players*** is this possible without a subquery?
View Replies !
Complex Calculated Columns
In MSAccess and other databases one can define a view and specify a custom function to calculate a column. How can this be done in MySQL? I can use version 5 with stored procedures etc. My custom functions are too complex to be placed as expressions directly in the SELECT statement like QTY*PRICE. Ordinarily I would just calculate these "custom calculated" columns on the fly in the client but I need MySQL to order by them before the result table arrives in the client. I need a clean implementation. This is not a one off hack but occurs time and time again in various guises in an app that I would like to convert to MySQL.
View Replies !
Multiple Complex Joins
How can one link two fields in one table to single field (Primary key) in another table in a single (left join) query, to return two values? e.g. Table 1 ID (PK) ...other fields Departure_ID Arrival_ID ...other fields "places" table Place_ID Placename (value to use in view) I think this is illegal but, more probably, impossible; perhaps someone could suggest an alternative methodology.
View Replies !
Complex Queries On Many-to-Many Relationship
I'm building a system that involves performing fairly complex queries against a set of member data. The member data consists of answers to a series of questions, each question is multiple choice, with the possibility of the user selecting multiple answers to a question. As such, Answers to Member accounts is a many-to-many relationship. My table structure currently looks like this: (with extra, un-related fields removed) Code:
View Replies !
Complex Column Names
i have a table with name table1. That table has a column with name thing1/thing2, when i want to do a query like: select thing1/thing2 from table1; shows an error sin it interprets as if i wanted the column thing1 divided by the column thing2 (their values).
View Replies !
Complex SQL Statement Construction
am confused how to construct this SQL statement am using this statement to get what i want SELECT * FROM table (to get all the records) SELECT * FROM table WHERE status = Ƈ' (To get all record where status=1) SELECT * FROM table WHERE status = Ɔ' (To get all record where status=0) how to combine this statements into one is this possible using MySQL By the way if it’s possible to do such statements where I can find tutorials on this kind of complex statements constructions.
View Replies !
Complex (for Me) Join Question
This is my first post, as I have reached the end of my brain and could really use some help from those who know what they are doing. First, I am on MySQL 4.0, so subqueries are out. Second, my problem goes like this: I have three tables, a data table, a categories table, and a linkage table. Categories are arranged hierarchically, so the categories table has 'left' and 'right' columns to keep things in line. Until now, if I know the left and right values of a certain category (for example, 5 and 11, I have been successfully executing queries telling me all the data entries in category 'X' by doing the following: SELECT DISTINCT data.some_column FROM data LEFT JOIN linkage ON linkage.link_data_id = data.id LEFT JOIN categories ON categories.id = linkage.link_cat_id WHERE categories.left >= 5 AND categories.left <= 11 As I said, this all works fine, but now I want to be able to find all the data NOT in a certain category. Unfortunately, since each row is currently unrelated, and items can exist in many categories, simply adding NOTs to the WHERE clause just ends up returning basically everything. Another way to think of it is if data is assigned to both the 'round' and 'red' categories, asking for everything that is 'red' works as expected, but asking for everything that is 'not red' still returns the 'red round' object because 'round' satisfies the 'not red'. Does this make sense to anyone? Am I way off? If not, how do I need to join this tables to successfully exclude the elements I want excluded?
View Replies !
A Complex Find And Replace
I have a table with a column I need to modify, I think, with a find and replace. The column contains a number currently with no decimals that I need to convert to a number with a decimal. I need to add a decimal 6 numbers from the right. For instance, 12345678 needs to become 12.345678. All of the numbers have will have at least one number to the left of the decimal, sometimes with 2 or 3 and possibly a negative sign. (They are lat and long coordinates that did not come with a decimal in them.) Does anyone have any thoughts on how to accomplish this with MySQL or, as a last resort, outside of MySQL?
View Replies !
Two (rather Complex) Problems. Help Needed!
Problem 1 I am developing a system where members of a site can submit graphic designs to a themed pack. Members can then view and rate the submissions from 1-5. This has all been accomplished, but I have encountered one problem. When listing the themed packs currently in progress, I want to be able to list how many submissions there are, how many of these submissions have a rating above 3.0, and how many of these submissions are above 4.0. I've been able to flawlessly count how many submissions there are without referring to their ratings with this query: select p.id as id, p.title as title, p.body as body, p.reltime as reltime, p.quota as quota, count(s1.id) as subcount_ns, from packs as p left outer join submissions as s1 on s1.pack = p.id where s1.state > -1 and p.released = 0 group by s1.pack order by p.id asc; How can I also, within this query, count how many submissions have a rating above 3.0 and count how many submissions have a rating above 4.0? I'm not sure how to do this, since I have already joined the submissions table to the packs table. The ratings table (sub_ratings) looks like this: id | subid | userid | rating | time The subid column refers to the submission's id, the userid column refers to the id of user who rated the submission, and the rating is an integer between 0 and 5. -------------------------------------------- Problem 2 For the submission system, some users choose to collaborate with other users on one submission. In this case, both of their userids are grouped together in the "creators" column of the submission, being separated by commas. Here is an example: 1,43,65 This would be an example of three users having collaborated on one design. Is there any possible way that I could, within one mysql query, determine the usernames of each of those users and return it as a column with the requested submission? I'm looking for something along the lines of php's "explode" function, but in mysql. Thanks in advance for the help that anyone offers.
View Replies !
Combining Two Complex Queries
I'm dealing with hierarchical data using the modified adjacency list model. In implementing the ability to move entire subtrees, I've come up with the following two queries:
View Replies !
Complex LOAD DATA INFILE
I am using mysql 5.0 and I want to load a huge txt-file in my database. My text file (file.txt) looks like: col1 col2 col3 ... col200 col1 col2 col3 ... col200 .... col1 col2 col3 ... col200 I now want it to import in a table t1 with two columns (col_nr, col_val) where col_nr is the number of column (e.g. 2 for col2, 46 for col46) and col_val are the effective values in my txt-file at the different columns. The problem is that col_nr is not in the "file.txt" so I have to assign based on the field number. I am looking at the LOAD DATA INFILE command, but in the help file I did not find an answer to my question. I was hoping to do it with: LOAD DATA INFILE 'file.txt' INTO TABLE t1 (col_val) SET col_nr = "how do I do this"; Does anyone has any suggestions or tips to do it differently (with php perhaps)?
View Replies !
Complex Mysql 3.23 LEFT JOIN
I've got a table of things called elements. They're components, sort of like amino acids, which come together to form complex web pages -- as nodes in trees which form parent-child relationships, sort of like newsgroups. For example, the parent_id field points to another element. Indent_level is there for denormalization purposes, to avoid costly recursive issues in querying. The element_page_id allows pages to link to other pages via elements (pretty complex, I daresay, but don't worry about that part). Each row must have certain information, but may only have one value (a numeric foreign key reference) among the following: resource, location, service, staff, subject, and element_page_id. The others are NULL. I've marked them below betweens rows of asterisks. In this example, only service_id actually needs to perform a LEFT JOIN to fetch information about it: Code:
View Replies !
Complex (tree Based) Db Design
Firstly, here is the data structure... Account User Site Notes Note0 Menus Menu0 MenuEntry0 MenuEntry1 MenuEntry2 Pages Page0 Paragraph0 Image0 Paragraph1 etc... As you can see, the data is essentially a tree structure. Hence I've decided to use an MPTT design to index the data. Each type of item is stored in a different table, i.e. table_paragraphs, table_images, table_menus, table_menu_entries etc. My problem is that I'm not sure as to the best way to link the tree nodes to the different tables. Should I include the object type in the tree nodes and use my server side language to extrapolate the query from that or... ?
View Replies !
Complex Select (Possible Subselect Needed?)
I have a table, b5_assignment_lookup, that is used elsewhere as a lookup but I'm trying to use the data contained by itself here. The table: CREATE TABLE b5_assignment_lookup ( as_id int(11) NOT NULL auto_increment, as_blog int(11) NOT NULL default Ɔ', as_blogger int(11) NOT NULL default Ɔ', as_milestone enum('start','finish') NOT NULL default 'start', as_timestamp timestamp NOT NULL default CURRENT_TIMESTAMP, PRIMARY KEY (`as_id`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=222 ; By running this query, I get the following resultset: mysql> SELECT * FROM b5_assignment_lookup WHERE as_blog = ྕ' AND as_timestamp <= ��-09-30' +--------+----------+-------------+---------------+---------------------+ | as_id | as_blog | as_blogger | as_milestone | as_timestamp | +--------+----------+-------------+---------------+---------------------+ | 87 | 89 | 41 | start | 2006-05-01 00:00:00 | |208 | 89 | 41 | finish| 2006-09-02 11:55:27 | |209 | 89 | 103 | start | 2006-09-02 11:55:27 | +--------+----------+-------------+---------------+---------------------+ 3 rows in set (0.00 sec) What we have here is that Blogger 41 began writing on Blog 89 on May 1, and on Sep 2 Blogger 103 took over for Blogger 41. What I really need to grok is all bloggers who blogged all or a part of a range of dates. For example, I really want to find out which bloggers blogged on blog 89 for all or part of 2006-09-01 to 2006-09-02. I use this dataset as an example, but we might have completely different circumstances such as Blogger 20 being replaced on the third day of the date range, being replaced by blogger 29 for 10 days and then quitting due to lack of time and the blog going unmanned for 5 days before Blogger 20 decides to step back in on day 25. The flags are the start/finish, obviously.
View Replies !
Complex Searching And Multiple Queries
I have a database of a list of companies and descriptions where users can login and see reports from companies they are subscribed to. 1) I want users to be able to search for a keyword and have it search the ENTIRE database for the keyword and return all rows. 2) For those returned rows, I want to cross-reference the permissions table and have it: ---A) return the company name and description if they are subscribed ---B) print that they are not subscribed to this company. Here is a simple version of the database: user ==================== id | username ==================== 1 | bob 2 | john reports ======================================= id | description | company ======================================= 1 | Green | Crayola 2 | Green | Bic 3 | Blue | Papermate permissions =========================== user_id | report_id =========================== 1 | 1 1 | 2 2 | 1 I need to put these two statements together(shown below). I need to have it search the DB and return all rows, then check to see if the person has access to that company. If they do, show it to them, if not print that a company was found but they are not subscribed to it. So if John was logged in and searched for "Green" his results would return two results, one would show that he could see the report from "Crayola" and it would also show that the company "Bic" was found, however he is not subscribed. Search results: 1) Crayola - click here to see the report 2) Bic - you are not subscribed to this company. Here is a look at the MySQL I have been trying to use: PHP $query1 = "SELECT * FROM `emt_report` WHERE `company` LIKE '%$P_search%' OR `description` LIKE '%$P_search%' OR `market1` LIKE '%$P_search%' OR `market2` LIKE '%$P_search%' OR `market3` LIKE '%$P_search%' OR `market4` LIKE '%$P_search%' OR `market5` LIKE '%$P_search%' OR `market6` LIKE '%$P_search%' OR `location` LIKE '%$P_search%' OR `date_year` LIKE '%$P_search%' OR `date_month` LIKE '%$P_search%' OR `source` LIKE '%$P_search%'" PHP $query2 = "SELECT u.id , u.username , p.user_id , p.report_id , r.id , r.company , r.description , r.market1 , r.market2 , r.market3 , r.market4 , r.market5 , r.market6 , r.location , r.date_year , r.date_month , r.source , r.video , r.audio , r.pp , r.execsum , r.report_url , r.exec_url FROM user as u INNER JOIN user_reports as p ON p.user_id = u.username INNER JOIN emt_report as r ON r.id = p.report_id WHERE u.username = '$username'" From above, how can i have search by query1 and then have it cross reference query2?
View Replies !
Complex Select For Similar Values In A Column
1) zip_id (an unique id for the row) 2) zip_cd 3) city_name 4) state Now for my dilemna. Within the database, I have several rows such that the zip code is the same and the cities are either completely different or very similar. For example: ID | ZIP_CD | CITY | STATE --------------------------------------------- 1 | 53158 | Pleasant Pr | WI 2 | 53158 | Kenosha | WI 3 | 53158 | Pleasant Prairie | WI 4 | 53158 | Pleasant Prar | WI 5 | 53158 | Pickwick Heights | WI 6 | 53159 | Power Lakes | WI Basically, I want to scan through my entire database and first identify where the zip_code is NOT unique (this will eliminate all the unique rows so I don't have to worry about them). In the above example, it would eliminate row 6 (Power Lakes, WI). Next, I want to look at the city names and compare all the cities that have the same zip code, but eliminate any unique cities based on the substring of the first letter. In the above example, it would eliminate row 2 (Kenosha, WI). This now leaves us with four rows: 1 | 53158 | Pleasant Pr | WI 3 | 53158 | Pleasant Prairie | WI 4 | 53158 | Pleasant Prar | WI 5 | 53158 | Pickwick Heights | WI Ideally, I would like to even eliminate row 5 (Pickwick Heights), but if that's not possible, I would be fine with just returning these 4 rows. My ultimate goal is to weed through the entire database and remove all the "similar" rows and just keep the full city name (for example Pleasant Prairie, WI) and delete all the abbreviated cities. Since I don't think this ultimate goal can be accomplished programmatically, I'm sure the reviewing and deleting part will be a manual process on my part. I'm just looking for a way to narrow down all the rows in my ENTIRE database so I'm only looking at similar city names with the exact same zip code.
View Replies !
|
TRACKER
