Make two query in too one

[Bert Boye]

I have two SQL query that I would like to make in to one, if possible

Here is the first one: (Query4)
SELECT rooms.DESCRIPTIO, rooms.ID, bookings1.DATE
FROM bookings1 INNER JOIN rooms ON bookings1.ROOMID = rooms.ID
WHERE (((bookings1.DATE) Between #4/3/2004# And #4/9/2004#));

And the second one:
SELECT rooms.ID, rooms.DESCRIPTIO, Query4.ROOMID
FROM rooms LEFT JOIN Query4 ON rooms.ID = Query4.ROOMID
WHERE (((Query4.ROOMID) Is Null));

Is it possible to make one query of this two?
With the same result.

[David Portas]

> Is it possible to make one query of this two?

With the same result.

Please show an example of the result you want. The first query has columns
(descriptio, id, date) and the second one has (id, descriptio, roomid).
Possibly a UNION is what you are looking for but this requires that the
columns are of compatible data-types.

Is this a SQLServer question? The date format you used isn't valid in TSQL.

It would help make your requirement clearer if you could post DDL and sample
data.
http://www.aspfaq.com/5006

--
David Portas
SQL Server MVP
--

[Bert Boye]

Please excuse me, I think I posted in a wrong group :)

The first query gives me a list of booked rooms and the second on a
list of available room.

Let's say I have 4 rooms:
1. Standard
2. Deluxe
3. Super deluxe
4. Suit

Query 1 give me the booked date from 4/3/2004 to 4/9/2004.
RoomID Date
1 4/1/2004
1 4/2/2004
1 4/3/2004
1 4/4/2004
1 4/5/2004
2 4/4/2004
2 4/5/2004

Query 2 shows available room.
3 Deluxe
4 Suit

I have two tables.
1. Bookings1 with fields roomid and date booked
2. Rooms with two fields RoomID and description

Here is the first one: (Query4)
SELECT rooms.DESCRIPTIO, rooms. ROOMID, bookings1.DATE
FROM bookings1 INNER JOIN rooms ON bookings1.ROOMID = rooms. ROOMID
WHERE (((bookings1.DATE) Between #4/3/2004# And #4/9/2004#));

And the second one:
SELECT rooms.ID, rooms.DESCRIPTIO, Query4.ROOMID
FROM rooms LEFT JOIN Query4 ON rooms. ROOMID = Query4.ROOMID
WHERE (((Query4.ROOMID) Is Null));

I really appreciate any help with this and sorry again for posting in
the wrong group;)

Bert

"David Portas" <RE****************************@acm.org> wrote in message news:<Ke********************@giganews.com>...

Is it possible to make one query of this two?
With the same result.

Please show an example of the result you want. The first query has columns
(descriptio, id, date) and the second one has (id, descriptio, roomid).
Possibly a UNION is what you are looking for but this requires that the
columns are of compatible data-types.

Is this a SQLServer question? The date format you used isn't valid in TSQL.

It would help make your requirement clearer if you could post DDL and sample
data.
http://www.aspfaq.com/5006

[Erland Sommarskog]

Bert Boye (sa***@a-o.biz) writes:

I have two tables.
1. Bookings1 with fields roomid and date booked
2. Rooms with two fields RoomID and description

Here is the first one: (Query4)
SELECT rooms.DESCRIPTIO, rooms. ROOMID, bookings1.DATE
FROM bookings1 INNER JOIN rooms ON bookings1.ROOMID = rooms. ROOMID
WHERE (((bookings1.DATE) Between #4/3/2004# And #4/9/2004#));

And the second one:
SELECT rooms.ID, rooms.DESCRIPTIO, Query4.ROOMID
FROM rooms LEFT JOIN Query4 ON rooms. ROOMID = Query4.ROOMID
WHERE (((Query4.ROOMID) Is Null));

I really appreciate any help with this and sorry again for posting in
the wrong group;)

A standard advice when we don't understand what is being asked for
(and I don't in this case) is that you should post:

o CREATE TABLE statements for the tables involved.
o INSERT statements with sample data.
o The desired output from the sample data.

You are saying that you want to make one query out of two. However, I
have no idea on how you want to combine them.

Of course, if you are using Access, you should post to
comp.databases.ms-access instead. However, the advice about CREATE
TABLE etc still applies.

--
Erland Sommarskog, SQL Server MVP, so****@algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp