There are two tables A and B. You are retreiving data from both tables where all rows from B table and only matching rows from A table should be displayed. Which of the following types of joins will you apply between A and B tables? - Inner join - Left outer join - Right outer join - Self join ...
Hi everyone, Can anyone give some illustration on how to write a program for reduce-side join? The reduce-side join provided by Hadoop is sort-merge join. How can I write a hash-join algorithm for reduce-side join? Best ...
Hi Everyone, I am trying to use the Hadoop's Map-side join using CompositeInputFormat but I get an error: "Unmatched ')'". I guess there may be a problem in the format of my input file. I have formatted the input files manually in such a way that keys are in sorted order in both the input files. Is this correct or do I have to pass the ...
I'm using SQL Server 2008 and I have 3 tables, x, y and z. y exists to create a many-to-many relationship between x and z. x y z -- -- -- id xid id zid sort All of the above fields are int. I want to find the best-performing method (excluding denormalising) of finding the z with the highest sort for ...
Ok this is weird, I know. I just want ideas about how to deal with it. I want to do a join --- --- A B --- --- id id idB --- --- A standard join would be : SELECT * FROM B JOIN A ON A.idB = B.id WHERE A.id = 3 But here, my idB is not an int foreign key, it is PHP serialized. For example, instead of being 5, it is "i...
Hi, I use Zend_Db_Select to perform a query with a Join. I end up with the following SQL query : SELECT `Utilisateur`.*, `Ressource`.*, `Acl_Cache`.*, `Role`.*, `UtilisateurRole`.* FROM `Utilisateur` INNER JOIN `Ressource` ON Ressource.idJointure = Utilisateur.id INNER JOIN `Acl_Cache` ON Acl_Cache.idRessource = Ressource.id INNER JOIN...
Hello, I'm having problems with design correct SQL query. I'm tired like a hell today, been working over 12 horus (deadline soon) and cannot find issue... Tables: buddies | userid | buddyid users | id | username Now, what I'd like to do: Query table buddies for all user friends (when ID = userid OR ID = buddyid). Having no problems ...
I have a users table, and a view table which lists some user ids... They look something like this: Users: User_ID | Name | Age | ... 555 John Doe 35 556 Jane Doe 24 557 John Smith 18 View_Table User_ID 555 557 Now, when I do run a query to retrieve a user: SELECT User...
Hi! I'm quite a beginner at this so please forgive me if this seems a bit easy for some of you. So, I have this query here: SELECT code.id AS codeid, code.title AS codetitle, code.summary AS codesummary, code.author AS codeauthor, code.rating AS rating, code.date, code_tags.*, tags.*, ...
I have three tables, we'll call them table1, table2, and table3. Lets say each table has a user id column and a date column. What i'd like to do is to be able to count the number of rows with a specific user id in table1, and sum it with the number of rows with that user id in tables 2 and 3. I'd then like to take that first query I d...
I'm changing my database structure and I want to do the following thing: I have one table for registered users that holds almost all the information about my site users. There is also one other table that holds information about the amount of points each user have. It has only two columns: user id and points. I want to move the points co...
Hi There, I have two tables, timetable and lesson_booking these are linked via timetable_id. timetable represents timetable entries for a given day (mon, tue etc) that can be selected and used to make a booking for a client in lesson_booking What I would like to do is get a list of timetable entries that have no bookings associated w...
Hi all, i need to select A list showing the customer id, title, first name and surname of each customer who has hired a car, sorted alphabetically by customer surname together with a count of the number of bookings each of them has placed. I've done the first part but not sure where to put count for the number of bookings placed. Here...
I have a table id_user, hour, medition 1 0 100 1 1 101 1 14 102 2 5 108 2 17 103 How I can complete the inexistent hours with a 0 value for any user? example 1 0 100 1 1 101 1 2 0 1 3 0 ... ...
In our project, we often end up writing complex joins over like 3 tables. Do we gain any performance advantages by using views or are they only for making the lives of query writers easier? In case it matters, we use MySQL. If any advantages are thus achieved (other than simpler queries of course) please illuminate. ...
Following join is supposed to retrieve user info along with their messages for users with a certain status: SELECT * FROM user, message WHERE message.user_id=user.id AND user.status=1 The problem is that all rows about a certain user in the result set contain redundant columns that repeat the same data about that user (those fields re...
Is there a better way to write this SQL query? SELECT *, (SELECT TOP 1 columnB FROM mytable WHERE mytable.columnC = T1.columnC ORDER BY columnD) as firstRecordOfColumnB FROM (SELECT * FROM mytable WHERE columnA = 'apple') as T1 Notice that columnC is not the primary key. ...
I have a user table (User) and 3 tutorial tables (Text, Video and Other). Each tutorial has the columns rating_positive and rating_negative and is linked to a user (id). I want to select the 10 users with the most tutorials and the sum of positive/negative ratings of their tutorials. I tried the following query but it does not work. I...
I'm trying to do a join between tables 1 and 2 which have a 1 to many relationship. table1 has the following fields createdate contact tkey (surrogate key) table2 has the following fields tkey (primary key) status userfld1 description I want to show all items in table2 with their corresponding items in table1 grouped by...
I've done something similar to this with hbm files but so far I haven't found an equivalent using annotations. What I'd like to be able to do is create an association across a join table where the join table contains an order colum for the association (sorry for any incorrect terminology here). At the very simplest, the tables might lo...