SQLize | PHPize | SQLtest

BEGIN TRANSACTION; CREATE TABLE IF NOT EXISTS "bid_material" ( "id" INTEGER NOT NULL PRIMARY KEY UNIQUE, "bid_id" INTEGER NOT NULL, "material_id" INTEGER NOT NULL ); CREATE TABLE IF NOT EXISTS "buyer" ( "id" INTEGER NOT NULL PRIMARY KEY UNIQUE, "name" TEXT NOT NULL, "surname" TEXT NOT NULL ); CREATE TABLE IF NOT EXISTS "bid" ( "id" INTEGER NOT NULL PRIMARY KEY UNIQUE, "min_area" INTEGER NOT NULL, "max_price" INTEGER NOT NULL, "city_id" INTEGER NOT NULL, "buyer_id" INTEGER NOT NULL ); CREATE TABLE IF NOT EXISTS "building_material" ( "id" INTEGER NOT NULL PRIMARY KEY UNIQUE, "building_id" INTEGER NOT NULL, "material_id" INTEGER NOT NULL ); CREATE TABLE IF NOT EXISTS "building" ( "id" INTEGER NOT NULL PRIMARY KEY UNIQUE, "name" TEXT, "area" INTEGER NOT NULL, "price" INTEGER NOT NULL, "owner_id" INTEGER NOT NULL, "city_id" INTEGER NOT NULL ); CREATE TABLE IF NOT EXISTS "owner" ( "id" INTEGER NOT NULL PRIMARY KEY UNIQUE, "name" TEXT, "surname" TEXT ); CREATE TABLE IF NOT EXISTS "material" ( "id" INTEGER NOT NULL PRIMARY KEY UNIQUE, "name" TEXT ); CREATE TABLE IF NOT EXISTS "city" ( "id" INTEGER NOT NULL PRIMARY KEY UNIQUE, "name" TEXT ); COMMIT; SELECT buyer_id FROM bid JOIN building ON bid.city_id = building.city_id AND building.price <= bid.max_price AND building.area >= bid.min_area WHERE NOT EXIST (SELECT * FROM building_material FULL JOIN bid_material ON building_material.material_id = bid_material.material_id WHERE bid_material.bid_id = bid.id EXCEPT SELECT * FROM building_material JOIN bid_material ON building_material.material_id = bid_material.material_id WHERE bid_material.bid_id = bid.id )