left-join

SQL Query - Use Like only if no exact match exists?

I'm having an issue with a query that currently uses LEFT JOIN weblog_data AS pwd ON (pwd.field_id_41 != '' AND pwd.field_id_41 LIKE CONCAT('%', ewd.field_id_32, '%')) however I'm discovering that I need it to only use that if there is no exact match first. What's happening is that the query is double dipping due to the use of LIKE, ...

Oracle joins ( left outer, right, etc. :S )

I knew stackoverflow would help me for other than know what is the "favorite programming cartoon" :P This was the accepted answer by: Bill Karwin Thanks to all for the help ( I would like to double vote you all ) My query ended up like this ( this is the real one ) SELECT accepted.folio, COALESCE( inprog.activityin, accep...

Help with a WHERE on a LEFT JOIN SQL Query

I'm trying to construct a query that will include a column indicating whether or not a user has downloaded a document. I have a table called HasDownloaded with the following columns: id, documentID, memberID. Finding out whether a user has downloaded a specific document is easy; but I need to generate a query where the results will look ...

Interesting Many-many sql join.

I have three related tables "A(id, val)", "B(id, val)", and a link table with a value "AB(aid, bid, val)" I am querying against B to bring back A values, for example: SELECT A.* FROM A INNER JOIN AB ON A.id = AB.aid INNER JOIN B ON AB.bid = B.id WHERE B.val = 'foo'; Every A has many B's and every B has many A's. And the catch that ...

Linq to Sql: Multiple left outer joins

I'm having some trouble figuring out how to use more than one left outer join using LINQ to SQL. I understand how to use one left outer join. I'm using VB.NET. Below is my SQL syntax. T-SQL SELECT o.OrderNumber, v.VendorName, s.StatusName FROM Orders o LEFT OUTER JOIN Vendors v ON v.Id = o.VendorId LEFT OUTER JOI...

Left outer join (join between three tables)?

I have a main table that I must get data from. I have a left outer join where the fields will match 40% of the time. And then I have another join where I need to match the data from table A with. This is the SQL in pseudo code. This query won't work. -- This is the part I want to do but doesn't work. AND H.COL3 = A.STATE???? I am ...

MySQL LEFT JOIN Problem - Missing LEFT column

Hi, I'm having problems with an SQL query used to display custom profile fields and any (optional) corresponding values. Here is the SQL query I'm using: SELECT pf.`id`, pf.`name`, pv.`value` FROM `profile_fields` AS pf LEFT JOIN `profile_values` AS pv ON (pf.`id` = pv.`field_id`) WHERE (pf.`site_id` = '0' OR pf.`site_id` = '%d') AND ...

MySQL Left Join Question

Hi Everyone, I have a MySQL Left Join problem. I have three tables which I'm trying to join. A person table: CREATE TABLE person ( id INT NOT NULL AUTO_INCREMENT, type ENUM('student', 'staff', 'guardian') NOT NULL, first_name CHAR(30) NOT NULL, last_name CHAR(30) NOT NULL, gender ENUM('m', 'f') NOT NULL, dob V...

joining 2 columns from Table1 to Table 2

How would you reference table1 columns to 2 columns in table 2 I created a table 'State' with 50 exact rows trying to relate (weddingState,contactState) in 'Wedding' table This is the statement that I created but it only joins the top WeddingState correctly - seems not to care about the INNER Join below it... SELECT * FROM weddings...

Left outer join on two columns performance issue

I'm using a SQL query that is similar to the following form: SELECT col1, col2 FROM table1 LEFT OUTER JOIN table2 ON table1.person_uid = table2.person_uid AND table1.period = table2.period And it's either way too slow or something's deadlocking because it takes at least 4 minutes to return. If I were to change it to this: SELECT col...

SQL order by and left outer join doesn't have correct order

I have a view that is joining two tables and ordering by the first table. Except that the order isn't correct. It misses an occasional record, and then at the end, most of those records exist in order, and then at that end, the rest of the records exist in order. So it has records such as 1 (most of the records in order) 2 4 5 6 7 ...

MySQL join query help

Basically I have a mysql query something like the following: mysql_query("SELECT n.title, v.value FROM posts n INNER JOIN votes v ON n.id = v.id"); And what I need to do is to grab the title from the posts table and the current vote value from the votes table. At the moment, votes aren't stored into the votes table unless a vote happ...

LINQ Inner-Join vs Left-Join

Using extension syntax I'm trying to create a left-join using LINQ on two lists that I have. The following is from the Microsoft help but I've modified it to show that the pets list has no elements. What I'm ending up with is a list of 0 elements. I assume that this is because an inner-join is taking place. What I want to end up with is ...

LINQ to Entities how to return all records from the Parent table?

I am unable to select all the records from the parent table using Linq to Entities. This is a simple DB design (image below): Image Link This is the exact output I want using Linq to Entities or Linq to SQL (image below): Image Link When I use Linq to Entities or Linq To Sql I can only get the records from the child table that ha...

optimize SQL query

What more can I do to optimize this query? SELECT * FROM (SELECT `item`.itemID, COUNT(`votes`.itemID) AS `votes`, `item`.title, `item`.itemTypeID, `item`. submitDate, `item`.deleted, `item`.ItemCat, `item`.counter, `item`.userID, `users`.name, TIMESTAMPDIFF(minute,`submitDate`,NOW()) AS 'timeMin' , ...

Linq to Entities How to return all records from Parent?

Employee table Employee_ID int Employee_Name varchar(50) Sales table: Sales_ID int Employee_ID int Sale_Amount money Standard SQL select Select * From Employee emp left outer join Sales s on s.Employee_ID = emp.Employee_ID Standard SQL Results (The exact results I want using Linq to Entites) ...

Linq with Left Join on SubQuery containing Count

I'm having difficulty translating sql to linq syntax. I have 2 tables (Category and CategoryListing) which reference each other with CategoryID. I need to get a list of all the CategoryID in Category Table and the Count of CategoryID for all corresponding matches in the CategoryListing table. If a CategoryID is not present in Category...

How do you perform an AND with a join?

I have the following data structure and data: CREATE TABLE `parent` ( `id` int(11) NOT NULL auto_increment, `name` varchar(10) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1; INSERT INTO `parent` VALUES(1, 'parent 1'); INSERT INTO `parent` VALUES(2, 'parent 2'); CREATE TABLE `other` ( `id` int(11) NOT NUL...

Left Outer Join and Exists in Linq To SQL C# .NET 3.5

I'm stuck on translating a left outer join from LINQToSQL that returns unique parent rows. I have 2 tables (Project, Project_Notes, and it's a 1-many relationship linked by Project_ID). I am doing a keyword search on multiple columns on the 2 table and I only want to return the unique projects if a column in Project_Notes contains a key...

PRE ANSI-92 Left Join Syntax (Sybase)

What is the syntax to specify a Left Join using pre ANSI-92 syntax (i.e. *=) when part of the Where clause has an 'equal to some constant' condition ? (in this case 100 is the constant) Example: SELECT t1.ID, t.* FROM (select * from SybaseTable where ID=1) t, SqlServerTable t1 WHERE t1.ID *= 100 and t1.SeqNo *= t.SeqNo In this ca...