SEARCH YOUR SOLUTION HERE  

trouble with query

I have a database (Access 2000) that holds ship itinerary information and a list of businesses at ports that are visited

I want to get a list businesses at the ports a ship visits over a period of time:

Eg. Find all businesses in the ports that the Carnival Destiny visits over the next fortnight.

---------------------------------------------------------------------------------
TABLES:

ItMain:

CID primary key Cruise ID
Ship name of ship

ItDates :

CID
Date dates an itinerary is repeated on e.g. 7-7-03, 7-14-03 etc

ItPorts:

CID
Day cruise day (seven day cruise 0-1-2-3-4-5-6)
Port port that ship visits


BizMain:

BID primary key - Business ID
Port port that business is located in

-----------------------------------------------------------------------------------

I thought this would be easy to solve, even with my poor knowledge of SQL.

Alas, No! My subquery refused to cooperate!

Can anyone post a solution for me, or even point me in the right direction (so that I can have the pleasure of solving it myself)

Posted On: Tuesday 20th of November 2012 12:02:48 AM Total Views:  154
View Complete with Replies




Related Messages:

Efficiency trouble with a very big query   (158 Views)
I seem to have discovered that, as a young programmer and college student, I know just enough SQL to be dangerous.
Having trouble connecting to a MySQL db   (305 Views)
I've been given the connection details for a mysql database from a client but am having no luck connecting to it- when I try to connect from a PHP script simply nothing happens and when I try from a GUI on my computer I get a 'connection failed'. Are there any common causes for this sort of thing that I should be aware of Or do I need to get in touch with the database administrator I don't want to make any stupid requests before trying the obvious solutions!
Having trouble with a large *.sql import   (109 Views)
I am trying to import a very large .sql file of about 165mb. When i try to import the file i use "source C:\DB.sql" and it runs for a bit and then stops. When i try to query on this table it crashes. But when i do "show columns from DB;" is show me the information correctly. I did update my my.ini config file with: innodb_data_file_path = cseibdata1:10M:autoextend still not working. Does anyone have ideas of what im doing wrong
Group by order by trouble   (196 Views)
i have the following table PHP Code: `msg`( `pin`int(10)NOTNULLauto_increment, `msg_pin`int(11)NOTNULLdefault'0', `poster_pin`int(11)NOTNULLdefault'0', `posted`timestampNOTNULLdefault'0000-00-0000:00:00', PRIMARYKEY (`pin`), KEY`msg_pin`(`msg_pin`) )ENGINE=InnoDBDEFAULTCHARSET=latin1; in which i have data in the following format PHP Code: pin|msg_pin|poster_pin|posted 18 16 3 2006-02-2417:06:26 19 16 3 2006-02-2417:10:17 20 16 3 2006-02-2417:11:23 21 16 3 2006-02-2417:12:02 22 16 3 2006-02-2417:14:58 23 16 3 2006-02-2417:16:41 24 16 3 2006-02-2417:17:39 35 18 3 2006-03-0317:25:09 and what im trying to achieve is to return the results in this order (the post recent posted date first) PHP Code: 35 18 3 2006-03-0317:25:09 24 16 3 2006-02-2417:17:39 but i only want to return the most recent result for a particular msg_pin (so in the above example i only want 1 return for msg_pin 16). if i use a query like PHP Code: select*frommsgGROUPBYmsg_pinORDERbypostedDESC then i get a result like PHP Code: 35 18 3 2006-03-0317:25:09 18 16 3 2006-02-2417:06:26 I am using mysql v4.1 so i can use subqueries and did try a subquery containg a limit 1 clause but got an error msg saying that a limit in a subquery wasnt supported.
Having trouble with a query...   (113 Views)
Im dealing with two tables student(columns are sno, sname, address, and sex) and results(columns are cno, sno, and score). I need a query of he snames of femal (sex = 'f') students who take every course taken by J. Brown (sname). So far I have: Code: SELECT student.sname FROM student, result WHERE student.sno = result.sno AND student.sex = 'F'; Now what I am having trouble with is finding the females who are in every course with J. Brown. Now cno from the result table is the course number I need to do something with that but I'm not sure what. Do I need to use a GROUP BY or something along those lines Any ideas would help.
trouble restricting results.   (85 Views)
I have a grandparent, parent and child relationship. Each is a business_id. each business may (or may not), have a stylesheet record in the second table. I will be entering an id to the WHERE clause. I need to bring back those ids on senior levels, which do have a record in table_2. so, if a child_id is say 32 and its parent records each has a record in table_2 (AND the live_or_dev col is 'live'), I need to get those ids, in order from grandparent->parent->child - or even grandparent->child OR grandparent->parent. Just whatever ids have a stylesheet I can't get my head around it but it seems to be join issue but I am not sure. Code MySQL: SELECT level_3.id as id_3 , level_2.id as id_2 , level_1.id as id_1 from businesses AS level_1 left outer JOIN businesses as level_2 on level_2.id = level_1.parent_id left outer join stylesheets as L2_s on L2_s.business_id = level_2.id and L2_s.live_or_dev = 'live' left outer JOIN businesses as level_3 on level_3.id = level_2.parent_id left outer join stylesheets as L3_s on L3_s.business_id = level_3.id and L3_s.live_or_dev = 'live' left outer join stylesheets as L1_s on L1_s.business_id = level_1.id and L1_s.live_or_dev = 'live' WHERE level_1.id = 7
trouble connecting   (168 Views)
Im trying to connect to my database, but cant, so I put this echo in there \ PHP Code: $conn=mysql_connect($dbhost,$dbuser,$dbpass)ordie('Errorconnectingtomysql');mysql_select_db($dbname)ordie('Errorconnectingtodb');echo$conn; And I get this, any idea what it means Resource id #4
Having trouble executing stored procedure through .net web page   (139 Views)
I am trying to execute a stored procedure through .NET but the stored procedure doesn't seem to work. I know that my code in .NET works without any errors and I am almost sure my stored procedure code is correct but it is not executing what I want it to do. In the code if I want to change the date in the drop down box and then hit the Save button it should execute the stored procedure in the Save button click event to update the record in the database. Here is the code for the Save button: Code: Protected Sub SaveButton_Click(ByVal sender As Object, ByVal e As System.EventArgs) ' Using onyxConnection Try dim @iReturnCode Dim cmd As New SqlCommand onyxConnection.Open() cmd.Connection = onyxConnection cmd.CommandType = CommandType.StoredProcedure cmd.CommandText = "cnpcuStudentSessionDate" cmd.Parameters.AddWithValue("@iProductId", lblProductID.Text) cmd.Parameters.AddWithValue("@chUserId", lblUpdateBy.Text) cmd.Parameters.AddWithValue("@dtPurchaseDate", ddlSession.SelectedValue) cmd.Parameters.AddWithValue("@dtSchEnrDate", ddlSession.SelectedValue) 'cmd.Parameters.AddWithValue("@iProductId", txtAppProjNum.Text) 'cmd.Parameters.AddWithValue("@iProductId", txtAppProjNum.Text) cmd.ExecuteNonQuery() Catch ex As Exception Dim a, b a = ex.ToString b = a.ToString Finally End Try End Sub In the stored procedure I am selecting an existing record from the database and updating it with a new date. Here is the code for the stored procedure: Code: use DB if exists (select * from sysobjects where name = 'cnpcuStudentSessionDate' and type = 'P') drop proc cnpcuStudentSessionDate go create procedure cnpcuStudentSessionDate @iProductId int, @chUserId nchar(10), @dtPurchaseDate datetime, @dtSchEnrDate datetime, @iReturnCode int = Null output With Encryption as set nocount on /* declare variables */ declare @dtNow datetime /* M003D Begin*/ declare @iIndividualId int, @dtReceivedDate datetime, @dtEnrollmentDate datetime, @vchScheduleNumber varchar(255), @vchCreditScore smallint, @siGuidesRequired smallint, @dcDeposit decimal(9, 2), @dcPendingDeposit decimal(9, 2), @dcDiscount decimal(9, 2), @dcShareDeposit decimal(9, 2), @dcNetAdjustment decimal(9, 2), @siTerm smallint, @dcPaymentAmount decimal(9, 2), @dtFirstDue datetime, @dtNextDue datetime, @dcInterestRate decimal(8, 5), @dcGrossSale decimal(9, 2), @dcInterest decimal(9, 2), @dcContract decimal(9, 2), @dcCurrentBalance decimal(9, 2), @dcContractPaid decimal(9, 2), @dcInterestPaid decimal(9, 2), @dcAccruedInterestBalance decimal(9, 2), @dcInterestPerDiem decimal(9, 2), @dcEstimatedPayoff decimal(9, 2), @siTotalPayments decimal(9, 2), @dcTotalPaid decimal(9, 2), @dtDisbursementDate datetime, @dcChargeOff decimal(9, 2), @dcInvoiceBalance decimal(9, 2), @dtInvoiceDate datetime, @iInvoiceStatus int, @dcAdjDiscount decimal(9, 2), @dcSalesTax decimal(9, 2), @dcPendingSalesTax decimal(9, 2), @iOwnerId int, @dcFinancedSalesTax decimal(9,2) --M001A /*M003D End */ --M003A Begin declare @iSiteId int, @chLanguageCode nchar(4), @iContactId int, @chProductNumber nchar(20), @vchSerialNumber nvarchar(50), @flQuantity onyxfloat, @iTrackingId int, @iSourceId int, @iStatusId int, @iAccessCode int, @vchUser1 nvarchar(30), @vchUser2 nvarchar(30), @vchUser3 nvarchar(30), @vchUser4 nvarchar(30), @vchUser5 nvarchar(30), @vchUser6 nvarchar(30), @vchUser7 nvarchar(30), @vchUser8 nvarchar(30), @vchUser9 nvarchar(30), @vchUser10 nvarchar(30), @dtUpdateDate datetime --Variables needed for incident Update. declare @iIncidentId int, @tiLCMSAccount tinyint, @iIncidentCategory int, @iIncidentTypeId int, @vchAssignedId nvarchar(255), @vchProductId nvarchar(255), @vchDesc1 nvarchar(255), @vchDesc2 nvarchar(255), @vchKeyWords nvarchar(255), @iPriorityId int, @iCode1 int, @iCode2 int, @iCode3 int, @iCode4 int, @iTime int, @iLabor int, @tiImage tinyint, @chAssignedTo nchar(10), @chInsertBy nchar(10), @dtInsertDate datetime, @tiRecordStatus tinyint, @iReminderId int --M003A End /* initialize variables */ set @dtNow = getdate() /* set default values */ set @iReturnCode = 0 if isnull(@iProductId,0) > 0 and isnull(@vchUser7,0) > 0 begin --Get the current values of the customer product record. SELECT @chLanguageCode = [chLanguageCode] ,@iContactId = [iContactId] ,@chProductNumber = [chProductNumber] ,@vchSerialNumber = [vchSerialNumber] ,@flQuantity = [flQuantity] ,@dtPurchaseDate = [dtPurchaseDate] ,@iTrackingId = [iTrackingId] ,@iSourceId = [iSourceId] ,@iStatusId = [iStatusId] ,@iAccessCode = [iAccessCode] ,@vchUser1 = [vchUser1] ,@vchUser2 = [vchUser2] ,@vchUser3 = [vchUser3] ,@vchUser4 = [vchUser4] ,@vchUser5 = [vchUser5] ,@vchUser6 = [vchUser6] ,@vchUser7 = [vchUser7] ,@vchUser8 = [vchUser8] ,@vchUser9 = [vchUser9] ,@vchUser10 = [vchUser10] ,@dtUpdateDate = [dtUpdateDate] FROM [OnyxTCN].[dbo].[CustomerProduct] WHERE [iProductId] = @iProductId --If we have successfully registered this course in the LMS, proceed. if @tiLCMSAccount = 1 begin --Set the tracking ID for the product to the Course Registered tracking id. --Sandbox: 5465 Live: 7743 Set @iTrackingId = 7743 end else begin --Set the tracking id for the product to the Course Not Registered tracking id. --Sandbox: 5474 Live: 7771 Set @iTrackingId = 7771 end if (select iTrackingId from customerProduct with (nolock) where iProductId = @iProductId) @iTrackingId begin --Update the customer product table. exec @iReturnCode = wbospsuCustomerProduct @iSiteId, --iSiteId @iProductId, --iProductId @iIndividualId, --iOwnerId @chLanguageCode, --Language Code @iContactId, --Contact ID @chProductNumber, --Product Number @vchSerialNumber, --Serial NUmber @flQuantity, --Quantity @dtPurchaseDate, --Purchase Date @iTrackingId, --Tracking Id @iSourceId, --Source Id @iStatusId, --Status Id @iAccessCode, --Access Code @vchUser1, --UDF1 @vchUser2, --UDF2 @vchUser3, --UDF3 @vchUser4, --UDF4 @vchUser5, --UDF5 @vchUser6, --UDF6 @vchUser7, --UDF7 @vchUser8, --UDF8 @vchUser9, --UDF9 @vchUser10, --UDF10 @chUserId, --chUpdateBy @dtUpdateDate, --dtUpdateDate null, --tiRecordUnlock null --tireturnType end return @iReturnCode end go grant execute on cnpcuStudentSessionDate to public go
Weird trouble   (87 Views)
, I have created a simple table with 4 fields: id (auto incr. int) prefix (varchar) startrange (int) (these are ranges and this is starting value of the range) endrange (int) ( and this is ending value of the range) The data in it is like the following: 1, CL, 001, 499 2, AB, 000, 999 3, 66, 001, 999 4, 92, 201, 400 5, G, 1001, 5000 6, CC, 001, 2000 Now the above data has to be searched by a user: So for eg. if user enters: 66121 it would show the record 3 coz i must search with and without prefix (i.e combine the prefix with start and end range). But if user enters only: 121 then i have to show: 1, 2, 3, 6 (coz this is without prefix but comes in starting and ending range) And user can also do: CL then it will show: 1 (I have taken care of this by stripping out the numbers from the user's search and then just searching the alphabets in prefix and show the matching results. So if user enters CL101 then i remove the 101 and just search prefix field for CL and show the results. This one is working fine. The above 2 are not working for me which are i.e all data specified by user is numeric including or not including prefix...how to work it out
MSACCESS criteria troubles   (77 Views)
(Using Microsoft Access) I'm trying to make a query for my database but i'm having a hard time getting something to work. We have a field called volume number and we put a bunch of numbers separated by commas. An example a a few field follows. Code: Record1:100 Record2:100, 100-1, 100-2 Record3:100-1, 101, 101-1, 101-2 Record4:101, 101-1 Record5:100-2, 100-3 Record6:100, 100-1 Record7:100-3 Depending on what part of the month it is we need to get all the 100 together, the next week we get the 100-1 together so on and so forth The problem arises when we need to get the single numbers. If we do a query for 100 then those records that only have 100-3 will also show up in our list. So far I've tried all these criterias to get it to display properly Code: like "*100[!-]* --> doesn't work "100" --> This one only gives records that hold 100 but not those that hold multiple values like "*100*" --> Fetches all the records with 100 in it as well as the dashes and subnumbers I've run of ideas, any suggestions
Having trouble importing databases. vbulletin   (132 Views)
The sql file is too big for phpmyadmin, and they suggest ssh, but im not so good with it, and i have tried every combination of command I have found when researching on google. I then came across a script called "bigdump". I have all tyhe settings correct in the bigdump.php but I keep getting this error: Database connection failed due to Access denied for user 'USERNAME'@'cgihost' (using password: YES) they ask to change these settings which i have the correct DB username and pass. The server isnt "localhost" with my new hosting company. (netfirms) $db_server = 'MYSQLHOST'; $db_name = 'NAME'; $db_username = 'NAME'; $db_password = 'XXXXX'; I have tried changing permissions and following all directions from bigdump. could someone please help me
Query troubles / forum app   (94 Views)
I am currently creating a simple forum script. The following tables actually contain more columns, but those columns have nothing to do with this query, so I omitted them here. TABLE: thread COLUMNS: id,forum_id,title TABLE: post COLUMNS: id,user,thread_id,time As may be clear from this structure, some of a thread's info, such as the time of creation and the creator (user), is defined in the data record of the first post of that thread. Thread to post is a one-to-many relation. I need a query which selects a total of 5 thread.title(s). Together with each of these 5 selected threads, the post.user and post.time from the corresponding first post of that thread need to be selected. (Of all posts with some selected thread_id, the data from the post with min(time) of these posts should be selected). Furthermore, thread.forum_id = 0. This should be done in one query. Thank you for your time and knowledge.
Subselect trouble   (109 Views)
A table exists where the same id can be issued more than once. Each id has an accompanying color. Code: CREATE TABLE `xorder` ( `id` INT( 3 ) NOT NULL , `color` VARCHAR( 20 ) NULL , ) ENGINE = MYISAM I need to select all ids where there is no duplicate id having the color 'blue'. Code: INSERT INTO `xorder` ( `id` , `color` ) VALUES ( '1', 'green' ), ( '2', 'red' ), ( '3', 'black' ), ( '1', 'yellow' ), ( '2', 'blue' ), ( '1', 'orange' ), ( '2', 'white' ), ( '2', 'purple' ), ( '4', 'blue' ), ( '5', 'gray' ) List entire table contents... SELECT * FROM xorder ORDER BY id ASC; Code: id color 1 green 1 yellow 1 orange 2 red 2 blue 2 white 2 purple 3 black 4 blue 5 gray So the id's I need returned from this data are: Code: 1 3 5 ...since no associated orders have the color blue. I could loop through the orders and use looped mysql queries but I really don't want to go there. Any suggestions on how this could be done in a single query
Create table trouble   (83 Views)
InnoDB tables doesn't store the start value of the auto_increment in the table. Instead it will do a SELECT MAX(PK column) the first time it will need to use the auto_increment value after the server has started. Then it only caches it in RAM until the server is switched off. Which also means that if you delete the last records in the table and restart it imediately afterwards, mysql will start to reuse id's since SELECT MAX() is going to return the currently highest id.
UTF-8 causing trouble :(   (111 Views)
Hi , I need some help with the following issue : The accentuated characters of the content taken form the UTF-8 database gets garbled once displayed on my website. What I don't understand is that everything seems to be properly set : my.ini Code: [mysql] default-character-set = utf8 default-character-set = utf8 character-set-server = utf8 collation-server = utf8_general_ci init_connect = 'SET collation_connection = utf8_general_ci' init_connect = 'SET NAMES utf8' PHP header Code: header("Content-Type:text/html; charset=utf-8"); Metas Code: Table structure Code: CREATE TABLE `mod_geonames` ( `geoname_id` mediumint(9) NOT NULL, `name` varchar(200) default NULL, `latitude` decimal(10,7) default NULL, `longitude` decimal(10,7) default NULL, `country_code` varchar(2) default NULL, PRIMARY KEY (`geoname_id`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8; So, what else could cause the problem I am at a total loss of ideas after extensive googling
More troubles with user permissions   (106 Views)
I'm trying to create 2 isolated users. right now what i have is 2 users with full access Quote: GRANT ALL PRIVILEGES ON *.* TO myUser@localhost IDENTIFIED BY 'pass' WITH GRANT OPTION; This is close to what I want. I want the users to still to be able to create databases (only accessible by the user who created it), but I want to limit the databases that have access to now (ie the mysql database, information_schema database, and a few others) Im not sure if I need to do anything special about the mysql and information_schema since they are kind of special. Anyways how should i go about doing this
UNIX TIMESTAMP Windows server trouble   (227 Views)
, i'm working on a school project (PHP & MySQL). The mysql query below does not return the right date , Code: $itemsql = "SELECT UNIX_TIMESTAMP(dateends) AS dateepoch, items.* FROM items WHERE id = " . $validid . ";"; Everything worked well on the linux server(apache) , but the windows server is returning me this : Wed 31st December 1969 , which is time zone setting of the machine at the time MySQL started. If anyone can help with an alternative to UNIX_TIMESTAMP for windows , i will really be grateful
MySQL 4 query trouble   (110 Views)
Hi i had this query running fine for sql server, i now have to use it on MySQL 4, but it won't run at all, any ideas on how to write it for MySQL 4 query: Code: strSQL = "Select Distinct c.idkey, " & vbcrlf strSQL = strSQL & "c.content, " & vbcrlf strSQL = strSQL & "c.tags, " & vbcrlf strSQL = strSQL & "c.title, " & vbcrlf strSQL = strSQL & "c.timekey, " & vbcrlf strSQL = strSQL & "c.date, " & vbcrlf strSQL = strSQL & "c.type, " & vbcrlf strSQL = strSQL & "(select file from media where timekey = c.idkey and file Like '%Front_X%') as image " & vbcrlf strSQL = strSQL & "from content as c" & vbcrlf Shem
Join trouble   (63 Views)
I'm a little rusty with using JOINs in SQL. I'm trying to use a query that will take the Grade from Grade_View and show the Grade from TechGroup only if they match up. Here's what I have so far, but it doesn't work: "select TechGroup.Grade FROM TechGroup INNER JOIN grade_View ON TechGroup.Grade = Grade_view.Grade" Anyone have any ideas
SQL string trouble   (86 Views)
Originally posted by : fred (fred_espi@hotmail.com) I a student trying to learn ASP but I have some troubles with my SQL querry.I want to have a querry that will depends on the value of a param.I don't know how to insert my dim (prix) as a string into the SQL command. I always got an error message.Is anybody able to help me please, it would be very nice.Thank you very much,Sincerely,Fred