Select Statement, Grouping By Totals
I have an enormous database and I'd like to count how many times a unique record appears, then order the results based on that. For example:
select a, b, count(a) AS TOTAL from table GROUP BY a ORDER BY TOTAL DESC;
+-----------+------------+----------+
| a | b | Total |
+-----------+------------+----------+
| z | 2004-01-14 | 24 |
| x | 2004-01-05 | 22 |
| b | 2004-02-11 | 20 |
-------------------------------------
Meaning z appeard 24 times, x 22, and so on. This only returns the totals, not each row in itself. I need to have each row returned based on the amount of times it appeared. I obviously have to keep GROUP BY in there so I'm unable to ORDER BY TOTAL returned.
View Complete Forum Thread with Replies
Sponsored Links:
Related Messages:
Select With Dummy Rows When Grouping?
If I select from a table and, for example, group by the month or day to determine how much activity there is day to day or month to month, it will only return months and days with a record and leave out any month or day without any records making it apear at a glance that every day or month has activity which forces me or whoever to have to look at the date column and actively look for spots where a day or more elapses without a single row in order to determine when nothing has happened. Is there any select method for grouping on something like a month and to have it output a dummy row for missing days or months without having to write a program that makes an individual select for each day?
View Replies !
View Related
Select With Dummy Rows When Grouping
If I select from a table and, for example, group by the month or day to determine how much activity there is day to day or month to month, it will only return months and days with a record and leave out any month or day without any records making it apear at a glance that every day or month has activity which forces me or whoever to have to look at the date column and actively look for spots where a day or more elapses without a single row in order to determine when nothing has happened. Is there any select method for grouping on something like a month and to have it output a dummy row for missing days or months without having to write a program that makes an individual select for each day.
View Replies !
View Related
Combining SELECT Statements Into One SELECT Statement.
I want to take the results from: SELECT name.empnumber, name.firstname, name.lastname FROM name INNER JOIN authuser ON name.empnumber = authuser.uname AND authuser.team = 'PHQ' ORDER BY name.lastname, name.firstname; and the results from: SELECT name.empnumber, name.firstname, name.lastname FROM name INNER JOIN crew_attendance_6QJ ON name.empnumber = crew_attendance_6QJ.empno ORDER BY name.lastname, name.firstname; And combine them into one query that outputs all of the results both queries would output. Then order those results. So far I have come up with:
View Replies !
View Related
Counting Totals
I have a table of messages. Each message has a me_date datetime. I want to get a count of the number of messages every day in the last 30 days - even the days when there were none. How can I do this in a single statement? so far I have: select count(me_id) as a, to_days(me_date) from messages where to_days(me_date)>to_days(now())-90 group by to_days(me_date) but this doesn't include the 'zero' days (days when there were zero messages).
View Replies !
View Related
Totals/Mean Values
I assume this is a very simple question, I just don't know the answer! I am planning on setting a MySQL database for some real estate property that has already been sold. The user will enter the information for each parcel sold for homes, land, etc. What I want to do is: a) Total the price columns for each and take the average price - have all of this calculate automatically b) Also call on these total values from an intro page displaying the totals for each year First, is this possible? Second, how would I go about doing this? Do I need to add extra fields to the table for these totals, or are there MySQL commands to do the arithmetic
View Replies !
View Related
Indexed Totals
don't know if that subject is correctly put, but here's what i'm trying to accomplish: I want to be able to tell how many rows in a given table, and for a given INDEXED column, carry any given ID. Example: suppose I have a field named 'IDNumeric' defined as decimal(5,0). Now suppose one ID is: '56007'. I want to be able to tell how many rows in the entire table have that ID. I know that I can use the select keyword, but i'm wondering if there's another way, because the table i'll be doing this for can be up to 350 Million rows, and the vast majority (probably close to 99.9%) of the rows will have mutually exclusive IDs. I am only concerned with that small percentage of rows that have duplicate IDs
View Replies !
View Related
Select Statement!
I have a simple query that is similar to this one: SELECT distinct b.username from permissions a, users b, links c where a.user_id = b.id and c.information = 14; In this case, it selects the all users details from table 'user' that are permissioned to access a certain information with id = 14. The problem is that I need to select the users that are not permissioned but I have sql v3.23 and I canot use union. In other words, I need to do a select b.username from users b where b.username not in "the result" of the last select.
View Replies !
View Related
One Select Statement
I have a table with the fields id | titleid | catid Each title can have more than 1 catid so it's possible for the fields to look like this: id | titleid | catid 1 1 1 2 1 2 3 2 1 Now I want to select any rows where titleid matched more than one catid. In this example I would like to select titleid 1 because it is in catid 1 and catid 2 but I can't seem to figure out a statement that properly says "give me any titleid that has a catid of 1 AND a catid of 2"
View Replies !
View Related
SELECT Statement With OR
I need some help with this select statement: SELECT * FROM gallery WHERE type = 'skate' AND park = 'Both' OR park = 'Park 1' I just need it to look at one table and grab all the rows that have a type of 'skate' and have a park value of either 'both' or 'milwaukee'. I think the OR is throwing it off.
View Replies !
View Related
Regarding Select Statement
I am retrieving data from two tables using the UNION statement and i am thinking of having a field in my resultset(this field is not found in the database) to distinguish the data from two tables.Is it possible to set a fixed value for a particular field using the select statement?For example, a field value of 'buy' and 'sell' values are used to distinguish between the two tables.
View Replies !
View Related
Select And A Max In The One Statement
Can you combine a select and a max in the one select statement? Eg,. I want to select like select ids from table_name where ids > $idsvariable... order by ids.... but I also want to grab the highest ids number so I can put $idsvariable (max) into a table so next time it selects only the ids higher than the last time the select was run! So if ids were: 2 5 6 9 12 14 15 then I'd like to get 15 as the max as well... any examples.
View Replies !
View Related
Select Nav Statement
I have a nav table in my database that grabs information to put into the navi menu, this is dependant on user access etc... i have an item in my table that is in zone "members" and it is marked for permission Z. Although when i run the query it dont pick it up.... these are the queries that ive tried... SELECT * FROM acwe_nav WHERE privs LIKE '%A%B%C%D%E%F%X%Y%Z%' AND zone='members' ORDER BY weight; SELECT * FROM acwe_nav WHERE privs LIKE '%ABCDEFXYZ%' AND zone='members' ORDER BY weight;
View Replies !
View Related
Else If In Select Statement
i have a bunch of records that i want to pull on the basis of eg SELECT lat, lng, html, label, icontype FROM ade WHERE icontype='" . $_POST['name'] . "'"; but i'd like to make the first query and also another on an "if else" basis to cover various bases if i post a query where the 'name' isnt an icontype, i want it to look in the "section" field and pull those records matching a certain value ie all records that have a value of "5" is it possible to do something like this SELECT lat, lng, html, label, icontype FROM ade where icontype='" . $_POST['name'] . "' ELSE IF section='" . $_POST['name'] . "' ELSE IF catid='" . $_POST['name'] . "'";
View Replies !
View Related
Is There Something Like A SELECT ALL BUT Statement?
Usually the tables I have to handel have a high number of columns. Additionally, columns are deleted and added dynamically. Most calls made on these tables are select statements: Consider two tables t1 and t2. A natural join results in the columns a,b,c,d,e,f,g,h,z. A usual select statement is the following: SELECT a,b,c,d,e,f,g,SUM(z) FROM t1 NATURAL JOIN t2 GROUP BY a,b,c,d,e,f,g This way the column h could be "eliminated". As you can see the select and the group by clause can become fairly long. What I am looking for is a clever way to say "Group by everything but h and z". Is that possible?
View Replies !
View Related
Select Statement MIN()
SELECT * FROM Distance, LorryDetails WHERE cavan = (Select min (cavan) From Distance where cavan>0); here is the results i am getting source ,Cavan, Athlone, Cork, Galway ,LorryID, LorryReg, Position Athlone 90 0 180 80 3 02-CN-8001 Cork Athlone 90 0 180 80 7 00-CN-1000 Cavan Athlone 90 0 180 80 10 02-G-8001 Galway What i want to try and do is compare the Values with the Column names and get the shortest path. for example when you compare the position cort with the column cork it is 180, cavan is 90, and galway is 80. Galway is the nearest to athlone so i want to narrow down the results to just give me back the row where the position is equal to Galway. Can anyone help me with this.
View Replies !
View Related
Select Statement Within Another?
Tag_id::Img_id::Tag::counter 001::1::Tree::70 002::2::Tree::50 003::3::Pond::30 004::4::Flower::20 005::5::pink::60 lets say the above is my table. this is my current query: SELECT tag, max(counter) FROM tags GROUP BY tag DESC limit 3 this is the result I get: tree 70 pond 30 pink 60 here is can someone pls tell me a way to get the total of the counter of all the occurrences of a particular tag(than the max(counter) ). so that it'l be tree 120 pond 30 pink 60
View Replies !
View Related
SELECT With An AS Statement
I'm using a LEFT JOIN to mash two tables together. It works fine. I'm feeding this result into PHP's mysql_fetch_assoc. mysql_fetch_assoc doesn't handle columns with duplicate names very well, and these two tables do have duplicate names. So, I am using AS to assign an alias to these duplicate column names. But, these tables have about 20 fields each, and I can't figure out how to use my SELECT statement so that it selects all of the fields from tables (SELECT * FROM . . . ) but also assigns an alias to a specific field.
View Replies !
View Related
SELECT MIN, MAX And IF Statement
I have a few entries with a start timestamp, and an end timestamp. What I'd like to know is the earliest start date, and the latest end date, to define the complete time span of the selected entries. Example: id, date_start, date_end: 1, 0000-00-00 00:00:00, 2009-02-07 12:00:00 2, 2009-01-01 12:00:00, 2009-01-05 12:00:00 3, 0000-00-00 00:00:00, 0000-00-00 00:00:00 As you can see, it's possible that a start_date and/or end_date aren't inserted. If I now select the MIN(start_date) and the MAX(end_date) from the above entries: SELECT MIN(start_date) AS start, MAX(end_date) AS end FROM entries WHERE ... The result is: start = 0000-00-00 00:00:00 end = 2009-02-07 12:00:00 What I'd like to return, is the lowest possible date_start, in case a date_start has been entered. In the example above, this would be 2009-01-01 12:00:00, because that's the only date_start that isn't NULL. I tried this: SELECT IF(start_date>0, MIN(start_date), NULL), etc...
View Replies !
View Related
SELECT With IF Statement(?)
# Table structure for table `db_emails` # CREATE TABLE `db_emails` ( `id` int(10) NOT NULL auto_increment, `user_id` int(10) NOT NULL default '0', `email` varchar(255) NOT NULL default '', `main` int(1) NOT NULL default '0', `list` int(2) NOT NULL default '0', PRIMARY KEY (`id`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=5 ; # # Dumping data for table `db_emails` # INSERT INTO `db_emails` VALUES (1, 1, 'a.a@a.se', 1, 1); INSERT INTO `db_emails` VALUES (2, 1, 'aa.aa@a.se', 0, 2); INSERT INTO `db_emails` VALUES (3, 2, 'b.b@b.se', 0, 1); INSERT INTO `db_emails` VALUES (4, 2, 'bb.bb@b.se', 0, 2) So, now to my question. How can i do a SELECT where i want all email adresses BUT only the main if main=1 and all if main=0. The result for this dataset should be: a.a@a.se b.b@b.se bb.bb@b.se
View Replies !
View Related
Select Statement
How can I do a select statement for this problem: If a in ('1', '6', '11', '16', '21', '26', '31', '36', '41', '46', '51', '56') then '1' as ‘stemp’ If a in ('2', '7', '12', '17', '22', '27', '32', '37', '42', '47', '52', '57') then '3' as ‘stemp’ If a in ('3', '8', '13', '18', '23', '28', '33', '38', '43', '48', '53', '58') then '5' as ‘stemp’ If a in ('4', '9', '14', '19', '14', '29', '34', '39', '44', '49', '54', '59') then '7' as ‘stemp’ If a in ('5', '10', '15', '20', '25', '30', '35', '40', '45', '50', '55', '60') then '9' as ‘stemp’ I have this possibilities as a result.
View Replies !
View Related
Select Statement
I have a table containing names of people in the neighborhood. The table only contains first name and family names. How to get lists of first names grouped by the same family name ? This is similar to getting "sum" of salary grouped by a certain condition. Unfortunately, "sum" does not work for strings.
View Replies !
View Related
Need Help With SELECT Statement
I have 2 simple tables: emps and rprts. Table emps has: "ID", "name" (simple string), and some other irrelevant fields. Table rprts has: "ID", "emp_id" (indication of "ID" in emps table), "rprt" (10-digits time), and "type" (boolean). I want to select the fields "ID" and "name" from emps, and in addition - for every selected row I want to select rprts's "type", where "rprt" (or "id", doesn't matter) value in the highest. For example: emps: id name ------------------ 1 jeff 2 wanda 3 brad rprts: id emp_id rprt type ------------------------------------------- 1 3 df 1 2 1 ie 1 3 2 ei 1 4 3 fd 0 5 2 tg 0 6 3 gf 1 For this data, the following should be selected: id name type ---------------------------- 1 jeff 1 2 wanda 0 3 brad 1 I tried several things, but I want to keep it simple and readable, so I'll understand if I'll read it sometime in the future.
View Replies !
View Related
Help Me With This Select Statement :)
I have two tables 1- articles table which contains the articleid and article_description 2- articles_translation table which contains translation_id, article_id(foreign key from the articles table) and translation_description I would like to select all the articles which aren't translated yet... How can i do this in MySQL?
View Replies !
View Related
Calculating Totals From Two Tables
I have three tables: invoices, invoicedetails, invoicepayments The fields are: invoices -------- InvoiceNo InvoiceDate CompanyNo invoicedetails -------------- InvoiceNo ProductNo Quantity UnitPrice invoicepayments --------------- InvoiceNo PaymentDate PaymentAmount For each row in invoices there will be 0 or more rows in invoicedetails and invoicepayments, with the InvoiceNo field linking everything together (i.e. one to many relationship between invoices and invoicedetails and invoicepayments). I need a query that will give me a list of invoices that still have money outstanding on them. To manually do this I would loop through the invoices table and for each InvoiceNo I would gather all the matching rows in invoicedetails and invoicepayments. To get the invoice total I would multiply Quantity by UnitPrice for each invoicedetails row. To get the total paid I would add up the PaymentAmount. Then I'd compare the invoice total and the payment total and if the payment total was less than the invoice total I'd know that there was still some money outstanding on that invoice. Now, how do I write a single query to do this? I using MySQL 4.0.20 (unfortunately because I don't control the server I can't upgrade to a version of MySQL that support subqueries). I'm guessing I'll need to use the SUM() function to add things up, and GROUP BY to group the invoicedetails and invoicepayments so I only get one row per invoice. I can get a total for each invoice by using the following query: SELECT invoices.InvoiceNo, SUM(Quantity * UnitPrice) AS InvTotal FROM invoices, invoicedetails WHERE invoices.InvoiceNo = invoicedetails.InvoiceNo GROUP BY invoices.InvoiceNo However I'm at a loss as to how I would modify this query to also total up the invoicepayments to give me a PaymentsTotal and then calculate the difference between the InvTotal and the PaymentsTotal to figure out if there is still money outstanding.
View Replies !
View Related
Getting Totals(or Percentage) Of Each Field
Say i have a select statement which selects 5 fields and displays the results as follows: field1 | field2 | field3 | field4 | field5 10 | 20 | 60 | 80 | 40 10 | 20 | 60 | 80 | 40 10 | 20 | 60 | 80 | 40 10 | 20 | 60 | 80 | 40 10 | 20 | 60 | 80 | 40 how can i add one more row to the output, which would calculate the total of each field and display at the bottom of the table?? for above example, the output should look like: field1 | field2 | field3 | field4 | field5 10 | 20 | 60 | 80 | 40 10 | 20 | 60 | 80 | 40 10 | 20 | 60 | 80 | 40 10 | 20 | 60 | 80 | 40 10 | 20 | 60 | 80 | 40 Total 50 | 100 | 300 | 400 | 200
View Replies !
View Related
Computing A Difference In Totals.
I am using php/MySQL 4.0 .. If I had a table consisting of: team l points ------------------------------------- teamA l 15 teamA l 10 teamA l 5 teamB l 5 teamB l 10 teamC l 5 if (mysql_query("SET @rank = 0;", $conn)) { if ($result = mysql_query("SELECT @rank := @rank + 1 AS Rank, team, SUM(points) as ttl FROM table GROUP BY team ORDER BY ttl DESC, TM ASC;", $conn)) How would I get the result below? rank l team l points l behind -------------------------- 1. teamA 30 0 2. teamB 15 15 3. teamC 5 25 I know how to implement the ranking of the teams .. I do not know how to get the point difference for the behind column. Is there a way to set the ranking to handle ties? (Example: 1,2,2,4,5 etc.)?
View Replies !
View Related
Showing Totals And Subtotals In One Row
I have a table with the following fields: ContractID | CustomerID | ProductID | Quantity For each Contract there is one record: Who has ordered which product in what quantity. Now I'd like to generate a report that shows: - which products were ordered (SELECT ProductID ... GROUP BY ProductID) - at most (SELECT ... SUM(Quantity) AS Quantity ... ORDER BY Quantity DESC) - and from which customers. (SELECT CustomerID, Quantity ...) GROUP_CONCAT(...)? Subquery? Sample-Output: P_ID - Quantity - Customer's quantities ---------------------------------------- 1230 - 10'000 - A: 2'000, B: 8'000 1240 - 8'000 - A: 7'000, C: 500, D: 500 1120 - 6'000 - C: 6'000 ... How shall I build the SQL statement?
View Replies !
View Related
Getting Totals From 5 Tables In One Query...
This is something that has been puzzling me for a few weeks. I have 5 tables in the database that I want the total rows count from. Now I know I could do 5 queries and use 5 mysql_num_rows to return the result but I feel sure that there is a better/easier/more efficient way of doing it. i am guessing that it has something to do with joins but mysql really isn't my thang! I Have tried something like: select count(hotel.id) as atb, count(trainer.rec_id) as det, count(club.id) as ml, count(activity.id) as sl from hotel, trainer, club, activity but that returns the totalled amounts added together which is why I figure a join is needed. My aim is a online stats panel something like: Leisure Clubs Onsite: 2442 Trainers Onsite: 232 Hotels Onsite: 1978
View Replies !
View Related
Select Statement In Normalized Db
can anyone share the best way to retrieve from a normalized db? My goal is to get the engines.eng_definition. Here's what I have: thehub: 1N --------- 1)item_no carinfo: 2n ---------- 1)item_no 2)eng_ID engines: 3n ---------- 1)eng_ID 2)eng_definition my query has to get the item number from theHub. Is there a way for me to JOIN these together? I understand how to do a JOIN on theHub and carinfo but can I join engines in the same select statement? right now this is what I have. Keep in mind there are other items being retrieved. Select x, y, z FROM thehub INNER JOIN carinfo ON (thehub.item_no = carinfo.item_no)
View Replies !
View Related
Logs Via Select Statement
I know then when I am using MySQL via the command interface, I can press the up arrow to scroll through previously typed statements. Is this information stored somewhere that can be accessed via log file or via a select statement? Is this available for all accounts?
View Replies !
View Related
Select Statement Needed
The sending page allows the user to choose which search criteria he wants, so this (and many other) pieces of the select statement may or may not be present. The statement always starts with SELECT * from logdata where driver = '$_COOKIE[username]' If the user has selected them and supplied the proper data for a search, other elements will be added to the query string. For example, it may look like SELECT * from logdata where driver = '$_COOKIE[username]' AND amount >= $_POST[txtLowAmount] AND amount <= $_POST[txtHighAmount] AND restaurant regexp??? I'm not even sure if that is where a regexp belongs or if that is how it should be used. Ultimately, I would prefer it be non case sensitive. Here are some examples of what I want: If the user string is "CHIL" Chili'strue Charlie'sfalse CHILtrue chfalse
View Replies !
View Related
Concat In Select Statement
Is it possible to conduct a test in the select statement to see if a value is not found in one table (t2) then get the value from another table (t3). In this case you would have 3 tables. if there is no related record in table2 t2, then get the value from table3 t3 If so what would the code look like? .....
View Replies !
View Related
SQL Statement To Select Only One Name From A Lot Of Same Names
I have SQL statement like "Select Fullnmae from abssubtest" and if I do so the output is the list of all data containing in the Fullname...including the data with same names is repeating...I dont want the repeated data...i.e. if the Fullname data has 1. Ulm 2. Prague 3. Frankfurt 4.Prague 5. Frankfurt I want the output as only 1.Ulm 2.Prague 3.Frankfurt but not the repeated city names...
View Replies !
View Related
IF Statement Within A Select Query?
If there a way to do an IF statement within a MySQL select query? I have this query that gets a vendor's code and name from the database and CONCAT it to put the code into "()" and add the vendor name to the end. Is there a way to write it so the "()" will not be included if the vendor does not have a code set.
View Replies !
View Related
Nested Select Statement
I am working on a feature for my website (MyRoladex.com) where i have a script run through the database once per hour, look to see who has events scheduled that are within the next hour, then look to see what their email/sms/text message preferences are, then email/text them a notification that it is close to time for their scheduled event. Under the Accounts table, i have 5 notable fields: uid, email, mobile, timezone, messagetype Under the DatebookEvents table, the important field is, eventdate (besides 'uid', obviously), which is is YYYY-MM-DD HH-mm-SS format. I did not want to calculate the time zone difference for each user, and then compare it to each of the event dates, and then to the system time, just to see if a notification should be sent, as this would be an immense waste of cpu and bandwidth. The next best thing that i though of was to STORE the event date PLUS or MINUS the time zone difference, whenever the user creates a new event, and then, whenever the user displays their events, it would ADD or SUBTRACT the time zone to the event date, so that it will display correctly for them, AND when the cron job runs once every hour, it will ONLY have to compare the eventdate field with the system time. This is better because the cron job will run once per hour, and have to work with, perhaps, thousands of records, whereas a user may only display his records once or twice per day, thus saving cpu and bandwidth overhead When i was writing the query for the displaying of event details, where i take the event date for the entry they are viewing and ADD or SUBTRACT their time zone to it so that it displays properly according to their time, i came across this problem: Select eid,(eventdate + hour(select Accounts.timezone from Accounts where Accounts.uid='666')),left(note,40) from DatebookScheduler where uid='666' Returns: #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 Accounts.timezone from Accounts where Accounts.uid='666')),left(note,40) ' at line 1
View Replies !
View Related
SELECT Statement For Area
I am trying to search a table with a statement like the following: "SELECT name FROM table WHERE state='$state' AND trigger=1 AND areaA='$area' OR areaB = '$area'"; The problem is I need it to find the 'area' value in one of two different columns while matching the 'state' and 'trigger'. My syntax seems to be incorrect and I dont know how to find matching for 2 values while the other 2 columns can be optional, but one must be found.
View Replies !
View Related
Optimize Select Statement
I have a big simple table with 3 fields and 700,000 records. Table: mytable Field1 Field2 Field3 name1 initial1 1 name2 initial2 0 name3 initial3 0 name4 initial4 0 ... ... ... i like to be able to do a fast search where it can return the first record that has 0 in field3. If i go through the whole table, it takes too long. Select * from mytable where field3 = 0; <---- it takes too long to do this i would like to get the record # 2 because this is the first record that field3=0
View Replies !
View Related
Using A Select Statement As A Wildcard
Is there a way to use a sub-query in a wildcard context? I need to reference a field in one table as a search query in another but where the content of the second could exists anywhere in the first. where t1.f1 like '%t2.f2%' I fully appreciate that the above does not work but is there a way that the reference can be enclosed in a wildcard type statement? An example of content: t1.f1 = '123456 ABC1234/09' and t2.f2 = 'ABC1234' If doing a sub query or join how can one acheive a match on the above?
View Replies !
View Related
Result Of SELECT Statement For IF/THEN?
What I am trying to find out if - if I do a SELECT FROM and there are NO results, is there a return code or variable to look at / compare in order to print out a message that the user does not exist? SQL Statement is: SELECT FROM subscribers WHERE (username='".$user1."' AND userpass='".$pass1."') Then I would like to have an IF statement that interrogates something that would be 0 or null if there were no records found on the select.
View Replies !
View Related
Select Statement With Date
Basically My events lister page sends a query to my database and so far i have it returning my event(s) in order of startDay and displaying events in the future. $query='SELECT * FROM events WHERE startDay >= CURRENT_DATE ORDER BY events . startDay ASC LIMIT 0, 30 ' ; I now want to display the current events for the current month. I guess I would have to use AND but am unsure of how to to actually filter out the current month. Would this be better done in php? I had a similar problem with the DATE_FORMAT function as i was trying to convert the dates to a European format. I eventually used php to convert the dates instead.
View Replies !
View Related
Trouble With SELECT Statement
I start to work with MySQL to store infos of my private group of dancers. There I have different tables (personen, addresses, emails, phones, urls) I like to display the infos for all members of this group on a website (php & mysql), but I have trouble with my MySQL statement : SELECT DISTINCT concat(firstname," ",name) AS Mitgliedsname, concat(area,"-",number) AS Telefonnummer, mail_address AS 'Email-Adresse', concat(street," ",houseno,", ",zipcode," ",city) AS Anschrift FROM personen LEFT JOIN phones ON phones.owner = personen.id LEFT JOIN emails ON emails.owner = phones.owner LEFT JOIN addresses ON addresses.owner = emails.owner WHERE personen.status="Active" ORDER BY Mitgliedsname; Some of the members have more than one emailaddress and more than one phonenumber. So when I use the sql statememt above I got multiple combination of some members. here an example of the sql result: Hans 8386884 hans@web.de Street 18, D-11111 Munich Hans 7927678 hans@web.com Street 18, D-11111 Munich Hans 751036 hans@web.net Street 18, D-11111 Munich Hans 7927678 hans@web.de Street 18, D-11111 Munich Hans 751036 hans@web.com Street 18, D-11111 Munich Hans 8386884 hans@web.net Street 18, D-11111 Munich Hans 751036 hans@web.de Street 18, D-11111 Munich Hans 8386884 hans@web.com Street 18, D-11111 Munich Hans 7927678 hans@web.net Street 18, D-11111 Munich Does anyone of you have an idea how I can solve this?
View Replies !
View Related
Select Statement Where Clause
Does MySQL support "==" in the where clause, or is an exact match the default behavior? I noticed the following select statement in existing code: *********************************** $q = "SELECT account_id, account_username, account_email, account_password, account_status FROM account i WHERE (upper(account_username) = $tcName)"; *********************************** I would have expected two records matching for the following situation: $tcName = "BECK", with a record having account_username = "BECK" and a record having account_username = "BECKER" It only returned a single record ("BECK"). The account_username is a varchar - does that make a difference? Are there any MySQL settings which control "exact on"?
View Replies !
View Related
SELECT Statement With MAX() Function
I am trying to select some ids from my database based on an effectiveDate. Here is a query similar to the one I'm using: SELECT id, MAX(effectiveDate) FROM mytable WHERE effectiveDate<='today' GROUP BY company, state; This returns the correct effectiveDate, yet it does not return the id cooresponding to that date. Is there any way to make that happen in a single query?
View Replies !
View Related
Cannot Get Select Statement To Work
I can't for the life of me figure out why my select statement will not retrieve the data from MySql. it is a very simple statment; mysql_query ("SELECT pub_type, call_number, pub_author, pub_title, subtitle, edition, publisher, publication_year, physical_description, series, isbn, holdings, subjects FROM library WHERE id=" .$pub_id); but when I try to echo any of these variables I get nothing showing for results. the $pub_id displays because it is set by a GET function. echo "<b class='foo'>$pub_id $pub_author $series </b> "; I use a select statement on the page that calls for this page and it retrieves info from the database not problem... my includes for database connection, etc are all in tact.
View Replies !
View Related
Double WHERE In A Select Statement
hey having a bit of trouble wit my SELECT statement because i can't two WHERE statements to works Can anyone help int uu = 3; String code = "SELECT * FROM destinations INNER JOIN holidayType ON holidayType.holidayID = destinations.holidayID WHERE holidayType.star = uu && holidayType.season = '" + line + "'";
View Replies !
View Related
|
TRACKER
