join

MySQL - Find rows matching all rows from joined table AND string from other tables

Hi, this is a follow up from http://stackoverflow.com/questions/1242223/mysql-find-rows-matching-all-rows-from-joined-table Thanks to this site the query runs perfectly. But now i had to extend the query for a search for artist and track. This has lead me to the following query: SELECT DISTINCT`t`.`id` FROM `trackwords` AS `tw` IN...

Making a MySQL view easier to update & load

I'm having a view that's actually a combination of 2 other views, which in they turn are the split of Table1 join Table2. The split in the 2 view is based on whether the column of the table1 EnterDate=curDate(), which determines the way a new column is calculated ( NewColumn=(Table2.Cpx-Table1.Px)*Table1.Size if EnterDate=curdate() and N...

Help with this JOIN

$threads = mysql_query("SELECT DISTINCT t.id, t.title FROM threads t LEFT JOIN comments c ON t.id = c.parentID ORDER BY c.date DESC"); while ($thread = mysql_fetch_assoc($threads)) { echo "<li><a href=\"?threadID=$thread[id]\">".htmlspecialchars($thread['title'])."</a></li>\n"; } Can ...

How do I JOIN aggregations results from several SQL SELECTS?

I have a MEMBERS table with the following relevant columns: Name JoinDate Level --1=Gold,2=Silver,3=Bronze** I want to create a single query to return a membership summary that lists the total number who joined by year and by membership level. Basically, the columns in my resultset would be something like this: | YEAR | ...

mysql: multiple join problem

Hi, Im trying to select a table with multiple joins, one for the number of comments using COUNT and one to select the total vote value using SUM, the problem is that the two joins affect each other, instead of showing: 3 votes 2 comments I get 3 * 2 = 6 votes and 2 * 3 comments This is the query I'm using: SELECT t.*, COUNT(c.id) as ...

orderby statements on multiple joins

Hi, I have three tables, each contain an auto-incrementing PK. I need to select the latest (ie, ORDERBY DESC) entries from the mix of these tables. I'd like to do it in one query, with two joins. My idea was is to somehow select a table, order it by id DESC, then somehow merge the results. Does anyone have a way (or probably a better id...

Using linq2SQL to return a 1 to many relationship?

Hi there, is there a good way of of returning a 1 to many relationship from linq2sql, probably needs some explanation ! :-) Basically i have a table that has invoices and another table that is invoice details.. I have my linq2sql classes that were automatically created using linq2sql designer ... So to return the query where invoice ...

conditional join in mysql

Hi, I have a table id1, id2, type. type is an enumerated value containing a name of another table. I'd like to preform a join with the name of the table of type. For example: switch($type) case 'table1': join table1; break; case 'table2': join table2; break; How can I achieve this? ...

SQL join related question

Hi guys, I am having trouble with a SQL join question. I have a table EMPLOYEE with EmpID, FirstName, LastName, Email, Phone I have another table OTHERNAME with 2 fields "Name" & "OtherName". This table contains lookup values such as "James", "Jim"; "Thomas", "Tom"; "Steven", "Steve". I want to write a query which will return rows E...

Conditions on outer joins

I'm trying to come up with an Access query which is equivalent to this oracle query select ledef_name, count(class.EVT_PK) timesTaught from ingenium.ledef course, ingenium.evt class where course.LEDEF_PK = class.EVT_LEDEFFK(+) and class.EVT_STARTDT(+) > to_date('2009-01-01', 'yyyy-mm-dd') group by ledef_name In ac...

join between two tables with linq to datasets

i want to create a join between two tables and that the result will include all the two tables columns. i want to do this without specifying the specific column names, just do select all, because i won't know how many columns will the two tables include and won't know their names. I JUST WANT TO CREATE JOIN BETWEEN TWO TABLES AND THAT T...

mysql structure for comments and comment replies

Hey, I've been thinking about this one for quite some time now, I need a way to add replies to comments in the database but I'm not sure how to proceed. This is my currently comment table (doesn't say much but its a start): CREATE TABLE IF NOT EXISTS `comments` ( `id` int(12) NOT NULL AUTO_INCREMENT, `comment` text, `user_id` in...

Mysql join on similar columns

I want to join table1 with table2 on column 'Name', but table2.Name has an 'e' in front of all the names (if table1.name=ABC,table2.name=eABC). How am I supposed to use a join for those two? I tried FROM table1 join table2 on 'e'+table1.name = table2.name, but it doesn't work... ...

Multiple counts within a single SQL query

I'm trying to get the count of documents within 4 specific sections using the following code: SELECT category.id , category.title , count(ts1.section_id) AS doc1 , count(ts2.section_id) AS doc2 , count(ts3.section_id) AS doc3 , count(ts4.section_id) AS doc4 FROM category LEFT JOIN category_link_section A...

Zend Framework Db Select Join table help

I have this query: SELECT g.title, g.asin, g.platform_id, r.rank FROM games g INNER JOIN ranks r ON ( g.id = r.game_id ) ORDER BY r.rank DESC LIMIT 5` Now, this is my JOIN using Zend_Db_Select but it gives me array error $query = $this->select(); $query->from(array('g' => 'games'), array()); $query->j...

What is the syntax to force the use of an index for a join in MySQL

The use of the "FORCE/USE/IGNORE INDEX" when doing a straightforward select is well-documented, but it's not clear from the documentation how to do it for a JOIN. How do you force a specific index to be used for a joined table? ...

Bind Results from a Left Join using mysqli

Hi, I'm recently changing to mysqli and while performing an update on a script, i couldn't manage to use the same SELECT information as i did before. How can I bind_results from a Left Join between 3 tables? This is the script: "SELECT actor.id, actor.name, actor.gender, thumbs.id, thumbs.filename, thumbs.actorid FROM actors, thum...

Is navigation through composite-id's key-many-to-one possible?

Is it possible to navigate through the key-many-to-one associations of a composite-id in Nhibernate? I have a few (legacy) tables that I mapped with the following settings: <class name="StructureUser"> <composite-id> <key-many-to-one name="Structure" class="Structure" column="STRUKTUR_ID" /> <key-many-to-one name="U...

SQL - LEFT OUTER JOIN and WHERE clause

Hello, I'm terrible at SQL. I do not know if what I am trying to do is possible. But, because of our data structure, I need to solve this problem this way or do a massive architectural change. I am trying to count the number of 'Provinces' (a.k.a States) for a Country. However, there are just a few Provinces that need to be ignored fro...

How do I link one table two times to another table using cakePHP

Hi I'm trying to set up a social networking type of site, where users can have friends and send each other messages. I have a users table, and a friends table, which basically has an user_id and user_friends_id. Using cakePHP, how can I link the user_id and user_friend_id back to the users table? I used bake to create all my models and...