Re: Syntax of two simple queries

George de Bouter wrote: > > Hi, > > In my database is a table with a link to an entry in the table with > organizations and a link to the parent of that organization, in the same > organization table. > > Now I don't know what the query should look like... > > I tried: > > SELECT organization.name, organization.name > FROM organization, parent_organization > WHERE organization.id = parent_organization.id > AND organization.id = parent_organization.parent_id> > As this is a sort of hierarchical model, the top knot is a parent of its > own. So organizationX has organizationX as a parent, and this is the > only organization > displayed.... > > I understood that I could use "aliases" whic should make the query: > > SELECT a.organization.name, b.organization.name > FROM organization a, parent_organization b > WHERE a.id = b.id > AND a.id = b.parent_id Do I understand right: the "organization" table has a primary key "id" and a foreign key of itself "parent_id", which contains the organization.id of the parent organization? This can form a hierarchy of several levels. I think you have misunderstood aliases. You query should look more like this: SELECT org.name, par.name as parent_name FROM organization org, organization par WHERE org.parent_id = par.id If you want several levels, changing alias names and adding outer joins for generality: SELECT o1.name as name1, o2.name as name2, o3.name as name3 FROM organization o1, outer organization o2, outer organization o3 WHERE o1.parent_id = o2.id AND o2.parent_id = o3.id The outer join ensures that you will get some answer even if the organisation has no parent. > > I think it's a very simple question, but it's the SQL knowledge I'm > missing.... > > Another question is: > > I try to do the following query (which I thought is plain SQL), but it > results in an error: > > SELECT person_id > FROM job_function > GROUP BY person_id > HAVING COUNT(person_id) > 1 Try this: SELECT person_id FROM job_function GROUP BY person_id HAVING COUNT(*) > 1 The COUNT(*) will count the rows in each group, which contain only the person_id column. > > (IN N.L.: Give me all person_id's of those rows in the table that have > this person_id more than once, which means, "Give me all persons with > more than one function"). > > The database complains that the syntax is not OK. It isn't usefull, but > when I put a distinct inside the COUNT, the answer of course is wrong, > but the syntax seems right.... It seems the problem is a COUNT(field) is > not supported, whereas a COUNT(*) is supported. > > The database used is a very stripped Informix that was delivered with > the Netscape Enterprise 2.0 webserver, with LiveWire Pro.... > > Thanks, > > George You might find it helpful to download some of the manuals from the Informix web site: http://www.informix.com/ -- Peter Lancashire Information Systems Specialist, Bayer plc Eastern Way, Bury St Edmunds, Suffolk, IP32 7AH, UK Tel: +44-1635-562258, Fax: +44-1635-562281 --- If all else fails, read the instructions and the release notes. Join Infuse, the UK Informix User Group at http://www.infuse.org.uk/ ---