join

Fetch fields from a table that has the same relation to another table

I'll try to explain my case as good as i can. I'm making a website where you can find topics by browsing their tags. Nothing strange there. I'm having tricky time with some of the queries though. They might be easy for you, my mind is pretty messed up from doing alot of work :P. I have the tables "topics" and "tags". They are joined usi...

SQL Outer Join Function

I've written this function before but I can't seem to remember it and it didn't get into version control. Now, more to do with sleep deprivation than anything else, I can't remember how to rebuild it. Here's the idea. I have two tables, "regPrice" and "custPrice", with shared key "itemID." They both have a "price" column and custPrice a...

Problem Query

I am facing issues trying to write a query. My tables are laid out as follows: tblTicketIssues TicketID | RequesterID tblPersonnelProfile PersonnelID | FirstName | LastName tblTicketAttribute TicketID | Attribute | AttributeValue I have to display the following fields: TicketID, RequesterFullName, UrgentPriorityID, MediumPriori...

MySQL Join Problem

I am having a problem with MySQL joins. Table_A: A_id Cost1 A1_id Cost2 1 500 0 200 1 100 1 100 1 50 2 60 1 10 3 50 2 5 0 10 Table_B (Refers B_id: from Table_A A1_id): B_id FName LName 1 X A 2 Y B 3 Z C Table_C (Refers C_id...

LINQ - join 2 datatables - wildcard

I am using LINQ to join 2 datatables. I am trying to get back only 1 field from dtTable2 and all the fields from dtTable1. When this code executes, I have two columns. The first column contains the value from dtTable2.field2 and the second column has the value "System.Data.DataRow". I know that if I explicitly specify the columns in ...

How to outer-join two tables (main and many-to-one sub-table) to get only ONE item from second table?

I have two tables that are something like this: Main table: id (int), title (varchar), etc. Sub-table: main_table_id (foreign key into main table), tag (varchar), etc. There can be zero or more subtable rows for a given row in the main table. I want to do a query that will return every row of the main table, with the columns of the...

Problem Query

Hi, Basic need is if a record has an Attribute of "Urgent", then the attributevalue should be displayed in the Urgent column. If the record has an attribute value of "closed", then the attributevalue must be displayed in the "Closed" column. I have a query below. My problem is that among the results I am getting back, there are two rec...

Relative Efficiency of JOIN vs APPLY in Microsoft SQL Server 2008

We're just starting to look at moving to SQL 2008 from SQL 2000 and are noting the new CROSS APPLY and INNER APPLY syntax that allows a form of 'joining' against either table-valued parametrized UDFs or correlated subqueries. Obviously it would be nice to be able to encapsulate logic in a UDF and be able to reuse it in different queries...

MySql query help : joints with sums and counts

Hello community, I have this database structure: TBL_A | TBL_B | TBL_C | TBL_D | TBL_E -------+---------+---------+---------+---------- id | id_tbla | id_tbla | id_tbla | id name | id_user | id_user | id_user | name_tbla ... | is_bool | | weight | id_user Here is what I'm trying to achieve : SELECT a.id...

Uses of unequal joins

Of all the thousands of queries I've written, I can probably count on one hand the number of times I've used a non-equijoin. e.g.: SELECT * FROM tbl1 INNER JOIN tbl2 ON tbl1.date > tbl2.date And most of those instances were probably better solved using another method. Are there any good/clever real-world uses for non-equijoins that ...

SQL JOIN: ON vs Equals

Is there any significant difference between the following? SELECT a.name, b.name FROM a, b WHERE a.id = b.id AND a.id = 1 AND SELECT a.name, b.name FROM a INNER JOIN b ON a.id = b.id WHERE a.id = 1 Do SO users have a preference of one over the other? ...

How do I join (merge?) several tables?

I've got three tables AUTHOR_TABLE ------------- AUTHOR_ID (PK) AUTHOR_NAME 1 me 2 you ARTICLE_AUTHOR_TABLE ------------- AUTHOR_ID ARTICLE_ID 1 100 2 101 EVENT_AUTHOR_TABLE ------------------------------------------ AUTHOR_ID EVENT_ID 1 200 1 201 All ...

JPA @OneToMany Sets and unique contstaints

I have the following scenario: I have a system full of users. There is a desire to run contests for users who log into the site over a week or so. Therefore I needed to create a new Contest object which would contain both the entries and the winners. I created something like this: private Set<User>; @OneToMany(cascade = CascadeType....

SQL Query Question (Possible Join / Subquery?)

Hi, I have two tables (Management and Employee). The management table tracks the different management teams that have managed company X in past few years. Each management team is given an ID (i.e: managementnr), and each team has a CEO (namely ceoname). The employee table tracks employees working for company X (basically just their ...

Need tips for optimizing SQL Query using a JOIN

The Query I'm writing runs fine when looking at the past few days, once I go over a week it crawls (~20min). I am joining 3 tables together. I was wondering what things I should look for to make this run faster. I don't really know what other information is needed for the post. EDIT: More info: db is Sybase 10. Query: SELECT a.id, a.da...

MySQL LEFT JOIN after 5.0.12 changes - How to rewrite query

After 5.0.12 MySQL changed the syntax for left joins to match SQL2003 standard. So ... FROM t1 , t2 LEFT JOIN t3 ON (expr) needs to be rewritten as ... FROM (t1 , t2) LEFT JOIN t3 ON (expr or else it will be parsed as ... FROM t1 , (t2 LEFT JOIN t3 ON (expr)) Now, I have an ancient app I'm porting from MySQL 3.23 (eek!) to 5.1, a...

Serious MySQL Performance Issue (Joins, Temporary Table, Filesort....)

I've got a users table and a votes table. The votes table stores votes toward other users. And for better or worse, a single row in the votes table, stores the votes in both directions between the two users. Now, the problem is when I wanna list for example all people someone has voted on. I'm no MySQL expert, but from what I've figure...

How can I count all the records in the both tables in a 1:n relationship in 1 query?

I'm guessing this a fairly easy question, but I've run into this problem a number of times and I can't find a solution either through searching (maybe I don't know what to search for) or trial and error. I have the following table structure and data: CREATE TABLE `table_a` ( `id` int(11) NOT NULL auto_increment, `table_b_id` int(11...

SQL query to exclude records if it matches an entry in another table (such as holiday dates)

I have two tables: Application applicationid (int) applicationname (varchar) isavailable (bit) and Holidays applicationid (int) holidaydate (datetime) I need to get the isavailable flag for any given applicationname but it should only return if the day if is not a holiday. The isavailable flag is independent of ...

NHibernate join single value from other table

I'm trying to get nHibernate to join the name of the structure that is stored in separate table into the structure POCO object. The database looks something like this: +-----------+ +------------+ +-----------+ | Structure | | Texts | | Languages | +===========+ +============+ +===========+ | Id | | Id | | I...