|
Let's see what your query looks like for the rooms that have been reserved. I suspect that you simply need to negate the criterion for choosing those that have been reserved.
The difficult we do right away...
...the impossible takes slightly longer.
|
|
|
|
|
SELECT RoomNo
FROM RESERVATION
ORDER BY HotelNo
|
|
|
|
|
You could try a subquery:
SELECT HotelNo, RoomNo FROM Room WHERE RoomNo NOT IN (SELECT RoomNo FROM Reservation) ORDER BY HotelNo, RoomNo
The difficult we do right away...
...the impossible takes slightly longer.
|
|
|
|
|
No data is retrieved back. It's blank.
|
|
|
|
|
My SQL may be a bit rusty. But I don't know what your data looks like either.
Let's see if anyone else has an idea.
The difficult we do right away...
...the impossible takes slightly longer.
|
|
|
|
|
It worked. I was trying to incorporate something else into the statement.
|
|
|
|
|
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.
|
|
|
|