join

MySQL left join one table to itself

Please consider the following table 'mmm': select * from mmm; Output: +-------+-------+------+ | texto | value | n | +-------+-------+------+ | aaa | 10 | 1 | | aab | 10 | 1 | | aaa | 11 | 1 | | aab | 11 | 1 | | aaa | 10 | 2 | +-------+-------+------+ The command: select...

PDO to Zend_Db_Select with JOIN

I have this query: SELECT groups.name categories.name, categories.label FROM groups JOIN categories ON (categories.group1 = groups.id OR categories.group2 = groups.id) AND groups.label = :section AND categories.active = 1 Now, this is my JOIN using Zend_Db_Select but it gives me array error $select...

mysql problem with union/join used in conjunction

I have these two tables: Table Author: ID (INTEGER), author_name (VARCHAR), first_name (VARCHAR), last_name (VARCHAR), preferred_name (VARCHAR) Table CoAuthored: ID (INTEGER) author1ID (INTEGER), author2ID (INTEGER), paper_ID (INTEGER) // (ID of the co-authored paper referenced by this link) As you can see, both tables have a colu...

PHP MySQL Query to get most popular from two different tables

Hi all, I have two tables afrostarprofiles and afrostarvvideos created as shown below. artistid field in afrostarvideos is the primary id of afrostarprofiles. I want a single query that will return for me most popular afrostarprofile based on sum of views(afrostarvideo) and views of afrostarprofiles. i basically need an algorithm or ...

MySQL join on record that might not exist

I'm trying to execute a query that looks similar to this: SELECT <columns> FROM table1 INNER JOIN table2 ON table1.id = table2.table1_id INNER JOIN table3 ON table1.id = table3.table1_id WHERE table3.column1 != 'foo' AND <other_conditions> LIMIT 1; The thing is--I want the query to return a result regardless of whether the record in ...

Rewriting MySQL query with subqueries to joins.

I have written a fairly complex SQL query to get some statistics about animals from an animal sampling database. This query includes a number of subqueries and I would now like to see if it is possible to rewrite this query in any way to use joins instead of subqueries. I have a dim idea that this might reduce query time. (it's now about...

LINQ Join on top 1

I've got three objects (splistitemcollection) that im joining together which is working great but the problem im having is that there are a one to many relationship between the a contract object and a customers object. I need to grab just just the first customers object for each contract object during the join. Here is what I am getting...

NHibernate - Retrieve parent / children with criteria applied only to children

I have a parent entity with a list of child entities. When using NHibernate to retrieve a given parent with children from SQL, it works fine if there are no children OR if there are children with dates that match the where condition. If there are children that do not match the where clause, the parent is null. I want to have the paren...

Pervasive Sql 10 Join one table, onto another, onto another

I have a table with products. When I get information from that table, I would also like to get the ETA of that article. To do so, I am planning to get the latest purchase Order Row, that is on this article, and then get the expected delivery of this purchase. This is three different tables and I would like it to be like another column o...

Export a MySQL table via JOIN?

Hey guys..... I was wondering if rather than creating another table to store the results of the MySQL JOIN query is it possible to just export a queries result.... like to a CSV? The query for example would be something like this.. SELECT us.id, CONCAT_WS(' - ',cats_sic_us.sic,cats_sic_us.category) AS sic_cat FROM us I...

Oracle "(+)" Operator

I am checking some old SQL Statements for the purpose of documenting them and probably enhancing them. The DBMS is Oracle I did not understand a statement which read like this. select ... from a,b where a.id=b.id(+) I am confused about the (+) operator., and could not get it at any forums.. (searching for + within quotes didn't work...

Select query in MySQL

Two tables Table1 ID FileName 1 abc 2 abc 3 abc 4 xyz Table2 ID Table1_ID isDeleted 1 1 1 2 2 1 3 3 0 4 4 0 I need to get the count of filename for the isDeleted=1 by passing any ID of table1, i.e for all the values(1,2,3) of ID, i need the count as 2 I tried with the following query S...

How to combine results from multiple tables with different columns?

I have several tables with different numbers and types of columns, and a single column in common. +--------+---------+------------+-------------+ | person | beardID | beardStyle | beardLength | +--------+---------+------------+-------------+ +--------+-------------+----------------+ | person | moustacheID | moustacheStyle | +--------+-...

Join a Flat Table to an EAV Table with Magento?

Hi, I am trying to manke an Adminhtml Grid which joins a table that I made called "facility" to "customer_entity" and all of its attribtues. I'm noticing that since my table is flat, and the model inherits fomr Mage_Core_Model_Mysql4_Collection_Abstract, I'm not able to use all the class_methods that I've seen as examples on the back...

mysql join query

I have 2 tables like this Table 1 id name 1 ABC 2 DEF 3 GEF Table 2 name meal ABC m1 ABC m2 GEF m1 Table 3 meal detail m1 mutton m2 beaf How can I get output like this? Id name meal_detail 1 ABC mutton,beaf 2 DEF 3 GEF mutton Thanks in Advance ...

Select All Right Hand Rows for Each Left Hand Row SQL.. HELP!

Hello, I am currently looking for a Select statement which will do this.. |------Apples------| |--id--| - 1 - 16 - 23 - 42 |------Oranges------| |--id--| - a - b - c *SELECT STATEMENT* |------Fruit Cocktail------| |--AppleID--|--OrangeID--| 1 a 1 b 1 c 16 ...

Using INNER JOIN with MySQL on selecting multiple tables?

Hi all, I have a site where a specific set of data is collected. This is a travel agency website. It is neccesary to determine whether or not an accommodation is still bookable. When I don't select any searchfilters (like destination, classification, facilities etc) I get a working query. The looks like: SELECT `accommodation` . * FROM...

Basic MySQL Table Join?

I have these 2 tables: ASSOCIATION_TABLE: [id, key, name, manager, office, bank, customer] and OFFICE_TABLE: [id, name, address, phone] I am currently running this query to get a data set that I need: SELECT `name`, `key`, `office`, `manager`, `id` FROM `database`.`ASSOCIATION_TABLE` WHERE `association`.`customer`=4; How can I m...

MySQL: Complex question with schema given

Here is the schema: Student(Snum, Sname) Course(Cnum, Cname) Prerequisite(Cnum, Prereq) Professor(Pnum,Pname, Dept, Office) Enrollment(Snum, Cnum, Term, Section, Mark) Schedule(Cnum, Term, Section, Days, Time, Room) Class(Cnum, Term, Section, Instructor) The full question is: Find professors (Pnum, Pname, Dept) who whenever taug...

MySQL Query Help

I have the following 5 tables: users(user_id) books(book_id, author_id) source_phrases(source_phrase_id, book_id, phrase) synonym_phrases(synonym_phrase_id, source_phrase_id, reader_id, synonym) synonym_ratings(synonym_ratings_id, synonym_phrase_id, rater_id, rating) I am trying to get a query that will select all the books a user has...