Transforming simple sql to "access sql"

Question

i need some help to transform sql code to sql access code,my code is:

SELECT `HOTEL`.`NAME_H`,`ROOM`.`NUMBER_R`,`ROOM`.`FLOOR_R`
FROM HOTEL, ROOM
WHERE `HOTEL`.`CODE_H`=`ROOM`.`CODE_H`
AND `HOTEL`.`TOWN_H`=`Athens`
AND (`HOTEL`.`CODE_H`,`ROOM`.`NUMBER_R`)
NOT IN (SELECT `CODE_H`,`NUMBER_R` FROM RESERVATION)

its not running in access,I don’t know how to use not in in ms,any ideas? I have a problem and with another one,my code is:

SELECT `ΟFFER`.`CODE_O`,`HOTEL`.`NAME_H`,`ROOMTYPE`.`NAME_RT`,MIN(`OFFER`.`PRICE_O`)
FROM OFFER,HOTEL,ROOMTYPE
WHERE `OFFER`.`CODE_H`=`HOTEL`.`CODE_H`
AND `OFFER`.`CODE_RT`=`ROOMTYPE`.`CODE_RT`
GROUP BY `CODE_O`

How about you give the full error messages you get? And add which position in the Access SQL is marked as error. — Andre
– Andre, Commented May 7, 2016 at 18:12
NOT IN is valid in Access SQL but you must align it to one column not multiple. As for second query, for group by queries all non-aggregated columns must be referenced in GROUP BY clause. Sadly, MySQL allows such a query where it fails for most RDMS's depending on only_full_group_by setting. — Parfait
– Parfait, Commented May 7, 2016 at 22:57
Which RDBMS are you using originally? Please add a tag to specify whether you're using mysql, postgresql, sql-server, oracle or db2 - or something else entirely. — marc_s
– marc_s, Commented May 13, 2016 at 18:46

Gordon Linoff · Accepted Answer · 2016-05-09 01:33:34Z

1

This should work:

SELECT h.NAME_H, r.NUMBER_R, r.FLOOR_R
FROM HOTEL as h INNER JOIN
     ROOM as r
     ON h.code_h = r.code_h
WHERE h.TOWN_H = "Athens" AND
      NOT EXISTS (SELECT 1
                  FROM RESERVATION as r
                  WHERE h.CODE_H = r.CODE_H AND
                        h.NUMBER_R = r.NUMBER_R
                 );

In fact, this should work in most databases.

edited May 9, 2016 at 1:33

answered May 7, 2016 at 16:21

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Nick.P. Over a year ago

its not working in ms access 2007,it asks me for a parameter.

Nick.P. Over a year ago

Any ideas about the second?

Gordon Linoff Over a year ago

@Nick.P. . . . The backticks were an oversight. The single quotes were another problem . . . MS Access uses double quotes for strings.

Collectives™ on Stack Overflow

Transforming simple sql to "access sql"

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related