Thread: COUNT(*) to find records which have a certain number of dependencies ?
Hello, I apologize in advance for this garbled message but I've been banging my head against a brick-wall for a while and I just can't figure how to do the following: I have 3 tables BRAND,MODEL,TYPE which are related to each other: BRAND ===== BRAND_PK BRAND_NAME MODEL ===== MODEL_PK MODEL_NAME BRAND_FK (NOT NULL, references BRAND_PK) TYPE ==== TYPE_PK TYPE_NAME MODEL_FK (NOT NULL, references MODEL_PK) ------------------------------------ I want to select only those BRAND/MODEL combinations, where the MODEL has more than one TYPE, but only where one of those has TYPE_NAME='xyz'. I am not interested in MODELs with multiple TYPEs where none of them are called 'xyz'. -- Regards/Gruß, Tarlika Elisabeth Schmitz
T E Schmitz <mailreg@numerixtechnology.de> writes: > I want to select only those BRAND/MODEL combinations, where the MODEL has more > than one TYPE, but only where one of those has TYPE_NAME='xyz'. > I am not interested in MODELs with multiple TYPEs where none of them are called > 'xyz'. There are lots of approaches to this with various pros and cons. The simplest one off the top of my head: select * from brand join model on (brand_pk = brand_fk)where exists (select 1 from type where model_fk = model_pk and type_name= 'xyz') and (select count(*) from type where model_fk = model_pk) > 1 You could try to be clever about avoiding the redundant access to the type table: select * from brand join model on (brand_pk = brand_fk)where (select count(*) from type where model_fk= model_pk having sum(case when type = 'xyz' then 1 else 0 end) >= 1 ) > 1 I'm haven't tested that, it might need some tweaking. In any case I don't think it's worth the added complexity, assuming you have indexes on type. I'm not even sure it would run faster. You could try to be really clever about it by turning the whole thing into a join: select * from brand join model on (brand_pk = brand_fk) join (select model_fk from type group by model_fk having sum(case when type = 'xyz' then 1 else 0 end) >= 1 and count(*) > 1 ) on (model_fk = model_pk) This would let the planner have a more plans to choose from and might be a big win if there are lots of brands and models but few that satisfy the criteria you're looking for. -- greg
Hello Greg, You have given me plenty of food for thought. Thank you for taking the time. Currently, the tables have such few records (350, 900, 1000) that performance does not come into it, particularly seeing as this was only needed for a one-shot report. However, I have stached your examples away for future reference. I was feeling a bit guilty about posting such a trivial question. I can cobble together some straightforward SQL but I could really do with a source of more complex SQL examples. If you know of any links - that would great and save the list from more such questions ;-) I am correcting a couple of typos below in case someone tries these examples out. Greg Stark wrote: > select * > from brand > join model on (brand_pk = brand_fk) > where (select count(*) > from type > where model_fk = model_pk > having sum(case when type = 'xyz' then 1 else 0 end) >= 1 > ) > 1 ... having sum(case when type_name = 'xyz' ... > select * > from brand > join model on (brand_pk = brand_fk) > join (select model_fk > from type > group by model_fk > having sum(case when type = 'xyz' then 1 else 0 end) >= 1 > and count(*) > 1 > ) on (model_fk = model_pk) > ) as somealias on (model_fk = model_pk) (subquery in FROM must have an alias) -- Regards/Gruß, Tarlika
T E Schmitz <mailreg@numerixtechnology.de> writes:
> ) as somealias on (model_fk = model_pk)
>
> (subquery in FROM must have an alias)
ARGH! This is one of the most annoying things about postgres! It bites me all
the time. Obviously it's totally insignificant since it's easy for my to just
throw an "AS x" on the end of it. But damn.
I see there's a comment foreseeing some annoyance value for this in the
source:
/* * The SQL spec does not permit a subselect * (<derived_table>) without an alias clause,
* so we don't either. This avoids the problem * of needing to invent a unique refname for it.
*That could be surmounted if there's sufficient * popular demand, but for now let's just implement *
thespec and see if anyone complains. * However, it does seem like a good idea to emit * an error
messagethat's better than "syntax error". */
So where can I officially register my complaint? :)
--
greg
On Mon, 20 Sep 2004, T E Schmitz wrote: > I was feeling a bit guilty about posting such a trivial question. I can > cobble together some straightforward SQL but I could really do with a > source of more complex SQL examples. > If you know of any links - that would great and save the list from more > such questions ;-) SQL for Smarties has some more complicated examples and topics for "advanced" type queries. I can't say it's exhaustive, but I found it a good bridge by hinting at what is really possible. I also found that a good way to improve is to try to write every complicated query by using all the different ways I can think of, like: - UNION (ALL) - SUB-SELECT - LEFT OUTER JOINS - HAVING etc. Here is the link for SQL for Smarties: http://www.amazon.com/exec/obidos/tg/detail/-/1558605762/002-2222957-7220055?v=glance The bad thing about the book is that it is sort of SQL agnostic, so some of the examples would be sub-optimal on postgresql, or may not even work. I would like to hear about other sources too.
Greg Stark wrote: > T E Schmitz <mailreg@numerixtechnology.de> writes: >>) as somealias on (model_fk = model_pk) >>(subquery in FROM must have an alias) > > ARGH! This is one of the most annoying things about postgres! It bites me all > the time. Obviously it's totally insignificant since it's easy for my to just > throw an "AS x" on the end of it. But damn. > > So where can I officially register my complaint? :) Hope you don't mind an opinion from someone who looks at this from the underside ... Trying to give the parser a better chance of confusing you? Having the tag only totally insignificant if you want to have a bunch of special validation cases, where if there is only ONE anonymous pseudotable, and no ambiguity is possible. If all it does is give you an annoying but understandable error message, might you care to consider the cryptic error messagesyou get from systems that try to 'do what you mean, not what you say' for such special cases ... and thereby turn a typo into an error twenty lines further down the page. BTW, the "as" is optional, but I always suggest that people use it explicitly. Why? Because without it, you get another silly error message or even a runtime error when what you did was omit a comma. For example select salary name from Employee returns one column (a dollar figure called "name"). (Yes, I know it's harder to cook up an example when the comma is missing between tables in the FROM list; just wantedit to be obvious) Okay, apologies for what may sound like a rant. I've just been wrangling with an interpreter that tries WAY too hard to make something executable out of what you tell it ... even if that's really nothing like your intent.
Mischa Sandberg <ischamay.andbergsay@activestateway.com> writes: > Hope you don't mind an opinion from someone who looks at this from the > underside ... > > Trying to give the parser a better chance of confusing you? Ok, I understand the basic idea that a parser muddles along too long before reporting an error makes it harder to track down the original error. However this isn't such a case. I can't think of any way I could accidentally introduce an extra comma that would lead to a valid looking alias and an error much further along. You're talking about something like: select * from a, as x which would still produce a syntax error directly after the erroneous comma. Which is right where you want the error to happen. > Having the tag only totally insignificant if you want to have a bunch of > special validation cases, where if there is only ONE anonymous pseudotable, > and no ambiguity is possible. There's no additional ambiguity. There can't be since all the user would do to make the parser happy is go and specify aliases, not necessarily use them. If the user didn't feel the need to put aliases in initially presumably it was because he wasn't using them because he didn't need them. Can you really say queries like this are dangerous: select a_id,b_id from (select a_id from a where xyz=?), (select b_id from b where xyz=?) or queries like select a_id,b_id from (select a_id from a where xyz=?) as x, (select b_id from b where xyz=?) as x are any clearer or less ambiguous? > If all it does is give you an annoying but understandable error message The reason it's annoying is because the database is saying, "I know perfectly well what you're doing: I parsed the subquery fine, but I'm going to refuse to run it because you didn't say the magic word." But when I add in "as x" after every subquery even though it's utterly meaningless to the rest of the query, then postgres is perfectly happy to cooperate. > BTW, the "as" is optional, but I always suggest that people use it explicitly. > Why? Because without it, you get another silly error message or even a runtime > error when what you did was omit a comma. For example > > select salary name from Employee I understand what you mean but I don't understand how always using the "as" helps you here. You'll still get this error even if you were always using AS when you intended. There's no option to make AS mandatory. Incidentally, I also always use AS in both column and table aliases (when specifying them) but for a different reason. I could never keep straight which of Oracle and MSSQL required AS for columns and which required it for tables. IIRC they're exactly reversed. However as best as I recall they both allowed subqueries without any aliases at all. -- greg