Simplyfying sql statement

Firkrag

I wrote a following statement:

SELECT b.buildingId, b.buildingName, (

SELECT cb.buildinglevel
FROM citybuildings cb
WHERE cb.cityId =$cityId
AND b.buildingId = cb.buildingId
) AS buildingLevel
FROM buildings b
WHERE (

SELECT count( * ) 
FROM buildingsrequirements br
WHERE br.buildingId = b.buildingId
AND br.bui_buildingId NOT 
IN (

SELECT cb.buildingId
FROM citybuildings cb
WHERE cb.cityId =$cityId
)
) =0
ORDER BY b.buildingName

Tt works fine, however in my opinion it is a bit too complicated and i am looking for a way to make it simplier, and I have no idea. For example it uses three subqueries and also I have to use variable $cityId two times, so it gets difficult to maintain and understand.

The purpose of this statement is to get a list of all buildings in the given city with their level, together with a list of buildings that can be build in the city and all buildings required for them have already been build. The level for those would be null.

That is the part of database structure:

CREATE TABLE `buildings` (
  `buildingId` int(3) NOT NULL auto_increment,
  `buildingName` varchar(24) NOT NULL,
  PRIMARY KEY  (`buildingId`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin2 AUTO_INCREMENT=14 ;

-- --------------------------------------------------------


CREATE TABLE `buildingsrequirements` (
  `buildingId` int(3) NOT NULL,
  `bui_buildingId` int(3) NOT NULL,
  KEY `FK_Relationship_2` (`bui_buildingId`),
  KEY `FK_Relationship_3` (`buildingId`)
) ENGINE=InnoDB DEFAULT CHARSET=latin2;

-- --------------------------------------------------------



CREATE TABLE `citybuildings` (
  `cityId` int(7) NOT NULL,
  `buildingId` int(3) NOT NULL,
  `buildingLevel` tinyint(4) NOT NULL,
  KEY `FK_Relationship_4` (`cityId`),
  KEY `FK_Relationship_5` (`buildingId`)
) ENGINE=InnoDB DEFAULT CHARSET=latin2;

tomhath

Kind of hard to follow your logic. This is a first guess:

SELECT 	b.buildingId, b.buildingName, cb.buildingLevel
FROM buildings b
INNER JOIN citybuildings cb
	ON  b.buildingId = cb.buildingId
WHERE cb.cityId = $cityId
	AND NOT EXISTS (
		SELECT 1
		FROM buildingsrequirements br
		WHERE br.buildingId = cb.buildingId
		  AND br.bui_buildingId <> cb.buildingId
		)
ORDER BY b.buildingName

Firkrag

Thats not exacly what I would like. I checked your statement and it returns this:

buildingId buildingName buildingLevel 
1	building1	3

and it should be sth like this:

buildingId buildingName buildingLevel 
8	building8	NULL
4	building4	NULL
1	building1	3
5	building5	NULL
2	building2	4
11	building11	NULL
10	building10	NULL

Any ideas?

cgraz

try changing INNER JOIN to LEFT JOIN in tomhath's SQL.

Firkrag

left join doesn't change anything

tomhath

LEFT JOIN is what you want, but the compare to $cityid also needs to be part of the join, not the WHERE clause.

SELECT     b.buildingId, b.buildingName, cb.buildingLevel
FROM buildings b
LEFT JOIN citybuildings cb
    ON  b.buildingId = cb.buildingId
    AND cb.cityId = $cityId
WHERE NOT EXISTS (
        SELECT 1
        FROM buildingsrequirements br
        WHERE br.buildingId = cb.buildingId
          AND br.bui_buildingId <> cb.buildingId
        )
ORDER BY b.buildingName

Firkrag

Thanks, it is what I was looking for.