left-join

Fetching join using @JoinTable with SQLQuery

I have a mapped entity with a property "latestHistory", which is mapped through a join table, like: class Record { @OneToOne(cascade = { CascadeType.MERGE, CascadeType.PERSIST, CascadeType.REMOVE }, fetch = FetchType.LAZY, optional = true) @JoinTable(name = "latest_history_join_view", joinColumns = { @JoinColumn(name = "record_id")...

GROUP_CONCAT and LEFT_JOIN issue - Not all rows returned

Let's say my DB Scheme is as follows: T_PRODUCT id_product (int, primary) two entries: (id_product =1) , (id_product =2) T_USER id_user (int, primary) id_product (int, foreign key) name_user (varchar) two entries: (id_product=1,name_user='John') , (id_product=1,name_user='Mike') If I run a first query to get all products with their...

Retrieving MYSQL Join values from PHP

I have two or more tables which hold values under the same column name. Now when I join these two tables the column names stay the same. When retrieving these values in PHP ($row['name']) I encounter problems since the column 'name' is used twice. Are there any possible means of separating these two columns inside the query? SELECT ...

LINQ Query to return a list of records that do NOT have an associated record in the join.

What is the most efficient way to return a query where the result set is those records that do NOT have an associated record? I am using LINQ and Visual Basic and have been trying to construct a left outer join and then filter on those records whose id is nothing or null in the associated table but am so new to this that I can't get any...

What is going on inside when I run left join?

I have a question regarding left join on SQL: I would like to know how SQL servers perform left join? Let's say I have two tables. PEOPLE id name PHONE id person_id phone When I execute: select name, phone from people left join phone on people.id = phone.person_id ...I would like to know how SQL servers process the...

MySQL: Is there a way to LEFT JOIN conditionally? (or similar?)

I'm writing a query to get ALL of the products in the products table, and the sale price for each product IF a record exists for that item in the specials table. What I'm looking for is something like: SELECT * FROM products P IF (S.specials_date_available <= NOW() AND S.expires_date > NOW()) { // The sale has started, but has not yet ...

Why does my MySQL MATCH() AGAINST() query fail with columns from LEFT JOIN'd databases?

I've got a MySQL query somewhat like the following: SELECT * FROM products LEFT JOIN descriptions ON products.DescriptionID = descriptions.ID WHERE MATCH (name, overview, specs) AGAINST ('ram'); All columns I'm trying to search with MATCH() AGAINST() are FULLTEXT, but I get the following error when testing in phpMyAdmin: #1210 - I...

MySQL Select Statement - Two Tables, Sort One Table by Count of Other Table

So I have built a voting system for a custom post system i wrote. I want to be able to sort by "most voted", "Most liked", etc. I have two tables. Entry: ID, Title, Post Vote: ID, EntryID, Result I want to be able to query the vote table for each entry and see how many vote's there are, and then sort the entry's by how many vote's e...

Limiting Access by Permission

Hi, thanks for viewing this. I have a db that has users, roles & user_roles. What I am trying to achieve is a login that will select users who have Admin or Associate permissions. The login then uses name and password to permit access. My SQL syntax thus far is - SELECT * FROM users LEFT JOIN ON user_roles ON user.id=user_roles.user...

MYSQL fetch 10 posts, each w/ vote count, sorted by vote count, limited by where clause on posts

I want to fetch a set of Posts w/ vote count listed, sorted by vote count (e.g.) Post 1 - Post Body blah blah - Votes: 500 Post 2 - Post Body blah blah - Votes: 400 Post 3 - Post Body blah blah - Votes: 300 Post 4 - Post Body blah blah - Votes: 200 I have 2 tables: Posts - columns - id, body, is_hidden Votes - columns - id, post_i...

MYSQL DATE function running insanely slow in LEFT JOIN

When adding the line: LEFT JOIN core_records_sales as sales ON DATE(appointments.date) = DATE(sales.date_sold) To my query, it boosts the time for the script to run from about 8 seconds, to 2-3 minutes. Would there be some data causing this problem or am I not implementing the function correctly? I need to use DATE() because I need ...

SQL to Linq translation with inner join and 2 left joins (with sub-query)

Hi there! I have the following query which works fine in SSMS. Im using LinqPad (C#) but really puzzling to succeed with the left outer join in LinqToSql: SELECT DISTINCT A.LocID, V1.PrfValue AS pID, V2.PrfValue AS sID, D.DivisionManager, A.IsApproved, A.DateCreated FROM dbo.Locations AS A INNER JOIN dbo.D...

Is an outer join possible with Linq to Entity Framework

There are many examples of outer join using Linq to Sql, all of them hinging on DefaultIfEmpty() which is not supported with Linq to Entity Framework. Does this mean that outer join is not possible with Linq to Entity using .NET 3.5 (I understand that DefaultIfEmpty is coming with 4.0 --- but that's not an option at this time for me) C...

Left JOIN faster or Inner Join faster?

So... which one is faster (NULl value is not an issue), and are indexed. SELECT * FROM A JOIN B b ON b.id = a.id JOIN C c ON c.id = b.id WHERE A.id = '12345' Using Left Joins: SELECT * FROM A LEFT JOIN B ON B.id=A.bid LEFT JOIN C ON C.id=B.cid WHERE A.id = '12345' Here is the actual query Here it is.. both return the same...

Slow query - Multiple joins and a lot of data

I'm sure there is a better way I could be doing this. I have three main tables with large amounts of data to run through: records_main, sales, and appointments. Each with close to 20,000 records. I need to join these three tables as well as a few others that arn't two large. $SQL = "SELECT DISTINCT appointments.id AS aid, appointme...

LEFT JOIN for current row in primary table

This is the question about JDBC. I have next task. I'm iterating through rows from table A and for some current row I want to execute some query against table B in context of current row from A. For example, if I have some query like SELECT B.description FROM A LEFT JOIN B ON A.ID = B.refId then I want to get all results where B.refI...

How to deal with DateTime field when using LEFT OUTER JOIN in LinqToSql

Hi, I have 2 tables, Employee and Absence. I want to do a left outer join so that I get all employees and any associated absences. In LINQ2SQL I can use the following to create the LEFT OUTER JOIN: from e in Employees join a in Absences on e.EmployeeID equals a.EmployeeID into ae from x in ae.DefaultIfEmpty() select new { FullName = ...

SQL join that displays left table even if right table doesn't match where

I have 2 tables, defined as such: CREATE TABLE `product` ( `pid` SMALLINT( 5 ) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, `name` VARCHAR( 50 ) NOT NULL, `description` TEXT, `qty` SMALLINT( 5 ) UNSIGNED NOT NULL DEFAULT '0', `category` ENUM( '1', '2', '3', '4', '5', '6', '7', '8' ) NOT NULL DEFAULT '1', `price` DECIMAL( 7, 2 ) UNSIGNE...

Left join not pulling through nulls where expected.

I have a query that looks something like this (I've changed the table names): select @user_id , isnull(ur.rule_value, isnull(manr.rule_value, def.rule_value)) [rule_value] , isnull(urt.name, isnull(manrt.name, def.name)) [rule_type] from (select @user_id [user_id] , rule.rule_value , rule_type.name fr...

Get max of two dates from two different tables in teradata - scenario?

I have two tables table1 and table2. Table2 is having less number of rows than table1. In these two tables there are two date columns caldate1 in table1 and caldate2 in table2. So now I need to join on these two tables and get the maximum of the two date columns and keep that in new table. But if we inner join on these two tables the tab...