Sql Help


Hi Everyone,

I have a simple question regarding outer join.

Please see the attached word file. It has screen shots of the query I am running. My first query shows the result where i have M.ReservationID = MA.MeetingID and it counts NoofRSVP (# of times the query runs). I have to modify first query in such a way that it returns records from eCDReservations table even if there is no matching MeetingID in MeetingAttendees table (means Null, see the result of 2nd query in attached file). So in my result for that case NoofRSVP column should show either Null or 0.
Posted On: Wednesday 7th of November 2012 02:10:47 PM Total Views:  493
View Complete with Replies

RELATED TOPICS OF MYSQL PROGRAMMING LANGUAGE




Need help with table types and collation

I'm using software that utilizes a MySQL database. Long ago I set up MySQL 4.0, the software built the tables and everything ran fine. This week I copied the database to another instance on a different computer, removed MySQL on the first machine and reinstalled 5.0. Everything went great, with the exception that I apparently don't have it set up for european characters. Some of my filenames stored in the database have european characters such as or . These show up in the database as question marks or strange combinations of HTML encoding. I need these to be correct so the software can find the correct files. These are MyISAM tables with utf8_general_ci collation. I've tried a couple of different things, but can't get it back to where it used to be before the upgrade. Any suggestions
VIEWS ON THIS POST

138

Posted on:

Monday 29th October 2012
View Replies!

Intersect - SQL query help

I'm stuck on how to write this query. I'm a noob so go easy on me: SELECT `pollselection`, count( * ) votes FROM info GROUP BY `pollselection` ORDER BY number DESC Provides a count for how many times a poll selection was voted for. But I want to count only DISTINCT / unique ip addresses, not someone who voted twice. So I also have: SELECT DISTINCT `ip` FROM info How do I combine the latter with the former
VIEWS ON THIS POST

149

Posted on:

Monday 29th October 2012
View Replies!

Page 3 - SQL loop query help!

Quote: Originally Posted by pabloj As per the similar thread in the Oracle forum he is running Oracle 8.x which doesn't support the standard join syntax ... well, WTF!!! why didn't he/she say so\t i mean, after all, this is the mysql forum!!!! hey ablemabel, have you tested this in mysql yet
VIEWS ON THIS POST

144

Posted on:

Monday 29th October 2012
View Replies!

cannot enter "Structure" (was "Error, help!")

I cannot enter"Structure " MySQL said: #1 - Can't create/write to file 'C:\WINDOWS\TEMP\#sql_8e8_0.MYI' (Errcode: 2)
VIEWS ON THIS POST

169

Posted on:

Monday 29th October 2012
View Replies!

3.28.58 to 4.1.20 kills my server, the command execution order is different, help me

Folks, I have an old server with MySQL 3.28.58 and I just got a new server and installed MySQL 4.1.20 I kept the same config file, so there shouldn't be sufficient change (heck, the new server got 4gigs ram, the old one just 2). but now I've noticed that the 2 versions are carrying the commands differently, for example OLD SERVER: Code: mysql> EXPLAIN SELECT t.*, p.*, g.g_dohtml AS usedohtml FROM ibf_topics t LEFT JOIN ibf_posts p ON (t.tid=p.topic_id) LEFT JOIN ibf_members m ON (p.author_id=m.id) LEFT JOIN ibf_groups g ON (m.mgroup=g.g_id) WHERE t.forum_id IN (106) AND t.approved='1' AND p.new_topic='1' ORDER BY start_date DESC LIMIT 0,5; +-------+--------+--------------------+----------+---------+-------------+-------+----------------------------+ | table | type | possible_keys | key | key_len | ref | rows | Extra | +-------+--------+--------------------+----------+---------+-------------+-------+----------------------------+ | t | range | forum_id,last_post | forum_id | 2 | NULL | 1397 | where used; Using filesort | | p | ref | topic_id | topic_id | 4 | t.tid | 12690 | where used | | m | eq_ref | PRIMARY | PRIMARY | 3 | p.author_id | 1 | | | g | eq_ref | PRIMARY | PRIMARY | 4 | m.mgroup | 1 | | +-------+--------+--------------------+----------+---------+-------------+-------+----------------------------+ 4 rows in set (0.02 sec) New Server: Code: mysql> EXPLAIN SELECT t.*, p.*, g.g_dohtml AS usedohtml FROM ibf_topics t LEFT JOIN ibf_posts p ON (t.tid=p.topic_id) LEFT JOIN ibf_members m ON (p.author_id=m.id) LEFT JOIN ibf_groups g ON (m.mgroup=g.g_id) WHERE t.forum_id IN (106) AND t.approved='1' AND p.new_topic='1' ORDER BY start_date DESC LIMIT 0,5 -> ; +----+-------------+-------+--------+----------------------------+---------+---------+-------------------------+---------+----------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+--------+----------------------------+---------+---------+-------------------------+---------+----------------------------------------------+ | 1 | SIMPLE | p | ALL | topic_id | NULL | NULL | NULL | 1269621 | Using where; Using temporary; Using filesort | | 1 | SIMPLE | t | eq_ref | PRIMARY,forum_id,last_post | PRIMARY | 4 | dgemu_ibfdb.p.topic_id | 1 | Using where | | 1 | SIMPLE | m | eq_ref | PRIMARY | PRIMARY | 3 | dgemu_ibfdb.p.author_id | 1 | | | 1 | SIMPLE | g | eq_ref | PRIMARY | PRIMARY | 4 | dgemu_ibfdb.m.mgroup | 1 | | +----+-------------+-------+--------+----------------------------+---------+---------+-------------------------+---------+----------------------------------------------+ 4 rows in set (18.02 sec) Due to the difference in how it processes the query, I'm running through over a MILLION AND A QUARTER more queries on the new server than what I did in the old server - what on earth makes it work like that how can I make it work the queries work like they did in 3.23.58 Please help, I'm pretty desperate.. - Ben Pere
VIEWS ON THIS POST

151

Posted on:

Monday 29th October 2012
View Replies!

Database design help

I'm making a database that will hold real estate property listings and I am hoping to get some suggestions as to it's design. There are 3 different types of properties the database will hold: residential, commercial, and land. Right now I have a "properties" table containing fields that are common to all properties and 3 other child tables for the 3 types of properties each with the primary key also being the foreign key holding the primary key in the "properties" table. The only thing I am uncomfortable with about this design is the fact that the only way for me to know which of the 3 tables a given record in the "properties" table belongs to is if I also have a "type" field in the "properties" table that specifies which table to SELECT from. This means that I would have to first run a SELECT on the "properties" table and get the "type" for the record and then once I have that I will know which of the 3 child tables I need to select from to get further details. Maybe this is the only way to do this, but I would like to be able to select all information of a property in 1 query. I realize that I could select from the child table first and then join with the parent, but this wouldn't work if I wanted to select ALL properties with ALL of the fields. I don't even know if something like that (selecting every detail from all tables) is possible.
VIEWS ON THIS POST

132

Posted on:

Monday 29th October 2012
View Replies!

Duplicating tables help.

I am looking to duplicate a bunch of tables from one DB to another (on the same server) and in my research I found this method of doing it: CREATE TABLE destination_db_name.destination_table_name SELECT * FROM source_db_name.source_table_name HOWEVER after I thought that this would work perfectly, I now realise it doesn't duplicate indexes and autonumbers etc... Is there anyway of doing that so that will duplicate that too I want the duplicate table in the other DB to be a FULLY duplicate table. My MySQL is MySQL 4.0.26-standard
VIEWS ON THIS POST

171

Posted on:

Monday 29th October 2012
View Replies!

Query required.Urgent.Plz help.

Can anyone help me in building a query in which the table contains a field called name varchar and stdate date. Given this can anyone give me a query to get the total number of names of the present month.The month has to be taken automatically say using curdate().
VIEWS ON THIS POST

147

Posted on:

Monday 29th October 2012
View Replies!

Optimizing many-to-many query, help appreciated

! As I haven't had that much experience with databases, and I've already spent a whole lot of time on this issue, I thought I'd turn to some professionals and perhaps get a few pointers. (Also posted this on MySQL forums, but the more suggestions the better for me :)) Issue is simple, find ways to optimize these types of queries. Using a many-to-many design here -- table of articles, table of categories and a table combining them. One article resides in many categories, so the combining articlecategory table is set up as: Code: articleid - INT 10 UNSIGNED categoryid - SMALLINT 5 UNSIGNED Indexes for testing purposes on every combination. I populated the tables with testing data, about 2800 unique articles with dummy data, and filled the connecting table (articlecategory) with about 16000 entries pointing to categories in the range of 1-150 for now. Query in general: Code: SELECT DISTINCT ac.articleid FROM articlecategory AS ac JOIN article ON article.articleid = ac.articleid WHERE ( ac.categoryid = 1 AND NOT EXISTS ( SELECT 1 FROM articlecategory WHERE articleid = ac.articleid AND categoryid = 4 ) OR NOT EXISTS ( SELECT 1 FROM articlecategory WHERE articleid = ac.articleid AND categoryid IN (23,25) ) ) ORDER BY article.publicdateline ([mysql]tags[/mysql] seem not to work] -- Trying to get articles that have been put into category [(1 AND NOT 4) OR (23 OR 25)], and sort them by date. Simple in theory, but harsh on MySQL. One major hog here is the DISTINCT, otherwise every articleid comes as many times as it has categories. Sorting also takes its fare share of resources. Hopefully some of you have some ideas or other ways to address this issue and lessen the load on the server.
VIEWS ON THIS POST

190

Posted on:

Monday 29th October 2012
View Replies!

Query help - sort the listings by ID number

I have a field called post_titles and the primary key is called post_id. Let's say I want to retrieve 5 random entries from this table, but I want to be able to sort the 5 entries by their post_id. How can I do so I've tried the following query but it doesnt work. PHP Code: $sql2="select*frompostingsorderbyRAND()orderbypost_idDESC";
VIEWS ON THIS POST

149

Posted on:

Monday 29th October 2012
View Replies!

Join help

I am writing a comment system a la slashdot and digg where users can reply to the article or they can reply to another users comment (and it is indented accordingly). My database is set up like this: Code: c_id | a_id | comment | ref_c_id | timestamp 1 5 comm1 0 1:00am 2 5 comm2 0 1:30am 3 5 comm3 2 2:00am 4 5 comm4 1 2:30am 5 5 comm5 2 3:00am If ref_c_id equals 0, that is a reply to the article itself. If ref_c_id equals a non-zero number, it's a reply to it's respective c_id. How can I get the output to look as follows: Quote: comm1 -->comm4 comm2 -->comm3 -->comm5 I hope this makes sense.
VIEWS ON THIS POST

155

Posted on:

Monday 29th October 2012
View Replies!

IDs that have a value of 'A' AND 'C' (was "Complex query help")

i have a table with two columns, ID and VALUE. Imagine this data in the table Code: ID VALUE 1 A 1 B 1 C 2 A 2 B i want to know all IDs that have a value of 'A' AND 'C' so in this case, it would be '1' how do i do this i am running MySQL 4.1.14-standard
VIEWS ON THIS POST

128

Posted on:

Monday 29th October 2012
View Replies!

Joins help

hi there, i have these two queries: Code: SELECT totalscore FROM top_scores WHERE userid=3 SELECT MIN(score1),MIN(score2),MIN(score3) FROM scores WHERE userid=3 how can i use join to make that query into one, but still selecting from the two seperate tables
VIEWS ON THIS POST

159

Posted on:

Monday 29th October 2012
View Replies!

What people have more than one photo - query help

people I have a table which maps people to their photos like so: Code: CREATE TABLE `member_photos` ( `AccountID` int(10) unsigned not null, `Image_ID` int unsigned not null, PRIMARY KEY (`AccountID`, `Image_ID`) ) ENGINE=MyISAM default charSET=latin1; a member may have more than one photo I need to get a list of all the members (AccountID) who have more than one photo (Image_ID) can anyone help me with such simple query
VIEWS ON THIS POST

201

Posted on:

Monday 29th October 2012
View Replies!

Index/query help?

I have a rather large table with about 5 million unique records. I am trying to make a query that lists the unique artists in the table. there are a set of links A B C D E F etc that when they click on them displays the artists name that starts with the letter they clicked on. I display them in a php page that displays 50 records at a time with a little index of links at the bottom where I can have them click on a page number and show the next or previous 50 records. There are approximately 178,610 unique artists in the table the table structure is as follows: id int(11) No auto_increment disc int(11) No 0 version varchar(100) latin1_swedish_ci No track int(11) No 0 artist varchar(166) latin1_swedish_ci No album varchar(166) latin1_swedish_ci No song varchar(166) latin1_swedish_ci No year varchar(25) latin1_swedish_ci No credits varchar(150) latin1_swedish_ci No asin varchar(10) latin1_swedish_ci No upc varchar(15) latin1_swedish_ci No 0 ean varchar(20) latin1_swedish_ci No date varchar(20) latin1_swedish_ci No 0000-00-00 label varchar(100) latin1_swedish_ci No genre varchar(50) latin1_swedish_ci No Indexes: Keyname Type Cardinality Action Field PRIMARY PRIMARY 3393593 id artist INDEX 65261 artist album INDEX 178610 album song INDEX 1696796 song asin INDEX 226239 asin artist_2 FULLTEXT 1 artist album song year credits label genre the queries I am using resemble the following: SELECT COUNT(DISTINCT artist,album) as `total` FROM `tablename` WHERE `album` LIKE 'A%' SELECT DISTINCT `artist`,`album` FROM `tablename` WHERE `album` LIKE 'A%' ORDER BY `album` limit start,50 I need the count of all records for the displaying of: start - end of total number is there a way to optimize the tables, indexes and/or queries to make this faster as it takes forever to pull the results
VIEWS ON THIS POST

187

Posted on:

Monday 29th October 2012
View Replies!

Need help restoring .sql file

I just installed MySQL 5.x, and it's been a while since I've used an earlier version of the program. I have phpMyAdmin, but I'm still trying to figure out the syntax for the config.inc.php file, so I can't use that program for a while. My earlier version of MySQL let me restore a backup like this: C:\mysql\bin> mysql -p forums
VIEWS ON THIS POST

176

Posted on:

Monday 29th October 2012
View Replies!

SQL Selecting help

So... I'm a total SQL n00b and need some help with queries. Here's my question... I have two tables that have columns with similar data. product_line.prodline_num has 86 unique fields product.ProLine has 73 unique fields I'm trying to make a query that will identify the 13 unique fields that ProLine does not have. I just can't seem to figure it out.
VIEWS ON THIS POST

134

Posted on:

Monday 29th October 2012
View Replies!

Need help on WHERE clause

I recently purchased a forum from http://www.webwizguide.com/webwizforums/ I need to modify the SQL slightly as when i click a link to one of my forums within my main forum, I only want that Category forum to show. eg; default.aspc=1 to only show Category 1 forum Code:
VIEWS ON THIS POST

148

Posted on:

Wednesday 7th November 2012
View Replies!

Sub-Query help

Hi i have the below query but i keep getting error message saying cant have 2 conditions in the sub-query. What can i do as i need both parts in order to get the correct data Code: SELECT DISTINCT dbo.tbl_property.PROPREF, dbo.tbl_property.APPLIANCENO, (SELECT COUNT(AssetAppID) AS AssetAppIDS FROM dbo.tbl_appliance WHERE (propref = N'1dum') GROUP BY AssetAppID, removedate HAVING (COUNT(AssetAppID) IS NOT NULL) AND (removedate IS NOT NULL)) AS ASSETAPPID FROM dbo.tbl_property INNER JOIN dbo.tbl_appliance AS tbl_appliance_1 ON dbo.tbl_property.PROPREF = tbl_appliance_1.propref WHERE (dbo.tbl_property.PROPREF = N'1dum')
VIEWS ON THIS POST

143

Posted on:

Wednesday 7th November 2012
View Replies!

Need help with implementing pricess with sql

Hi I have the following process and do not know how to implement this with sql query, do i need to write a procedure Process is as follows Intro: we have a employee evaluation process and the eval form goess through as many hierarchy the employee has above him. The last person is always a director. Employee as empid and supid (supervisorid) steps 1) Hr initiates the process by selecting an employee so find empid 2)get the managerid for tht employee tht is supid 3)send it for eval to the supid ( this will be internal process so we need to check accept or reject at every step excluding the first step as in first step we will hav magaer fill and pass it to next level that managers manager 4)to let go the eval to next level check status flag accept or reject 5) if accept go to next level tht next manager 6)if reject go back one level , keep going back until to u get the status flag as accept again 7)keep checking levels 8) whn no more levels remaining for eval tht is whn reached to highest level director stop and pass the eval to employee 9) get employees sign or approval on eval and store it in dbase now i know there is looping in this process i guess there will be loop within loop can anyone help me with this i need to get it done soon
VIEWS ON THIS POST

118

Posted on:

Wednesday 7th November 2012
View Replies!