[SQL] Searching for results with an unknown amount of data
Hi, I've got two tables, A and B, the first one containing columns A_ID | info where A_ID is primary, so that this table stores various information about an object, and the second containing columns A_ID | property where property is an integer referring to a particular property that an object may possess. I've seperated these into two tables so that an object may have several (ie an unknown number of) properties. Now, I want to find all objects that have at most properties 1,2,3, say (so something with (1,2) is okay, as is (1,2,3)). I can't see a way to do this -- can anyone help? Also, what if I want to find all the objects possessing properties 4,5,6, as well as possibly other things? I've done it with nested SELECTs (constructed programmatically), but I think this is quite slow and not a very pleasing solution. Obviously, both these things will need to be done for an arbitrary list of integers. Thanks, DL ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] joins again
Hi, You may find examples in the section 'Joining Tables' of Bruce Momjian's marvelous work. http://www.postgresql.org/docs/aw_pgsql_book/node63.html I hope this helps. Regards Erol CB wrote: > Hi, > > Can someone tell me which (if any) joins are supported with 7? It > appears the answer is 'none' but I want to make sure. Since I think the > answer is 'none' can someone tell me how you emulate inner and outer > joins using the where clause (I need an example)? Thanks! > > Craig
Re: Antw: [SQL] LEFT JOIN
Antti Linno wrote: > > > I think, the following select will solve your problem > > > > select first.id > > from first > > except > > second.id_first > > from second; > > > Nay, I got parse error. 'select' is missing after 'except'. select first.id from first except select second.id_first from second; erol > Antti
Re: [SQL] Finding entries not in table..differnce?
If I understand you exactly, you may use except: select distinct * from prodlang except select distinct * from prodlang2 gives you the records which exist in prodlang and do not exist in prodlang2. So you get all the records in prodlang which are newly inserted or updated. regards erol Zot O'Connor wrote: > > I need to write a quick function that tells me all of the entriles in > table that are not in table2. > > The tables are copies of each other, but 1 has been updated. I know > this is easy, but I am running on little sleep :) > > I want to due something like > > select prodlang.prodlsku from prodland,prodlang2 WHERE prodlang.prodlsku > != prodlang2.prodlsku > > But of course I would get every record, several times since at some > point the sku does not equal another sku. > > I tried !!= (NOT IN) but it did not like that at all, since sku is a > char field. > > I am running an older version of postgress on this server, I do not know > if that is important. > > -- > Zot O'Connor > > http://www.ZotConsulting.com > http://www.WhiteKnightHackers.com
