Select query Where clause from another table

[mrt003003]

Hi there,

 

I was wonderin if its possible to create a select query from one table thats Where clause is determinded from another table. For example my 2 tables are: Structures and Planetstructures. Structures holds the structure details and Planetstructures holds the planet details and the structure detals etc.. When there are 3 of any planetstructure records of the same type, i want to select the data in the structures table leaving out the structure that coresponds to the planetstructure with more than 3 records of the same type.

 

SELECT s.StructureName, s.Maintenance, g.PlanetStructureID, g.ConstructionTime, g.PlanetID FROM structures s
LEFT JOIN planetstructures g ON (s.StructureID = g.StructureID)
WHERE p.PlanetID = %s[b] AND Totalrows of any one type < 3

 

I hope ive made sense. Any ideas on how i can achieve this please??

 

Thanks

[requinix]

I don't know about what you said, but at least the SQL makes sense to me.

 

You can use an IN or, probably more appropriately, a NOT IN condition and subquery.

...AND s.StructureID NOT IN (SELECT StructureID FROM planetstructures GROUP BY StructureID,  HAVING COUNT() >= 3)

 

It can be simpler than that if all planetstructures which belong to the same structure and have the same type also have the same ConstructionTime and PlanetID: just move the GROUP BY and HAVING into the main query and forget the NOT IN condition and subquery.

[Andy-H]

Couldn't you just do:

 

 

SELECT COUNT(<type>) AS TotalRows, s.StructureName, s.Maintenance, g.PlanetStructureID, g.ConstructionTime, g.PlanetID FROM structures s
LEFT JOIN planetstructures g USING (StructureID)
WHERE p.PlanetID = %s GROUP BY <type> HAVING TotalRows < 3

 

 

 

//EDIT: Used PHP instead of code lol

[fenway]

LEFT JOIN without "IS NULL" isn't equivalent to "NOT IN".

[awjudd]

By having a LEFT JOIN and then following that up with a WHERE clause using where you are requiring a value of that field, you are removing the use for the LEFT JOIN.

 

~juddster