joins

Zend Join Models

I have models of my tables and would like to do a join using a model as opposed to to a table. For example, instead of: $select = $this->select() ->from(array('p' => 'products'), array('product_id', 'product_name')) ->join(array('l' => 'line_items'), 'p.product_id = l.product_id', ->limit(20, 10); where I specify the table na...

SQL JOIN get name and all tags for item

Hi all, this is my first ever JOIN clause and I did run into a problem. I want to echo all the necessary information for my items but I don't understand how to echo all the tags for one item, right now I get a list with duplicates of items but with different tags if more than one tag is assigned for an item. Any ideas? Better ways to do...

SQL query not returning results when expected

I am having trouble with my queries not returning any results. There are no errors in the query but i expect to see a result and i don't get any Here is my table structure CREATE TABLE IF NOT EXISTS `boards` ( `boardid` int(2) NOT NULL auto_increment, `boardname` varchar(255) NOT NULL default '', `boarddesc` varchar(255) NOT NUL...

Not getting back enough columns

My query is as follows: select [Transaction].id, [User].name, EventType.EnglishDescription from [Transaction] join [User] on [Transaction].userid = [User].id join EventType on EventType.id = [Transaction].EventType I'm expecting to get back Transaction.id, User.name and EventType.EnglishDescription in the colum...

SELECT from one table while two conditions are NOT TRUE in another table. Possible?

I'm stuck on my matchmaker site. It's like eHarmony for married couples to find other couples to socialize with. I'm trying to be a good rdba and am using a million tables then using a number of complicated joins to pull the info out I want. But, I'm stuck on one of the last ones. I can do it in the code, but I should be able to do it i...

Not sure how to go about joining these two tables.

I had trouble coming up with a title for this question because I'm not really sure how to do what I want, so I'm not sure if "joining" is the best terminology in this instance. I have two tables in my database, Plans and Assumptions that look like the following: Plans Table Plankey ass1 ass2 ass3 ass4 aplan 0 6 5 7 bplan...

help to write a query statement

I need a query statement written such that it searches three tables table 1 id fullname active table 2 id fullname table 3 id fullname I wanted this query to search all three tables combine and give me the result for those id fullname that is not active ...

joining 3 queries with outer join

Hi, I have created a table tblOperationLog and wrote triggers to fill it as a user deletes, updates or inserts a row in my main table. It’s the delete and insert one: CREATE TRIGGER FILL_TABLE ON Person FOR INSERT, DELETE AS INSERT INTO tblOperationLog SELECT SYSTEM_USER, 'user has inserted a row with ID = ' + Conv...

Ruby on Rails, Model Joins

Hi Everyone, I have 2 models: class Video < ActiveRecord::Base belongs_to :categories, :foreign_key => "category", :class_name => "Category" end class Category < ActiveRecord::Base has_many :videos end This is fine so far, in my videos controller for the index page I have: def index @videos = Video.all(:joins => :categories) etc, ...

MYSQL - limiting joins

Hi, I know this subject has been covered before on here but I need a different result to the other questions. I need to order some results on institution name and then candidates last name. SELECT DISTINCT candidates.*, histories.job_title, institutions.name FROM candidates JOIN histories ON histories.candidate_id = candidates.id JOI...

Adding extra field to sql query

I have a query i've written for a private message system but i would like to add an extar field in the query but not sure how to do it. At the moment it gets the username of the sender but not the recipient but it does however get both ids of sender/recipient. How do i add an extra field in my query to get the username of the recpient?...

MySQL joins? What are they? When to use them?

I am probably going to get slaughtered for this, and told to read the manual (which I have, but it has not helped my understanding). I working with MySQL more and more at the moment, but I cannot get my head around joins, I know there are multiple types of join, i.e Inner, Outer, Left, Right but I do not what the differences are between...

How can I find all results with a null foreign key in MySQL?

I have 2 tables... meat and potatoes in MySQL. meat_id is the primary key for the meat table and there is a meat_id in the potatoes table that links the 2 tables. I want to find all rows in the potatoes table that don't have a valid meat_id. Any ideas? ...

SQL - Using a join to pass a list to a lookup tbl

Hi Everyone, I want to be able to loop over a list and return a single row where all the list elements have matched. e.g. lest say my list of id's is 1,2,3 I want to write an SQL statment that will do the following (without breaking). SELECT id1 FROM TBL WHERE 0=0 AND id2 = 1 AND id2 = 2 AND id2 = 3 Is this possible? Thanks in adv...

Add Conditional Join Dynamically with Linq

I have a basic search control which lists companies from a CRM depending on predefined search/filtering criteria supplied by dropdowns. The default selection is "ALL" for each DropDown, otherwise the user chooses a specific item(s). I'd like to be able to construct a Linq query dynamically based on the selections. Out of the 5 selectors ...

Speeding up query with join, where, group by, and count.

I have an elementary query that is taking too long to execute even on small tables (<100,000 rows): select images.classification, count(boxes.id) from images join boxes on images.id = boxes.image_id where boxes.round = 0 group by images.classification; I have indices on boxes.round, boxes.image_id, and images.classification (only varc...

Joining multiple Parent/Child records

I am doing some analysis on a Cisco ICM database looking specifically at transferred calls. Basically there is an ICRCallKey field which is a unique number generated at a peripheral gateway for each line logged in the table. I can tell when a call is transferred by looking at the ICRCallKeyParent and ICRCallKeyChild fields and seeing if...

SQL Join with Null Values

My Query does not work when I have not set the ProcessorID in myComputer table How can I make a query that will show me the ProcessorName when I have a value assigned in ProcessorID or NULL when I don't? CREATE TABLE myProcessor ( ID INT NOT NULL AUTO_INCREMENT PRIMARY KEY, Name VARCHAR(255) NOT NULL ) ENGINE=InnoDB; INSERT IN...

changing left join to basic join

Hi there, I currently have some SQL that should return 3 rows of data but returns 6 (3 rows repeated twice). I believe this is down to my syntax and want to try and build the query using basic joins, currently the SQL looks like this, `function getMultiContentById($id) { $query = "SELECT FROM `mailers` LEFT JOIN `mailer_co...

how to query related three related tables using one query

I have three following tables. The relationship is that each report_param and report_frequency is tied together using the intermediate table report_freq_map. Is it possible to construct an SQL so that user can select all the report_param rows using the constraint like frequency='daily', instead of using frequency='1'. Thanks, -peter...