|
List the guests by name and the number of times each has reserved a room at one of our hotels. Arrange the list in order from most-frequent to least-frequent guest.
My two related tables are Guest and Reservation.I can't have the FK GuestNo be one of the attributes.
This is what I have so far.
Select FirstName, LastName, GuestNo FROM GUEST INNER JOIN RESERVATION ON GUEST.firstname = RESERVATION.guestno
I'm not sure about GuestNo.
|
|
|
|
|
SELECT GUEST.FirstName, RESERVATION.GuestNo
FROM GUEST
INNER JOIN RESERVATION ON GUEST.GuestNo = RESERVATION.GuestNo
ORDER BY GUEST.FirstName This is my SQL statement that retrieves all the names of the people who made reservations with the GuestNo appearing as well. I don't know where to incorporate the Count function.
|
|
|
|
|
You need to COUNT(reservation.guestNo) while GROUPing BY Guest.GuestNo
=========================================================
I'm an optoholic - my glass is always half full of vodka.
=========================================================
|
|
|
|
|
SELECT HotelNo, RoomNo from ROOM
LEFT JOIN RESERVATION ON (ROOM.HotelNo = RESERVATION.HotelNo) AND (ROOM.RoomNo = RESERVATION.RoomNo)
WHERE (((RESERVATION.HotelNo) Is Null) AND ((RESERVATION.RoomNo) Is Null))
ORDER BY ROOM.HotelNo
|
|
|
|
|
Access is giving me that HotelNo could refer to more than one table in this SQL statement.
|
|
|
|
|
try now I forgot to add table name
SELECT ROOM.HotelNo, ROOM.RoomNo from ROOM
LEFT JOIN RESERVATION ON (ROOM.HotelNo = RESERVATION.HotelNo) AND (ROOM.RoomNo = RESERVATION.RoomNo)
WHERE (((RESERVATION.HotelNo) Is Null) AND ((RESERVATION.RoomNo) Is Null))
ORDER BY ROOM.HotelNo
|
|
|
|
|
This is second SQL statement that retrieves everyone's name and the GuestNo who have made a reservation at the hotel. I don't know where to add the count function without getting a Syntax Error.
SELECT GUEST.FirstName, COUNT (RESERVATION.GuestNo)
FROM GUEST INNER JOIN RESERVATION ON GUEST.GuestNo =RESERVATION.GuestNo
ORDER BY GUEST.FirstName
GROUP BY GUEST.GuestNo
This is giving me a Syntax error in my query expression GUEST.FirstName, GROUP BY GUEST.GuestNo.
modified 9-Mar-15 23:28pm.
|
|
|
|
|
SELECT COUNT(RESERVATION.GuestNo), GUEST.FirstName, RESERVATION.GuestNo
FROM GUEST INNER JOIN RESERVATION on GUEST.GuestNo = RESERVATION.GuestNo
GROUP BY GUEST.FirstName
I've tried this combination as well but Access tells me, You tried to execute a query that does not include the specified expression 'GuestNo' as part of an aggregate functio
|
|
|
|
|
i am trying to save datagridview records to mysql table...But am getting the error:
"value cannot be null
parameter:datasource"
any help
private sub Save()
Try
con = New MySqlConnection(dbPath)
sql = "SELECT *FROM shubject1"
da = New MySqlDataAdapter(sql, con)
con.Open()
ds = New DataSet()
cmdb = New MySqlCommandBuilder(da)
da.Fill(ds, "subject1")
bsource.DataSource = ds.Tables("subject1")
dgMarksheet.DataSource = bsource
Catch ex As Exception
MsgBox(ex.Message)
End Try
end sub
Private Sub SaveRecords()
Try
ds = New DataSet()
dt = New DataTable()
dt = ds.Tables("subject1")
Me.dgMarksheet.BindingContext(dt).EndCurrentEdit()
Me.da.Update(dt)
MsgBox("Saved", MsgBoxStyle.Information, "Record Changes")
Catch ex As Exception
MsgBox(ex.Message)
Exit Sub
End Try
End Sub
|
|
|
|
|
At which point do you get that error?
|
|
|
|
|
the error is found at dt = ds.Tables("subject1")
|
|
|
|
|
In your save method you declare a new dataset and then you try and reference a datatable "subject1" in that new, empty dataset. Next time point out where you are getting the error!
You need to get the datacontext of the DGV into a dataset before trying to do anything with it.
And dataadaptors must have changed since I last used them, the idea that you can throw an entire datatable at it and get it to update the database was not there in my day, a decade ago 
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
|
Seriously, this isn't a subject that can be answered in a forum post.
Read about indexing[^] first and come back with specific questions.
|
|
|
|
|
.
What an interesting question!
Oh you mean your title is the entirety of your question.
Ok, here is an answer, buy a book on Oracle database development and/or read lots of articles and blogs on how to optimise Oracle queries. DBAs spend years learning this subject, and you want them in a forum post!
I'll put my sarcasm stick away now!
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
|
Hi, i want to get data from table, which has data like that:
ColA ColB
1 12.324.145
2 425.152.643
3 12
After Select the results must be like:
1 12
1 324
1 145
2 425
...
3 12
How can i do this?
|
|
|
|
|
Do a search on split procedures.
Mongo: Mongo only pawn... in game of life.
|
|
|
|
|
create table #tmp (ColA varchar(2),ColB varchar(100))
insert into #tmp
select '1', '12.324.145'
union all
select '2', '425.152.643'
union all
select '3', '12'
select a.ColA,t.c.value('.','varchar(100)') as col2 from (select cola,cast('<t>'+replace(colB,'.','<t>')+'' as xml) as c2 from #tmp) a
cross apply c2.nodes('/t') t(c)
modified 13-Mar-15 4:04am.
|
|
|
|
|
Our database access code is DAO which has worked admirably for many years, making full use of the MFC DAO classes (CDaoRecordset, CDaoDatabase, CDaoFieldExchange etc.) to access mdb files.
Now we need a 64-bit build and support for .accdb, does anyone have any advice on the quickest and easiest way to convert all my code? (to ADO? or something else?)
|
|
|
|
|
If you have a proper Data Access Layer it should be as simple as replacing it.
|
|
|
|
|
Surely you have just one class that does all your database access! If not you now know the reason why it is so important.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
You guys are so funny!  It's as if my code wasn't more than a decade old or something, and had been updated as technology progressed... 
I have a single DLL of database access code comprising several classes derived from CDAORecordset and a database class containing a CDaoDatabase pointer.
I think most of it will translate to CDatabase and CRecordset fairly directly.
|
|
|
|
|
Yah sarcasm sucks sometimes but you have seen the quality of some questions...
I have never heard of a tool to convert a custom DLL to a different data transport layer. It should be a trivial matter to rewrite the DLL, ours has about 6 methods for accessing the database.
It does sound like you are using your DLL for a lot more than just accessing the database, traversing and manipulating datasets I suspect.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Ahh, I am so used to using collections I have forgotten what it is like to access the database directly. I would suggest you may want to take a look at your architecture, I presume you are using winforms and binding the datasets to the controls which is going to entail a lot of work to change.
Never underestimate the power of human stupidity
RAH
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
