I was reading the great tagging article by Nitin Borwankar and he started me thinking of the ways to implement differnet levels of searches using two tables. tags { id, tag } post_tags { id user_id post_id tag_id } I started with the simple example of T(U(i)) which means all tags of all users that have an item i. I was ab...
StackOverflow lets you search for posts by tags, and lets you filter by an intersection of tags, e.g. ruby x mysql x tags. But typically it's inefficient to retrieve such lists from MySQL using mulitple joins on the taggings. What's a more performant way to implement filter-by-multiple tag queries? Is there a good NoSQL approach to thi...
Hi there, given the two tables and values: Tables People- Columns: [ID]PK Name Field - Columns: [ID FieldName]PK FieldValue Values People ID Name 1 Mary 2 John 3 Tony Field ID FieldName FieldValue 1 Age 20 1 Country USA 2 Age 21 2 Country USA 3 Age 20 3 Country USA I would like a query th...
Where are Cartesian Joins used in real life? Can some one please give examples of such a Join in any SQL database. ...
My LINQ query is not producing the expected output below. Basically, it's the sum of table3.cost corresponding to table2.code and table2.class categorized by table1.alias and ordered by table1.priority. I added two DataRelation's to the DataSet: ds.Relations.Add("Table1Table2", ds.Tables[1].Columns("ID"), ds.Tables[2].Columns("ParentI...
I have a join in a oracle query which looks like: FROM eiv.table1 eiv.table2 b WHERE a.state_cd = b.state_code(+) what does the (+) towards the end mean? With this query I have noticed that sometimes I am getting an empty space when records do not match in tables. Is this a left outer join or ri...
I'm trying to create a record within a join table from the action of a button. To explain, I would have an events model and would like to track selected events from each user. I used the HABTM relationship since I dont really need any extra fields. User.rb => has_to_and_belongs_to_many :events Event.rb => has_to_and_belongs_to_many :u...
I am trying to figure out how to create ActiveRecord models with associations that can yield the same results as this SQL query: SELECT login, first_name, last_name, email_address FROM accounts INNER JOIN people ON person.id = accounts.person_id INNER JOIN email_address_people ON person.id = email_address_people.person_id INNER JOIN ...
I am new to Databases. I came across a peculiar problem with two tables. Please let me know the solution. Please fnd the scenario below a ProductCentre table prdcntrId (primary key), prdcntrname a ApplicationType table apptypeid (primary key) prdcntreid(foreign key to ProductCentre ) apptypname ProductCentre table || ...
Hi, I have a join which deletes rows that match another table but the joining fields have to be a large varchar (250 chars). I know this isn't ideal but I can't think of a better way. Here's my query: DELETE P FROM dbo.FeedPhotos AS P INNER JOIN dbo.ListingPhotos AS P1 ON P.photo = P1.feedImage INNER JOIN dbo.Listings AS L ON P.account...
I have a database two tables and a linking table that I need a JOIN query for: Here are my Tables: family (userid (int), loginName, etc) member (memberid (int), loginName(this links member to a family),name, etc) Linking Table: user2member (userid,memberid)...would both be foreign keys? I want to do two things: 1) Be able...
I have three tables specifying important columns below Users(Id, username) Groups(Id, groupname, creator) (creator is the creator of the group) Association(Id, UserId, GroupId) (entries in this table include which user is in which group) Association.UserID =Users.Id, Association.GroupId = Groups.id and also Groups.creator = Users.Id....
I am doing a couple of joins with a variable in the WHERE clause. I'm not sure if I am doing everything as efficiently as I could, or even using the best practices but my issue is that half my tables have data for when tableC.type=500, and the other half don't resulting in the entire query failing. SELECT tableA.value1 , tableB.value2, ...
In our application, we collect data on automotive engine performance -- basically source data on engine performance based on the engine type, the vehicle running it and the engine design. Currently, the basis for new row inserts is an engine on-off period; we monitor performance variables based on a change in engine state from active to...
I'm developing an rss feed reader that uses a bayesian filter to filter out boring blog posts. The Stream table is meant to act as a FIFO buffer from which the webapp will consume 'entries'. I use it to store the temporary relationship between entries, users and bayesian filter classifications. After a user marks an entry as read, it w...
Hello, I'm hoping someone can help me as I've been stuck on this problem for a few days now. Basically I'm trying to pull data from 3 tables in Oracle: 1) Orders Table 2) Vendor Table and 3) Master Data Table. Here's what the 3 tables look like: Table 1: BIZ_DOC2 (Orders table) OBJECTID (Unique key) UNIQUE_DOC_NAME (Document Name i.e...
I have a 3 tables that I'm trying to join and get distinct results. CREATE TABLE `car` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `name` varchar(255) NOT NULL DEFAULT '', PRIMARY KEY (`id`) ) ENGINE=InnoDB mysql> select * from car; +----+-------+ | id | name | +----+-------+ | 1 | acura | +----+-------+ CREATE TABL...
I have two tables, one is a table of forum threads. It has a last post date column. Another table has PostID, UserId, and DateViewed. I want to join these tables so I can compare DateViewed and LastPostDate for the current user. However, if they have never viewed the thread, there will not be a row in the 2nd table. This seems easy bu...
I am trying to write a query that connects 3 tables. The first table is info about each festival. The second table is the number of votes for each festival. The third table is reviews for each festival. I want to join all 3 tables so I get all the columns from table1, join table1 with table2 on the festivalid, but I also need to count ...