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.name as cli_name, buyer.surname, obj.* FROM buyer JOIN (SELECT bid.buyer_id, bid.min_area, building.area, building.price, bid.max_price, building.city_id, bid.city_id, building.name FROM bid INNER JOIN building ON bid.city_id = building.city_id AND building.price <= bid.max_price AND building.area >= bid.min_area WHERE building.name = "Object 101" AND NOT EXISTS (SELECT bid_material.material_id, building_material.material_id FROM building_material LEFT JOIN bid_material ON building_material.material_id = bid_material.material_id WHERE bid_material.bid_id = bid.id OR building_material.building_id = building.id EXCEPT SELECT bid_material.material_id, building_material.material_id FROM building_material JOIN bid_material ON building_material.material_id = bid_material.material_id WHERE bid_material.bid_id = bid.id OR building_material.building_id = building.id ) AND NOT EXISTS ( SELECT bid_material.material_id, building_material.material_id FROM bid_material LEFT JOIN building_material ON building_material.material_id = bid_material.material_id WHERE bid_material.bid_id = bid.id OR building_material.building_id = building.id EXCEPT SELECT bid_material.material_id, building_material.material_id FROM building_material JOIN bid_material ON building_material.material_id = bid_material.material_id WHERE bid_material.bid_id = bid.id OR building_material.building_id = building.id ) ) obj ON buyer.id = obj.buyer_id;