smti

Hello, I have what seems like a basic question, but I'm stumped. Here is my situation; I have a table called courses with the following fields: RID (The Record ID, Pri. Key) Subject CID (Course Section) Section Name Instructor Location StartTime EndTime Days What I want to do: I would like to generate a query which provides a list courses taught by each instructor and grouped by the instructor like so: Adams - Course A - Course B - Course C Jones - Course A - Course B - Course C ....And so on...... When I tried the following query: Select Instructor, Location, Days, StartTime, EndTime from Courses Group by Instructor; I do get some results, but not all the courses a particular instructor teaches are shown. Is there a better way to make this happen? Thanks for any insight!

I also tried this with the same result: LOAD DATA LOCAL INFILE '/home/jalewellyn/tstProducts.csv' INTO TABLE tstProducts FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\r\n' IGNORE 1 LINES (Name, Type, Brand, Description, ABV, TimesAvailable, Image, Website);

Hello, I wrote a bash script to import some data from a CSV file. The code is as follows: Load Data local infile '/home/<USERNAME>/tstProducts.csv' into table tstProducts fields terminated by ',' enclosed by '"' lines terminated by '\n' (Name, Type, Brand, Description, ABV, TimesAvailable, Image, Website) Ignore 1 Lines; When I run the bash script I get the following error: syntax error near unexpected token `(' Any thoughts on why this may be occurring? I checked my code against a sample and everything looks correct. Any help or insight you can provide would be greatly appreciated! -smti

Hello, I could use a little help solving the following problem: I have two tables: Counties and Products. The counties table contains a list of counties in which products are sold and the products table contains a list of products that are sold. I need to know the best way to write a query which produces a list products given the name of a county. My table structure structure is as follows: Table: Counties CountyID Name Table: ProductBrands BID Brand CountyID --> Foreign key relates to Counties.CountyID I tried using a join statement; however, I seem to get erroneous results. Any help would be greatly appreciated!

The field: type_name does in fact exist in the SubProblemTypes table. Do you mean that a value must exist before the record can be created? -smti

Hello - I am having a problem adding a new record to a table named: WizardChoices. When I execute the query I receive the following error: Cannot add or update a child row: a foreign key constraint fails (`resticket`.`WizardChoices`, CONSTRAINT `WizardChoices_ibfk_3` FOREIGN KEY (`sub_problem_type`) REFERENCES `SubProblemTypes` (`type_name`) ON DELETE SET NULL ON UPDATE NO ACTION) How can I correct this? Here is the structure of both tables; the relationship view is also attached. describe SubProblemTypes; +-----------------------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-----------------------+-------------+------+-----+---------+----------------+ | type_id | int(3) | NO | PRI | NULL | auto_increment | | type_name | varchar(25) | NO | MUL | NULL | | | assoc_primary_problem | int(3) | NO | MUL | NULL | | | type_description | varchar(25) | NO | | NULL | | +-----------------------+-------------+------+-----+---------+----------------+ 4 rows in set (0.00 sec) mysql> describe WizardChoices; +-----------------------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-----------------------+-------------+------+-----+---------+----------------+ | choice_id | int(3) | NO | PRI | NULL | auto_increment | | assoc_question_number | varchar(3) | NO | MUL | NULL | | | choice_text | text | NO | | NULL | | | primary_problem_type | varchar(50) | NO | MUL | NULL | | | sub_problem_type | varchar(50) | YES | MUL | NULL | | +-----------------------+-------------+------+-----+---------+----------------+ 5 rows in set (0.00 sec) Any assistance would be greatly appreciated! Thanks!

Hi Everyone: The Situation: I am working on a web-based questionnaire project. I have two tables: WizardQuestions and WizardChoices -- WizardQuestions stores the questions themselves, while the WizardChoices stores the possible answers associated with each question. Database Schema: WizardQuestions has the following fields: 1. question_id 2. question_header 3. question_body WizardChoices has the following fields: 1. choice_id 2. assoc_question_id (Relates to table above) 3. choice_text The Problem: Using one query, I need to get: 1. question_header from WizardQuestions 2. question_body from WizardQuestions 3. choice_text from WizardChoices associated with that question. ** Header and question body should be returned once ** I have tried: The inter-select: Select wizard_questions.question_id, wizard_questions.question_header, wizard_questions.question_body FROM wizard_questions WHERE wizard_questions.question_id = (Select wizard_question_choices.choice_text from wizard_question_choices where wizard_questions.question_id = wizard_question_choices.assoc_question_id); The JOIN: SELECT WizardQuestions.question_header, WizardQuestions.question_body, WizardChoices.choice_text FROM WizardQuestions JOIN WizardChoices ON WizardQuestions.question_id = WizardChoices.assoc_question_id; I have had very little success. If I run the join statement without a GROUP BY statement, then the question_header and question_body are shown twice -- with both choice options, but if I add a GROUP BY statement, then the header and body are shown, but with one option only. Any help would be greatly appreciated!

Hello, I am a bit stumped as to how I can combine a select and select count statement together. I need to select fields from one table and count the number of records in another table using one of the fields from the first table's results. My table structure is as follows: Trackchasers contains the following fields: 1. cid 2. cfn 3. ccitty 4. rgid 5. ctid Both the trackchasers and trackvisits tables share the cid field. I am able to execute queries individually to get some of the data I need, however, I need to combine these queries if possible so it is easier to work with in PHP. My selection query: select cid, cfn, cln, ccity, rgid, ctid from trackchasers My count query: select count(cid) as trackvisits from trackvisits where cid=12; How can I combine these two statements to display all the fields I need plus the counting code? I tried using an inter join query, but had little luck. Thanks for any help you can provide! -smti