Extending Simple Query With Specific COUNT() From Second Table
Currently i basically do following:
-----------------------------------
SELECT cat_id, cat_name FROM categorytable WHERE blah=something ORDER BY pos ASC
while(FetchRowAssoc())
{
SELECT COUNT(*) as itemcount FROM itemtable WHERE category=cat_id
echo cat_it, cat_name, itemcount, blah... etc.
}
------------------------------------
I fetch some info from the category table and then I want to know how many items I have in the 2nd table which are associated to the categories in question from the first table.
Can this be done with a single query statement, so i get from FetchRowAssoc the cat_id, the cat_name and the count of the items for this category?
"SELECT t1.cat_id, t1.cat_name, COUNT(t2) as itemcount FROM categorytable as t1, itemtable as t2 WHERE t1.blah=something ORDER BY t1.pos ASC " <- like this but working, i really don't get how to properly write the syntax there... also didn't found a useful example with googling.
View Complete Forum Thread with Replies
Related Forum Messages:
Simple Count Query
I am trying to use the below query to count the number of images held for each book. Unfortunately it is only returning the books with images. I would like all books returned even if they have no corresponding image. SELECT tblBooks.Title, tblBooks.Description, tblBooks.Author, tblBooks.Annotation, tblBooks.Collection, tblBooks.Price, tblBooks.SortID, tblBooks.Heading, Count( tblImage.ImageID ) AS CountImage FROM tblBooks INNER JOIN tblImage ON tblImage.BookID = tblBooks.BookID GROUP BY tblBooks.BookID
View Replies !
Help Extending Query
I have the below query built mostly by R397 with me looking over his shoulder you can see the original question here anyway what it does is scan thro a table and pulls back the number of days a given date range cross through another date range. Now this works fantasic but I have been thrown a big curve ball and Im stuck again, what i have now been told is that within these set date ranges there maybe occasional specials that cross over between all other dates. For instance they set a special up for the date range of 7 July -1 August but ONLY for those booking on or after the 7th July, now this goes straight thro the middle of the date range 6 july - 6 september. What I need to be able to do is where some one has booked on or after the special's startdate for me to return the number of days there other wise ignore the specials and go about how it was before. Im not sure whether to run a query before the main query in order to catch the specials or can I extend/modify the query I already have. Set Date Ranges HIGH SEASON DATES 1 JANUARY - 4 JANUARY 2006 6 JULY - 6 SEPTEMBER 2006 21 DEC - 31 DEC 2006 1 JAN - 3 JAN 2007 SHOULDER SEASON DATES 9 FEBRUARY - 22 FEBRUARY 2006 30 MARCH - 26 APRIL 2006 25 MAY - 7 JUNE 2006 29 JUNE - 5 JULY 2006 19 OCTOBER - 1 NOVEMBER 2006 LOW SEASON DATES 5 JANUARY- 8 FEBRUARY 2006 23 FEBRUARY - 29 MARCH 2006 27 APRIL- 24 - MAY 2006 8 JUNE - 28 JUNE 2006 7 SEPTEMBER - 18 OCTOBER 2006 2 NOVEMBER - 20 DECEMBER 2006 select sdate , edate , band , datediff(edate,@SD)+1 as days_overlap from season where @SD between sdate and edate and @ED >= edate union select sdate , edate , band , datediff(@SD,@ED)+1 as days_overlap from season where @SD >= sdate and @ED <= edate union select sdate, edate, band, datediff(sdate,edate)+1 as days_overlap from season where sdate >= @SD and edate <= @ED union SELECT sdate, edate, band, datediff( @ED , sdate )+1 AS days_overlap FROM season WHERE @ED BETWEEN sdate AND edate AND @SD <= sdate
View Replies !
Updating On A Specific Count
I tried searching for this, but to no avail. I need to update a "valid" bit in my table (set it to 0), when the total number of rows that have been grouped by column bin_id is less than 5. Ie: bin_id valid 0 1 0 1 1 1 1 1 1 1 2 1 2 1 2 1 2 1 2 1 3 1 here, all rows where the number of rows contained in the respective bin_id is less than 5 are set to invalid. (in this case, there are 5 rows which have bin_id = 2, so valid remains 1. for the rest, there are too few rows of the respective bin_id, so i need to set valid to 0.
View Replies !
Getting Row Data And A Count - Maybe Simple
I have a query in MySQL which gets the details of members of a club. e.g. Select * from members. however, in the same query I want to return the amount of "functions" the member has attended. therefore there is a "functions" table and because functions has a many-many relationship with members I also have a "functionslink" table. To get the amount of functions that a particular member has attended one would write a query like so: select count(functionlink_id) as cnt from functionslink where functionlink.member_id=$MEMBER*_ID What I want to do is just have the one query that not only returns all the member data in its returned row, but also the count of the number of functions they attended. At present using PHP code, I make the two calls separately to construct this data in to a php array, but this involves many more calls to the DB because for each row returned by the first query, I make another call to the DB and something tells me this is a slow bad way of doing things.
View Replies !
Simple Count From 2 Tables
I have 2 tables, Users table: user_id INT user_name user_address Package_id ------------------------ Packages table: Package_id Package_name Package_quota ------------------------ I want to select all packages from table packages, and also count the number of users assigned to each package, so for example if i have "users1" and "user2" both assigned to Package_id=1, so i want a table to show Package ID - Package Name - Package Quota - Number Of Users 1 - Basic - 50MB - 2 2 - Standard - 100MB - 0 3 - Unlimited - 999MB - 0
View Replies !
Simple COUNT For 2 Tables
very simple (in theory), but i dont know what is the correct or effective method. Ive got 2 tables (contact1, contact2) which both have the fields 'viewed' and 'subject'. There is no unique relationship between them, however i do understand that there needs to be some kind of join for the query to be sucessful. What i written at the mo is.. Code: SELECT COUNT(*) FROM contact1, contact2 WHERE contact1.viewed AND contact2.viewed ='n' AND subject <> 'tech' ...obvioulsy its rather crued,
View Replies !
Simple MySQL Count For Non Sql Idiot!
Trying to build a category list with the number of products in each category! current perfectly working code! PHP function fetchCategories() { $sql = "SELECT pd_name, cat_id, cat_parent_id, cat_name, cat_image, cat_description FROM tbl_category, tbl_product ORDER BY cat_id, cat_parent_id "; $result = dbQuery($sql); $cat = array(); while ($row = dbFetchAssoc($result)) { $cat[] = $row; } return $cat; } and now i need to add the number of products that occur within each category..been trying for last hour or so with no luck... Database Schema! tbl_category PHP cat_id cat_parent_id cat_name cat_description cat_image tbl_product PHP pd_id cat_id pd_name pd_description pd_price pd_qty pd_image pd_thumbnail pd_date pd_last_update
View Replies !
SQL Query To Include Count From Associated Table
I am having a tough time coming up with the sql for the statement but the long and short of it is this: Two Tables: Table 1 is a group - Table 2 is a list of items that belong to a group from table 1. I want to query all (*) information from table 1 AND get the count of the number of associated items from that group from table 2 in that same query.
View Replies !
Count One Table's Rows From Multi Table Query
here are my tables (condensed) FEEDS feed_id site_id SITE site_id site_name ARTICLES article_id feed_id link I want to create a query that returns the total number of articles for every site_id (which is unique in the SITE table). I have this: PHP $gsite = mysql_query("SELECT site.site_id, feeds.feed_id, COUNT(articles.article_id) AS acont FROM site,feeds,articles WHERE feeds.site_id = site.site_id AND articles.feed_id = feeds.feed_id group by site.site_id", $connection) or die(mysql_error()); The query does not use JOIN, ON and all that good stuff. I just need the following variables to run through a loop: site_id the number of articles rows per site_id
View Replies !
Query The Article Table And Get A Count Of All Related Comments
Could someone please help me with this query. I have two tables -- one for my articles and another for my comments. Comments are stored in the comments table with a corresponding article id. I want to query the article table and get a count of all related comments. The following query doesn't seem to work for me. Can you please suggest how I fix it? SELECT a.id AS id, a.title AS title, a.preview AS preview, a.thumbnail AS thumbnail, a.category AS category, a.timestamp AS timestamp, a.game AS game, count( b.id ) AS commentcount FROM article_table AS a, comment_table AS b LEFT JOIN comment_table ON a.id = b.article WHERE a.news = 'Yes' AND a.type = Ƈ' AND a.saved != Ƈ' GROUP BY b.article ORDER BY a.timestamp DESC LIMIT 5
View Replies !
Order By Count Of Occurrences In 2nd Table Without Nested Query (MySQL 4.0.27 Workaround)
My ISP is using an old version of MySQL, 4.0.27-max-log. I am using a query that runs fine on my test-server (MySQL 5.0.70-log). The main point of this query is to return an ordered list of volunteers for job assignment. One of the metrics is the number appearances in an activity table. I am using a nested query to COUNT the number of appearances during a specific time window to obtain this metric, but this approach does not appear to work in 4.0.27. Is there a simpler or alternative query that will make 4.0.27 happy? SELECT job10.person_id, people.history, job10.history, ( SELECT COUNT( * ) FROM activity WHERE activity.person_id = people.person_id AND (activity.day = '2009-2-1' OR activity.day = DATE_ADD( '2009-2-1', INTERVAL 3 DAY)) ) AS appearances, CONCAT( first_name, ' ', last_name ) AS name FROM duty_sing_am INNER JOIN duty_people USING ( person_id ) WHERE people.active AND job10.willingness > 0 ORDER BY appearances, job10.history, people.history, job10.person_id
View Replies !
Simple Sql Question: Using A Query Result As A Query Variable
EDIT: it works now, I had an error in my code, not my method. I have a very simple question. I have 2 tables: 'users' and 'posts' with the following structure: users: id, username, email_address posts: id, user_id, post_title, post_text in a my own mind's mysql, I would like to: SELECT posts.id, posts.user_id, posts.post_title, posts.post_text users.username FROM users, posts WHERE posts.user_id = users.id I usually do one query for the post data, and then, based on the use_id record, do another of the users table, but today, I'm being forced to do them in one swoop.
View Replies !
Logic - Extending Database Have Own PK?
I have two databases, I have Contact, then I have Student, right now StudentID is a primary key, and a foreign key referenced by Contact (as ContactID), should I make a new column in Student called ContactID and use that as the primary key instead?
View Replies !
Extending The Limiting Of Results
I have a table which is updated continuously and has over 2 million records in it, lets call it table A. The records have a "category" field and also a "date" field. Is there a way to get the most recent 10 records for each category in a single query? I know I can use "ORDER BY date DESC LIMIT 10" to get the 10 most recent records from the table, but I would like to extend this to get the most recent 10 for each category. I guess it would be the equivalent of (SELECT * FROM A WHERE category='cat1' ORDER BY date DESC LIMIT 10) UNION (SELECT * FROM A WHERE category='cat2' ORDER BY date DESC LIMIT 10), etc... It seems like there must be an easier way using a JOIN or something?
View Replies !
Extending Collation-Tables
I want to extend an existing Collation-Table. MySQL should also threat the character _ like ' - the same way it is threating Ă´ as o and so on. Is it possible to extend an existing Collation-Table - do I need a SUPER-Account to have enough rights?
View Replies !
Query A Specific Day, And GROUP BY
The query below, counts how many records each agent has entered in the previous 7 days and groups them by agent... Then it also counts a total for all the agents for the previous 7 days as well, not grouped. How can i query a specific day of the week, say 4 days back, only count that days records and group them by agent?
View Replies !
Question About A Specific Query
imagine I've got user that can have visual preference (enabled or disabled) saved on a table. Before a user make change of a preference, there is no row associate to that user and by default, the preference is enabled. Now, is it possible to do a query that will output user that have preference set to enabled (those who have update their preference) while also outputing those who have no row in the table preference? I know it may be not super clear, but I need to know before working on that script. So here a sample to make sure it is all clear: TABLE USER (id - name - email) 1 - NameA - a@a.com 2 - NameB - b@b.com 3 - NameC - c@c.com TABLE PREFERENCE (id - label) 1 - Show warning 2 - Show help TABLE USER_PREFERENCE (user_id - preference_id - status) 1 - 1 - Disable 2 - 1 - Enable So if I want to output user email that show warning, it should give me: b@b.com, c@c.com My problem so far is, how to retreive user that have a user_preference row enabled, and user that have no user_preference row in the same query!
View Replies !
Increment The Specific Field By 1 In A Query
I still do the process of incrementing a specific field in a stand alone apps by query 1: get its value query 2: update value +1 But, i realize that this is really a bad idea when this would be used in web applications... specially the database is in another webhost....(very slow response) i tried (using php function) Expand|Select|Wrap|Line Numbers
View Replies !
Query Result In A Specific Format
I need a query in which will give me a single column result ----------------------------------- | [any column Name] | ---------------------------------- 8:00 AM ---------------------------------- 9:00 AM ---------------------------------- 10:00 AM ----------------------------------- 11:00 AM ---------------------------------- I want the query to return a single column name with multiple rows in return ...
View Replies !
Sql Query To Exclude Specific Data From Resultset
I have got two tables,one customer with fields cust_id,email and another with field cust_id,domname,expirydate. i have this query to combine these two tables to give me email and the expirydate as $sql = "SELECT l.email,s.expirydate from customer l, domain s where l.cust_id = s.cust_id "; I need to exclude those expirydates whose domname contains .np in the text.
View Replies !
A Simple Query?
In table "vb_user" I want to pull the data in field "username" and copy that username in another table called "vb_userfield" in a field called "field5". Both tables share the field "userid" which is how the relation is made. I know this is probably very simple but man... I just can't seem to pull it off. I've got about 500 members and I really want to populate that field "field5" with thier username without having every one of them doing it manually.
View Replies !
Help With Simple Query
I know this is easy to accomplish, but my brain is fried and I can't figure it out. I have a few markets (areaID) that vendors belong to. Within these markets, vendors are assigned categories (catID). I am trying to do a SELECT on the vendors table to find out the total number of vendors to a particular category, within a particular market. So essentially, I want to know the number of vendors within each category within each market. Does that make sense?
View Replies !
Can Anyone Help Me With This Simple Query?
In my database. I want to pull out one record: Where the date I wish to publish a story where the published_web_date(date) is less or equal to now and The published_web_time(time) is less or equal to now However, using the below query data is nomatter what the published_web_date is if the published_web_time has not elapsed then nothing is shown. select * from cms_stories WHERE section = 'news' AND published_web_date <= NOW() AND published_web_time <= NOW() ORDER BY story_id DESC LIMIT 1";
View Replies !
Simple SQL Query
I'm making a football prediction website and I want to be able to display the weekly fixtures in a GridView so the admin can edit, insert, etc. This is a simple version of the two tables that I have: Fixtures and Clubs... CREATE TABLE Fixtures ( MatchID Int PRIMARY KEY, HomeClubID Int, AwayClubID Int ) CREATE TABLE Clubs ( ClubID Int PRIMARY KEY, Name Nvarchar(50) ) where HomeClubID and AwayClubID are actually foreign keys to ClubID. My Problem is that I'd like to create a query that will show each fixture with both the names of the home and away clubs on the same row, eg: | MatchID | Name | Name | |--------------------------------| | 1 | Arsenal | Man Utd | |--------------------------------| | 2 | Liverpool | Chelsea |
View Replies !
Trying To Get Total Count Plus Count From WHERE Clause In One Query
I am fairly familiar with mysql, have been using it with php for about a year now for my development work. One query I am stuck on is the following. I am doing reporting, and need to find the count from a WHERE clause compared to the total count for that group. For the sake of an example, let's say I have a prize table that contains a complete list of prizes. There is a column to list if the prize has been won or not (prizeUsed - Y or N) and another that has the prize name (prizeName - string). I want the final result to list something like: Total Prizes ____________________ 100 of 850 ......
View Replies !
MySQL Admin - Lost Thread On Specific Like Query?
mySQL win32 (4.0.22) I am using mySQL Control Admin and seeing a lockup on a thread specific to a like query. select * from table1 where field1 like 'http://h%' limit 100 This request will sit (thread) forever and never return any records. (status : sending...) However : select * from table1 where field1 like 'http://h%' limit 100 returns records immediately, no problems... I have already run : /mysql/bin/myisamchk --sort-recover dbtable1, no errors, no problems... How can I tell what this thread is really doing or if any additional problems exist with this table using myisamchk?
View Replies !
Simple SQL Query Syntax
I am looking for some help on a simple (yet beyond my reach) SQL query that I can run via phpMyAdmin whenever I need to. In a nutshell, I have a Database called A with table B, in which fields C and D sit. For each record, field C has an integer and field D has a human-readable date/time entry in the format: 2005-08-23 17:35:33. It is important to note that the above date part is YYYY-MM-DD as the convention might be different in your locality. Anyway, I am looking for an SQL query that will go through the table and whenever the date/time shown in field D is no more than 5 days ago, to add X (some whole number chosen and manually specified in the query each time it's run) to the value in field C
View Replies !
Mysql Simple Query
I am doing a project in mysql and php.. While inserting the data, i made a mistake of inserting date values into the month field and viceversa. can i swap it with a query. I cant take a backup of this table.. because its very big and the webspace for mysql database for me is running very low. ONE MORE THING :- there are many tables almost 565... can this all be swapped in a single query or procedure.
View Replies !
Simple Mysql Query
I know this will be simple but I just cant get it to work and I am being tested here by my line manager. Our database is named: lsql_Category and we have a table column named: Keywords We have thousands of rows The column Keywords has data in most of the rows and some without any data at all. Normally assorted and various text words. I want to clear all data from this coloumn but DO NOT want to delete the coloumn cos we want to input data at later time. The result should be that the column Keywords remains but will not have any data. Can someone help? I think it is a select command and a where command but that is all I know.
View Replies !
Simple Null Query
How do you say I want to select all data from this column but only show the data where the data is not equal to null. I have; SELECT prevleaving1 FROM `jobemployment` WHERE prevleaving1 IS NOT null
View Replies !
Simple Not Distinct Query?
I have a very large product table and believe that some of the fields contain duplicate values when they shouldn't. So I'm trying to create a simple query to identify those that do.. ie something like Select modelnumber from tbl_product where modelnumber is not distinct OR Select NOT Distinct modelnumber from tbl_product. These obviously don't work (Otherwise I wouldn't be asking for help!), and was wondering if there is a not distinct function that im missing or misusing?
View Replies !
Simple Query Trouble
I have written a simple select query...but it is not doing what I want it to: $query = "SELECT * FROM helpdeskticket WHERE (`customerid` = '$customerid') AND (`helpdeskticket`.`status` = 'Assigned') OR (`helpdeskticket`.`status` = 'Unassigned') OR (`helpdeskticket`.`status` = 'Can Be Closed') ORDER BY `helpdeskticket`.`ticketid` DESC Because its wrong i'll explain what I wish it to do. Select all fields from helpdesk ticket where customerid is equal to $customerid, and the status is either assigned, unassigned or can be closed. Then order by ticketid descending. At the moment it seems to be messing around with the AND and the OR, and is selecting everything from the database table, ignoring the customer id.
View Replies !
Simple Between Query Problem
I have multiple fields on my form where a user submits a minimum and maximum value. Such as minimum price and maximum price, so I use a Between in my queries like this. SELECT COUNT(*) AS 'numrec' FROM tblmls WHERE mlsstatus IN('Active', 'New', 'Back on Market', 'Price Change') AND mlsclass = 'Residential' AND mlslistprice BETWEEN ?' AND ?' My question is, is there a way to do wild cards using a between statement? Or is there a better way to do this? Currently if they don't put anything in I don't run that part of the querie, but if they put in a minimum but no maximum I just autosubmit a large number. If they put in a maximum but no minimum I just submit a 0.
View Replies !
Simple Query That I Can't Figure Out
First off, here's my table: CREATE TABLE order_info ( ID int(10) NOT NULL auto_increment, ordernum int(10) NOT NULL default Ɔ', item_code text NOT NULL, item_qty int(5) NOT NULL default Ɔ', item_price text NOT NULL, have int(5) default NULL, standby int(5) default NULL, backorder int(5) default NULL, refund int(5) default NULL, `status` int(2) NOT NULL default Ƈ', PRIMARY KEY (ID), KEY ordernum (ordernum), KEY `status` (`status`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 PACK_KEYS=0 AUTO_INCREMENT=477150 ; I would like to make a simple query that returns ordernum's that match this critera: All of the items have a 'status' of 3 Have at least 1 'refund' and I would like the ordernum's returned to be distinct. Here's the query I had before the requirement of the 'refund' column: SELECT DISTINCT order_info.ordernum FROM order_info WHERE order_info.ordernum NOT IN ( SELECT order_info.ordernum FROM order_info WHERE order_info.status != 3 ) AND order_info.status = 3 I tried adding: SELECT order_info.ordernum FROM order_info WHERE order_info.ordernum NOT IN ( SELECT order_info.ordernum FROM order_info WHERE order_info.status != 3 ) AND order_info.status = 3 AND SUM(order_info.refund) > 0 GROUP BY order_info.ordernum But it returns incorrect information (orders with 0 'refunds'). What is the correct way to make this query?
View Replies !
Bug In A Simple MySQL Query
Having a problem with a very simple query I am trying to execute. Hopefully someone can shed some light on the issue. SQL Query SELECT tblUserInfo.PersonalID, tblUserInfo.mainPicLink, tblUserInfo.Location FROM tblUserInfo, tblCoreUser WHERE tblUserInfo.Orientation = 'Straight' AND tblUserInfo.Location = 'Hampshire' AND tblUserInfo.PersonalID = tblCoreUser.PersonalID AND tblCoreUser.usrSex = 'M' LIMIT 10; Error: Unknown column 'tblCoreUser.PersonalID' in 'where clause'
View Replies !
A Simple Query Question ...
The following returns one row - PHP SELECT ab.id, ab.menu_name, ab.keywords, bb.content, NULL , NULL , NULL , NULL , NULL , NULL FROM menu_management AS ab JOIN dynamenu_content AS bb ON ab.id = bb.menu_id WHERE 1 =1 AND bb.publish =1 AND ab.version = 'fr' AND ab.menu_name LIKE '%Nanotechnologies%' OR ab.keywords LIKE '%Nanotechnologies%' OR bb.content LIKE '%Nanotechnologies%' LIMIT 0 , 30 But the following returns zero rows PHP SELECT ab.id, ab.menu_name, ab.keywords, bb.content, NULL , NULL , NULL , NULL , NULL , NULL FROM menu_management AS ab JOIN dynamenu_content AS bb ON ab.id = bb.menu_id WHERE 1 =1 AND bb.publish =1 AND ab.version = 'fr' AND ( ab.menu_name LIKE '%Nanotechnologies%' OR ab.keywords LIKE '%Nanotechnologies%' OR bb.content LIKE '%Nanotechnologies%' ) LIMIT 0 , 30 In fact I am trying to make a query (which will later be with other queries thro' UNION ALL) where upto ab.version = 'fr' will remain constant, but the following part will change. Means, among ab.menu_name, ab.keywords and bb.content, there may be all the three, or two or one condition only, depending on user's input. Also, the condition between them can be OR or AND, depending on whether user tends to search for all or any matches.
View Replies !
Simple Query Problem
I'm a newbie having trouble properly executing a simple query. I'm trying to make a pathway to prevent tennis players from signing up more than once on a specific date. Here is my PHP $play_date=($_POST['play_date']); $user_id=$_SESSION['user_id']; // Make the query to check if the player already signed up on that date. $query = "SELECT user_id, play_date FROM table WHERE user_id=$user_id AND play_date=$play_date "; $result = mysql_query ($query); if (mysql_num_rows($result)>0) { echo '<p><font color="red" size="+2">Sorry, you already signed up to play on this date!</font></p>' } else { Although the above code runs, it doesn't prevent one from signing up twice on the same date.
View Replies !
Specific Table
Hello MySQL gurus, This is the command : mysqldump -u$user -p$pass $database >$backup Can you exclude a specific table, say table UnNecessaryAndHugeTable, from the dumping process? that is mysqldump all tables except table UnNecessaryAndHugeTable
View Replies !
1 Line Query To Delete Specific Records From Multiple Tables
On clients machine, currently to delete on trainee record it runs 10 queries to delete records from 10 tables. At the time of running all queries, server shows (104) Connection reset by peer. An error condition occurred while reading data from the network. I think it because of running 10 queries at a same time. Is there any possibility that through one line of query we can delete record from 10 tables. I've tried following query DELETE FROM table1, table2, table3, table4, table5, table6, table7, table8, table9, table10 WHERE empID = 11; But it gives ' error in query.
View Replies !
Simple JOIN Query Issues
What I want to do is have multiple tables interact with each other. payments TABLE id | paidfrom | paidto | paidmethod 1 | 2 | 1 | 2 2 | 3 | 1 | 3 users TABLE userid | username 1 | Jake 2 | Todd 3 | Spencer methods TABLE methodid | methodstring 1 | Cash 2 | Check 3 | Credit Card
View Replies !
Using A Simple 'function' In SELECT Query
Is there any way of creating a SELECT query which has as it's result a column which is comprised of a function whos inputs are the values in other results columns. For a simple example - a four column output, x, y, x+y, x/y SELECT A.x, A.y, A.x+A.y, A.x/A.y FROM A This is works fine, as is, and produces the correct results. However, as the function gets more and more complicated and the A.x and A.y turn into sub-queires (and generally the expressions all become larger and then more unwieldy) not only does the function get very painful to write and debug but the query times rise rapidly also. Can alisas' be used here (or any other methods people know of) to refer to a columns' results to ease writing the functions and keep the load on the DB to a minimum?
View Replies !
Simple Query Question: LIMIT
I'm looking to list all the rows in a database, but skipping the first 5. I know that I can use "LIMIT 0,5" to get the first 5 results, but what query do I use to list everything from 6th result onwards?
View Replies !
Simple Select Query Take 5 Secons
This select i've got take 5 seconds and i really don't know why!!!! The number of rows in any table is NOT bigger than 50 records! SELECT DISTINCT(j.id) as idunic,j.* FROM jobs j INNER JOIN jobs_categs jc ON j.id = jc.id_job INNER JOIN jobs_type jt ON j.id = jt.id_job INNER JOIN jobs_cities jci ON jci.id_job = j.id WHERE j.status = 'OK' AND j.deleted = 'NO' AND j.suspend = 'NOK' AND j.date_end > NOW() AND jci.id_city = ƌ' ORDER BY j.date_begin DESC LIMIT 0, 25
View Replies !
Simple Query? Why Is My Hair Falling Out?
Im trying to pull all records from table a and table b for 14 hrs now. It seems so simple? both tables are indexed with the fields 'classid' and 'states' actual results should be 36 records as you can see that is not the case. Here is what ive tried so far: SELECT * FROM classifieds_g WHERE states='AZ' records 4 SELECT * FROM classifieds WHERE states='AZ' records 32 Samples and results of what I've tried (should return 36 records) SELECT * FROM classifieds_g, classifieds WHERE classifieds.states='AZ' and classifieds.states = classifieds_g.states records 0 SELECT classifieds.states, classifieds_g.states FROM classifieds_g, classifieds WHERE classifieds.states='AZ' and classifieds.states = classifieds_g.states records 0 SELECT * FROM classifieds_g, classifieds WHERE classifieds.states='AZ' GROUP BY classifieds.states records 1 SELECT * FROM classifieds_g, classifieds WHERE classifieds.states='AZ' records 4000 SELECT DISTINCT * FROM classifieds_g, classifieds WHERE classifieds.states='AZ' and classifieds_g.states='AZ' records 128 SELECT * FROM classifieds_g, classifieds WHERE classifieds_g.states='AZ' and classifieds.states = classifieds_g.states records 0 SELECT * FROM classifieds_g LEFT JOIN classifieds ON classifieds.states = classifieds_g.states WHERE classifieds_g.states='AZ' records 128 SELECT * FROM classifieds_g INNER JOIN classifieds ON classifieds.states = classifieds_g.states WHERE classifieds_g.states='AZ' records 128 SELECT * FROM classifieds_g LEFT JOIN classifieds ON classifieds.states = classifieds_g.states WHERE classifieds_g.states='AZ' GROUP BY classifieds.states records 1 SELECT * FROM classifieds_g LEFT JOIN classifieds ON classifieds.states = classifieds_g.states WHERE classifieds_g.states='AZ' GROUP BY classifieds.classid records 32 (all the same) SELECT DISTINCT * FROM classifieds_g WHERE states IN(SELECT states FROM classifieds WHERE states='AZ' )GROUP BY states records 1 SELECT DISTINCT * FROM classifieds_g WHERE states IN(SELECT states FROM classifieds WHERE states='AZ' ) records 4
View Replies !
Simple Mysql Query Not Working
I have a simple table set up listing stockists and there details, included in this table I have a name column and a website column. I wish to select the name and the website of the stockists only if a website has been entered into the website column. Here is the query I have tried to use Quote: SELECT name, website FROM stockists WHERE website IS NOT NULL ORDER BY name ASC This query is returning stockists with no website as well as the ones with websites and I don’t know why!
View Replies !
Having Problems Woth This Simple Sql Query!
hi, i have two tables essentially they are the same but one gets all normal file details placed in to it and the other has all the files that have been attached to comments put in to it...it just made programming far easier this way.... Now this seems like such a simple query but i just cant get it to work... I want to select file_name,file_type, and upload_id from both tables so i can create an archive of all files posted to the site...how do i do this.... its something like SELECT file_name,file_type,upload_id from uploads, comment uploads ORDER BY date_submitted DESC LIMIT 30 but thats obviously not it....i also tried a join query: SELECT uploads.*, upload_comments.* from uploads INNER JOIN comment uploads ORDER BY date_submitted DESC LIMIT 30
View Replies !
|
TRACKER
