I've got two tables (let's say "left" and "right" tables). The right table's got from none to several rows matching each row on the left table. I need to perform a query where I can join the two tables the same way a LEFT OUTER JOIN works, but getting only one row for each existing one in the left table. That row should be the one corre...
I am trying to search questions which have a given tag. How can you fix the following problem? Tables questions | tags -------------------|----------------- question_id | tag title | question_id was_sent_at_time | My code SELECT question_id, title FROM questions WHERE question_...
How can you select question_id and tags for a question when one of the tags is known? I am building a tag-search which searches questions by one tag such that the result shows the question and its tags including the one which was used in the search. I use the same tables as in this question. They are Tables questions | ...
Hello I have the following tables : TableA ID | SomeInt 1 55 1 66 2 77 TableB ID | OtherInt 1 ComputedBy Field 2 ComputedBy Field The computed by field needs to return the sum from tableA where TableB.ID = TableA.ID, but if I say: SELECT SUM(SomeInt) from TableA where ID = TableA.Id where the first ID would be ...
Let's say i have 2 tables Customer and Books. Table Books can have multiple rows that pertain to a row in Customer. Ex: customer John Doe has an id of 1000. Table Books has 2 rows with a member_id column with 1000 (John Doe). These 2 rows are identical EXCEPT one of the fields (book title) is empty. The other row has a value for a tit...
I have a simple domain class Licensee: @Entity @Table(name = "LICENSEE") @Cache(usage = CacheConcurrencyStrategy.READ_WRITE) public class Licensee implements Serializable, Comparable { private Integer id; private String orgName; /* ... */ private Set<Address> address = new HashSet<Address>(); @Id @Column(name ...
Maybe I'm going about it the wrong way. Here is what I'm trying to do and my problem. I have 3 tables. assets (a computer, network device, etc) ports (a port on the computer, network device, etc) port_connections (has a port_id_a and port_id_b field and links each port and therefor each asset together) It's really just a way to track v...
Hi all, I have 2 tables as follows: tags: id version name tag_links: id version tag_id (foreign key to tags.id) I need to write an SQL statement that returns how many times each tag_id occurs in tag_links table. For example: tags: id version name -- ------- ------ 1 1 sport 2 ...
Given two data frames df1 = data.frame(CustomerId=c(1:6),Product=c(rep("Toaster",3),rep("Radio",3))) df2 = data.frame(CustomerId=c(2,4,6),State=c(rep("Alabama",2),rep("Ohio",1))) > df1 CustomerId Product 1 Toaster 2 Toaster 3 Toaster 4 Radio 5 Radio 6 Radio > df...
I want to use below code and want to get datas from both tables, but how? There aren't any association in my hibernate mapping files. SQLQuery query = session.createSQLQuery("SELECT cat.* from cat inner join owner on cat.owner_id = owner.id where owner.name=:username"); query.addEntity(Cat.class); query.setInteger("username",'Duke');...
Consider the following database schema: create table UserGroup ( id int not null auto_increment, name varchar(200), primary key(id)); create table User ( id int not null auto_increment, name varchar(200), groupId int not null, primary key(id)); User.groupId = UserGroup.id, so a user can only be a member of one group, but a usergroup ...
I am looking for a tip on how to optimize this.... SELECT u.uid, u.username, u.nid, wd.pay FROM (users_data AS u LEFT JOIN winners_data wd ON u.nid = wd.nid AND u.uid = wd.uid) LEFT JOIN dp_node dn ON u.nid = dn.nid WHERE u.uid = ".$val." ...
In the code below I create 20 threads, have them each print out a message, sleep, and print another message. I start the threads in my main thread and then join all of the threads as well. I would expect the "all done" message to only be printed after all of the threads have finished. Yet "all done" gets printed before all the threads...
Hello, I am new to rails and would appreciate some help optimizing my database usage. Is there a way to load two models associated with each other with one DB query? I have two models Person and Image: class Person < ActiveRecord::Base has_many :images end class Image < ActiveRecord::Base belongs_to :person end I would like to ...
I am trying to code a social networking element to our website. We have two tables, one showing users and one showing friendships between those users. Users: userid - PK, int name profilepic Friendships: id - PK int userid friendid datecreated The friendship table is two-way - ie someone can befriend someone without them frien...
I struggling with a problem I have in TSQL, I need to get the top 10 results for each user from a table that might contain more than 10 results. My natural (and procedurally minded) approach is "for each user in table T select the top 10 results ordered by date". Each time I try to formulate the question in my mind in a set based appro...
I have 2 temp tables #temp1 and #temp. Both have a key and date columns. Both have around 25k rows. And I'm left joining them on the basis of the key and date which are unique on all rows. It's taking around 4 minutes for this join to complete. Is there any way to speed it up or any alternative methods? ...
is there a way to tell ms access (2003) to not put joins into parentheses. or at least to understand them without (every other database does) i want something like: SELECT * FROM a INNER JOIN b ON a.a = b.a INNER JOIN c ON b.c = c.c but access tells me that the query is wrong. IT’S NOT, and it’s driving me c...
Hello guys, Is it possible to select only some columns from a table on a LEFT JOIN? ...
i have two tables like this -> Categories ----------- id name --------- 1 --> a 2 --> b 3 --> c Messages ------------ id catid message --------------------------- 1 --> 1 --> aa 2 --> 1 --> bb 3 --> 1 --> cc 4 --> 2 --> d...