join

Outputs all minus one

I use this code to output all threads with a specific forum ID $query = mysql_query("SELECT * FROM forums JOIN threads ON threads.fid = forums.id WHERE forums.id = ".intval($_GET['forumID'])); $forum = mysql_fetch_assoc($query); ?> <h1><a>Forums</a> &gt; <?=$forum['name']?></h1> <?php while ($thread = mysql_fetch_array($query)): ...

Linq: What is the difference between == and equals in a join?

I always wondered why there's an equals keyword in linq joins rather than using the == operator. Property deadline = (from p in properties join w in widgets on p.WidgetID equals w.ID select p).First(); Instead of Property deadline = (from p in properties join w in widgets on p.WidgetID == w.ID select p).First(); [EDIT] Rephra...

Oracle with join , need uniques

Hi i have a "small" problem , iam using join to get some names for OS Architectures. SELECT "OSARCH".*, "OS".* FROM "OSARCH" INNER JOIN "OS" ON OSARCH.OSARCH_ID = OS.OSARCH_ID The result is something like this : OSARCH_ID OSARCH -- ------- 22 Powerpc 22 Powerpc 22 Powerpc 28 x86 28 x86...

MySQL: Merge 2 tables for a SELECT query?

First.. here are the two tables I've created (sans irrelevant columns).. CREATE TABLE users_history1 ( circuit tinyint(1) unsigned NOT NULL default '0', userh_season smallint(4) unsigned NOT NULL default '0', userh_userid int(11) unsigned NOT NULL default '0', userh_rank varchar(2) NOT NULL default 'D', userh_wins int(11) NOT ...

Is a JOIN faster than a WHERE ?

Suppose I have two tables that are linked (one has a foreign key to the other) : CREATE TABLE Document ( Id INT PRIMARY KEY, Name VARCHAR 255 ) CREATE TABLE DocumentStats ( Id INT PRIMARY KEY, DocumentId INT, -- this is a foreign key to table Document NbViews INT ) I know, this is not the smartest way of doing things, but t...

Oracle performance and memory

Hi, I have two queries, and I want to understand which is better in terms of performance and memory. I also welcome other alternatives for these two. Query 1: SELECT DISTINCT a.no, a.id1 , a.id2 FROM tbl_b b , tbl_a a , tbl_c c , tbl_d d WHERE ( b.id1 ...

SQL select joined string for set of ids

I have to deal with a mssql database and the information given in a table like this: Users: ID Name Countries -------------------- 1 User1 1,2,3 2 User2 2,5 Countries: ID Country ---------- 1 Australia 2 Germany 3 USA 4 Norway 5 Canada Now, what i am looking for is a select statement that will give me a result like thi...

Query related data based on "like" statement

I have two tables Table1: ColumnA (varchar) Table2: ColumnB (varchar) I need to get all rows from T2 where ColumnB is "like" any of the rows from 'ColumnA%'. For example, rows in T1 might be: Summer09 Fall09 while rows in T2 might be Spring09 Com101 Sec1 Summer09 Stat400 Sec2 Fall09 CS200 Sec3 In that scenario it would retun...

NHibernate join table mappings

I have an interesting issue, which I can't seem to find a satisfying answer for. It concerns join tables. Basically (I'm sure this has been out here in some form or another, but I cant find it) I have 3 tables. A Person table, Address table, PersonAddress.. Person PersonID Name Age etc.. Address AddressID AddressLine1 AddressLine...

MySQL Help

I need to run a query, and Im not sure if it's possible. I could do 2 queries but one is better right :| I have two tables. First is user, this consists of username and userid. The second is search which consists of userid and query. When I select the search table I want user ID to be replaced by username by taking the data from the use...

Linq Join Question

Hi guys, I have a problem trying to do a couple of things with linq joins... currently I have a group in linq that gives two columns, basically a count of tickets by location. Well now I'm trying to add a join that will join on the ticketID columns of two different tables Tickets and Comments. I'm having a hell of a time trying to co...

How do I Write a SQL Query With a Condition Involving a Second Table?

Table1 ... LogEntryID *PrimaryKey* Value ThresholdID - - - Link to the appropriate threshold being applied to this log entry. ... Table2 ... ThresholdID *PrimaryKey* Threshold ... All fields are integers. The "..." thingies are there to show that these tables hold a lot more imformation than just this. They are set up this way for ...

Join on multiple booleans

I have two tables, each with three boolean (ms-access "Yes/No") columns. Table 1: A1, B1, C1 Table 2: A2, B2, C2 I would like the rows from Table 2 given a specific row from Table 1 that satisfy the following conditions: If A1 is true then only rows where A2 is true, if A1 is false then rows where A2 is true or false. If B1 is true t...

Django models - self join on both FK in a M2M scenario using QuerySet

Let's take following M2M scenario. I want to get all the colleagues of any given student, and the number of courses both of them attend. (Meaning how many courses the given student has in common with each and any of his colleagues.) class Student(models.Model): pass class Course(models.Model): students = models.ManyToManyField...

SQL join multiple values from column into one cell

There are a ton of sql join q's already but I didn't see my answer so here goes . . . I am working with WPDB (Wordpress database)/EZSql/MySQL 5.0. Trying to achieve the 'simple' desired output below has not proven to be easy. Current output MemberID MemberName FruitName -------------- --------------------- --...

Create a matrix/table from a LEFT JOIN in SQL

I'd like to know a good solution for converting a LEFT JOIN into a table/matrix. For example, given this schema: objects id (integer) name (string) attributes id (integer) object_id (integer) name (string) value (string) And these values: 1,ball 2,box 1,1,colour,red 2,1,shape,sphere 3,1,material,rubber 4,2,colo...

CRUD Operations with join table using LINQ and Telerik radGridView

What is the best CRUD design pattern for an editable grid that will be databound to a joined query derived from LINQ? Background It's probably irrelevant whether I'm using Telerik's radGridView because this situation may pertain to any list control but I thought that I'd mention that if it helps. At any rate, I have two tables. One tab...

Joining keys/values from C++ STL associative containers

I have a join function that operates on STL strings. I want to be able to apply it to to a container like this: getFoos(const std::multimap<std::string, std::string>& map) { return join_values(",", map.equal_range("foo")); In other words, find all matching keys in the collection and concatenate the values into a single string wit...

Is there any perfomance issue with Inner Join?

Hi, Currenlty I am using lot of inner join's(around 7) in my sp, does it have any impact on sp perfomance. does left outer join gives better perfomance then inner join. one more thing if i m joining two tables a and b which has column id and id1, both r not nullable. i suppose here i can go for inner join as these columns r indexed. ...

How to use Join with NHibernate.Mapping.Attributes?

I am trying to join two tables to map to a single entity using NHibernate.Mapping.Attributes. It works when there is only one property in the joined table. However, when there are additional properties they get mapped to the primary table instead of the joined table. [Class(Table = "primarytable")] public class Entity { [Id(0, Name ...