join

How to write better this Mysql Join query

I have the next tables Users {id, name} Messages {id, user_id, cache_user_name} What I want is to do a JOIN only when cache_user_name is NULL for performance reasons. For example: SELECT Messages.*, Users.name FROM Messages INNER JOIN Users ON (Messages.user_id = Users.id) // ON (ISNULL(Messages.cache_user_name) AND ... The best...

Is the join query correct?

I have multiple tables that I want to join to get a specific result. Tables are: ad_categories, ad_image, ad_data, ad_location I need a row output for every one of the following rows in the ad_categories table, regardless if there exists data for any of these categories for a specific location when using the WHERE conditional. mysq...

How do I JOIN a third table in my SQL statement which returns a COUNT without losing the 0 count items?

A continuation from this question I need a SQL statement that returns the number rows in a table with a specific value. We left off with a SQL statement as follows... SELECT t.teamid, t.teamname, COALESCE(COUNT(p.playerid), 0) AS playercount, t.rosterspots FROM TEAMS t LEFT JOIN PLAYERS p ON p.team...

LINQ Join Where Clause

Hello all. I'm struggling with a join/where clause with what is a rather simple sql select statement. I am trying to retrieve a list of product information from tb1 with the where condition behind situated in tbl2 but this must be joined by three different columns. so the SQL would look something along the lines of: SELECT tb1.* ...

Parent-child relationship: multiple tables vs. self-join

I'm working on a database for stat keeping for a sports club where I help administrate some events. I think I've got the parts for keeping track of the competitor names, classifications, categories, etc. figured out using a main table, several lookup tables and a couple bridge tables to cover the many-to-many relationships involved. I ...

Doing a join over 2 tables in different databases using Hibernate

Hi everybody, I have two tables in two separate oracle databases (not schemas) that I need to join in Hibernate. Currently, I have two hibernate sessions going out to the separate databases. Before anybody says look at Hibernate Shards, I have spent a better part of a day looking at that sub-project and have found that: it is for hor...

Using FFMPEG to join two MTS files together

I have two MTS video files, each one 2 minutes long. I need to be able to join the files together and convert the format to MPEG4. I have a suitable command line for converting MTS to MP4 but don't know how to join the files together in the first place. Some articles on the web suggest using the CAT command, like: cat video1.mts video2...

JOIN 3 tables query

ok, here we go... I have 3 tables: posts, comments and members, i need to get title from posts, msg from comments and username from members. all tables has an identical ID to associate(?) for example: i want to get these data from certain ID... sorry for my bad english, isn't my language, and my very basic skill on MySQL (started toda...

Oracle SQL - select within a select (on the same table!)

Hi guys, I'll try and explain what I'm trying to achieve quickly, since I have no idea how to explain it otherwise! We have a table here that shows all employment history for all employees, I want the "Start_Date" of the current post ("Current_Flag" = 'Y'). As well as that, I want the "End_Date" of the post before that (was going to fi...

nhibernate "join"

is any way to optimize this solution? whether there is any possibility of obtaining the same result by calling single query? public List<Company> GetCompanies(DateTime maxDate, int stockQuotesCount) { List<Company> result = new List<Company>(); IList<Company> company = null; DateTime lastSessionDate = new St...

LINQ query using join and aggregate functions

Urgh...my LINQ has suddenly taken a turn for the worse!! I have two tables and want to join and perform aggregate functions upon the tables. Firstly, is this possible, I am assuming it is, and secondly, how do I handle the aggregate functions? As part of the same LINQ statement? The SQL statement will look a bit like this: SELECT ...

Problem combining multiple SQL JOINs

ALTER PROCEDURE [dbo].[GetValues] @FieldName NVARCHAR(50), @FormName NVARCHAR(50), @PoolName NVARCHAR(50) AS SELECT FieldValue FROM [dbo].[Values] INNER JOIN [dbo].[Fields] ON [dbo].[Fields].FieldID = [dbo].[Values].FieldID INNER JOIN [dbo].[FormFields] ON [dbo].[FormFields].FieldID = [dbo].[Fields].FieldID INNER JOIN...

getting data from 3 tables via a recurring id

I have a database with 3 tables in this structure, CREATE TABLE `mailers` ( `id` int(11) NOT NULL AUTO_INCREMENT, `mailer_title` varchar(150) NOT NULL, `mailer_header` varchar(60) NOT NULL, `mailer_type` enum('single','multi') NOT NULL, `introduction` varchar(80) NOT NULL, `status` enum('live','dead','draft') NOT NULL, ...

Databind ListView datasource and joined tables

Hi all. I am not expert in ASP so i would be appreciated for the help. Issue connected to Entity FW, ListView Control, its Datasource and type convertion. I have the following LINQ query : RoutesEntities routesModel = new RoutesEntities(); LocalesEntities localesModel = new LocalesEntities(); ObjectQuery routesQuery = (ObjectQuery) f...

Count of a column with different rows

I want to join two tables (below) which i get. But then I want to do a count to see how many times each job_category is shown when contactId > 0 Any Help would be appreciated! Tablel : | JobPositionId | JobPositionName | JobDescriptionId | JobCategoryId | ContactId ----------------------------------------------------------------------...

How to SQL join two tables so that all entries of the left one are in the result even if there is no matching entry in the right one?

Hi everybody, I have spent several hours with this SQL problem, which I thought would be easy - I still think it should be, but obviously not easy enough for me (not an SQL expert). I would be glad if you could help me with this. I have stripped this down for this example. Imagine two tables: PRODUCT -ID -NAME REVIEW -ID -PRODUCT...

Double append in Xquery

I want to append the values of some XML tags, and then append again the result. But I'm having some trouble appending the results, so I only have one final result of appending. Imagine you have this: <nodeA> <nodeB> <item>1</item> <item>2</item> <yesno>blah</yesno> </nodeB> <nodeC> <thing>A</thing> </nodeC> </nodeA> <nodeA> <nodeB...

php mysql forum app...how to make sticky threads?

Hi I am working on a mysql/php forum app as part of a corporate intranet system. My problem is that while listing all threads under a particular forum, there could be more than one "sticky threads" which should show on top and the rest should after them sorted by date in descending order. Just need to get an idea about the sql query to u...

What is a fast way of joining two tables and using the first table column to "filter" the second table?

Hello! I am trying to develop a SQL Server 2005 query but I'm being unsuccessful at the moment. I trying every different approach that I know, like derived tables, sub-queries, CTE's, etc, but I couldn't solve the problem. I won't post the queries I tried here because they involve many other columns and tables, but I will try to explain ...

A different object with same identifier was already associated with the session error

Scenario: I have a customer object with lazy loading enabled. I use that throughout the program to call a list of customer for a listbox. It has relationships to the Division_Customer_Rel, Division_Email_Rel and Email_Address objects. All off the relationships have Lazy = true, Cascade = ManyRelationCascadeEnum.AllDeleteOrphan, Inverse =...