FAQ Database Discussion Community


PHP displaying data from four tables in one query (ie: LEFT JOIN)

php,mysql,sql,mysqli,left-join
I have three tables: I want to display 'Event Details' which shows attending employee details (listo f employee ids from the 'attending_employees table > corresponding employee details form 'employee' table), what team they belong to (from the club_teams table) and the event details from the 'club_events' table). Currently I am...

SQL query: different rows into same row

sql,sql-server,left-join
I am a beginner and I am using SQL Server for this particular query. I have to combine data of two rows into one row. The difference is the people who conducted the inspection. I have no problem getting all data. But I cannot put two rows of data into...

Haskell Implement left outer join

haskell,left-join
I am working on a uni assignment to implement a left outer join in Haskell using only the prelude function definitions. I have tried using list comprehensions: ljoin :: Eq a => [(a,b)] -> [(a,c)] -> [(a,b, Maybe c)] ljoin xs ys = [if x1 == y1 then (x1,x2, Just...

Use Join In Delete Zend 2

delete,zend-framework2,left-join
How can i use join in zend 2 delete query? For example : DELETE t1 FROM t1 LEFT JOIN t2 ON t1.id=t2.id WHERE t2.id IS NULL; I won't write query directly. $Delete = new Delete(); $Delete->from("example"); $Delete->join // join is not available Sincenrely....

left join two tables in same DataContext LinQ in c#

linq,left-join
I have two table in the same DataContext as follows. Table PersonnelInfo { personnelId, personnelName , description, deathMonthYear, updatedBy, updatedAt } Table PersonnelInfoOther { personnelId, personnelName , updatedBy, updatedAt } I define a class as follows: public class PersonnelInfoAll { public short personnelId{get;set;} public string personnelName { get; set; }...

How to use join to fill missing values of a column - Python Pandas?

python,join,pandas,left-join,concatenation
To be concrete, say we have two DataFrames: df1: date A 12/1/14 3 12/2/14 NaN 12/3/14 2 12/2/14 NaN 12/4/14 NaN 12/6/14 5 df2: B 12/2/14 20 12/4/14 30 I want to do kind of a left outer join to fill the missing values in df1, and generate df3: date...

LEFT JOIN query not returning all rows in first table

sql,left-join
Using management studio for SQL Server 2008 R2. Trying to do a LEFT JOIN and I need to return all rows from the first table regardless of the row being able to tie with the second table. Not sure I explained that right. This is what I got right now:...

mySQL JOIN from different TABLE different RECORD

mysql,sql,select,left-join
I'm trying to join two tables, but inputs are not the same. On table Category category_id is an integer. But on table url_alias category is a string like category_id=15. I've tried: SELECT * FROM category c LEFT JOIN url_alias ua ON ( ua.category = 'category_id=c.category_id') No luck. How can I...

make a left outer join in javascript

javascript,jquery,performance,left-join
I have an array of objects in javascript (D3) and I need to remove every object of which a certain attribute is present in another array of objects attribute, i.e. a left outer join: http://www.pinaldave.com/bimg/March09UG/left%20join%20null.jpg I managed to do it myself with 2 loops but it's quite slow. And I...

Get max date from table in join

mysql,join,left-join
My first table is a members table: member_id, name, address, email 1 jon 122 any [email protected] 2 amy 111 gee [email protected] My second table is test levels accomplished: MemberHistoryTable member_id, level, date 1 a 2007 1 b 2008 1 c 2009 1 d 2010 2 a 2007 2 b 2008...

merge or left join R

r,merge,left-join
I have three datasets that has same Index column (Key), StudentId, and all have same number of observations, I repeat same number of observations, but different columns and different values for each observation. Dataset 1 Id Lab 1 33 . . 2334 98 Dataset 2 Id Sports 1 83 ....

Any way to substitute left join or it is necessary?

mysql,sql,left-join
I would like to know if there is any better way to do the following query or if there is any necessity to make it better? (Considering that the DB load is not that big). The only criterion is that the 3 "variables" have to be included as AND, and...

SQL Outer Join - improper execution

mysql,sql,join,left-join,outer-join
I am working with learning SQL, I have taken the basics course on pluralsight, and now I am using MySQL through Treehouse, with dummy databases they've set up, through the MySQL server. Once my training is complete I will be using SQLServer daily at work. I ran into a two-part...

mysql join query taking too many resources

mysql,join,debian,left-join
I am using python2.7 and mysql5.5 on debian. With this query mysql use 100% cpu: mysql> SHOW FULL PROCESSLIST; +-----+------------------+-----------------+---------+---------+------+--------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Id | User | Host | db | Command | Time | State | Info |...

MySQL - LEFT JOIN - How do it get the value that i need

php,mysql,select,left-join
i have three tables: "playerstat", "player" and "game". the "playerstat" table is empty at the first time, the "players" already have a choice of players and the table "game" also has an entry where the stats and the players can be assigned.   now i want that the players with...

MySQL query with left join, count and group with 3 tables

mysql,sql,group-by,left-join
I have 3 tables where I am trying to put a join query like below. The below is the Section table containing 3 sections. Section ***************************** * section_id * section_name * ***************************** * 1 * A * * 2 * B * * 3 * C * ***************************** The below...

mysql sum returning wrong value

mysql,wordpress,sum,left-join,inner-join
Hi there so i have this problem in the following query, inside wp_wti_like_post i have number of rows with similar post_id and with value column as 1 or -1. So let's say for post with id 727 i have only one row , so the sum should be 1, but...

Filter RHS items in LINQ Group Join

vb.net,linq,left-join
In LINQ, I need to left join (group join) 2 lists of objects using some criteria that are able to be expressed as equijoins (so I can use the On clause), and other criteria that are relative. In SQL, the equivalent is something like this (using a ludicrous example, where...

Update Property in List of objects based from a list of ids

c#,linq,left-join
Two lists. One is a object representing a group of people I sent a email to. For brevity lets say the structure is public class EmailSent { public int Id {get;set;} public string Email {get;set;} public bool HasResponse {get;set;} } That has a backing table of every email I've sent...

how to filter repeated rows based on date in Mysql?

mysql,left-join
I have two tables table 1 name : depot_tracking ------------------------------------------------- depot_tracking_id | cont_details_id | date | ------------------------------------------------- 1 | 4 | 05-02-2015 | 2 | 1 | 25-03-2015 | 3 | 3 | 05-04-2015 | 4 | 2 | 15-02-2015 | 5 | 3 | 05-05-2015 | 6 | 2...

Laravel 5, Derived table in join clause?

left-join,laravel-5,query-builder,derived-table
I have this query: SELECT * FROM blog LEFT JOIN ( SELECT blog_id, AVG(value) as blog_rating FROM blog_ratings GROUP BY (blog_id) ) T ON T.blog_id = blog.id; I do not know how to write this with Eloquent. For Example: Blog::select("*")->leftJoin( /* Here goes derived table */ )->get() How do I...

Hibernate join fetch does an N+1, how to fix it?

hibernate,join,left-join,fetch
I have this query: @NamedQuery( name = "org.mygovscot.stars.model.UserNeed.findAll", query = "SELECT un FROM UserNeed un " + "LEFT JOIN FETCH un.services " ) With this mapping from Service to UserNeed: <set name="userNeeds" table="service_userNeed"> <key column="service_id"/> <many-to-many column="userNeed_id" class="UserNeed"/> </set> and this mapping from UserNeed to Service: <set name="services" table="service_userNeed"> <key...

LEFT JOIN with condition is not returning data desired

php,mysql,join,left-join
I would like to do a left join using MYSQL. Currently I have a table something like this: CREATE TABLE `books` ( `bookId` int(11) NOT NULL, `bookTitle` varchar(100) NOT NULL, `bookAuthor` varchar(100) NOT NULL, `bookStatus` tinyint(1) NOT NULL, `bookDeleteFlag` tinyint(1) NOT NULL ); CREATE TABLE `lends` ( `lendId` int(11) NOT...

Getting two values from same Joined table

php,mysql,left-join
I have two tables: procedures countries Table procedures has two columns destination_country_iso origin_country_iso Table countries iso name Im trying to join up the two tables in order to get both destination an origin country name from countries table. SELECT * FROM draft_procedures AS drp LEFT JOIN countries AS c1 ON...

How to formulate query to show all courses taken by a person

mysql,group-by,left-join,right-join
I'm having trouble formulating a MySQL query correctly. Everything I've tried doesn't give me what's needed, or gives a syntax error. I have three tables: Clients, Courses, and CoursesForClients. The Clients table just has basic coordinates for a person: ID, Name, Address, email, etc. +----------+-----------------------------+------+ | ClientID | Name |...

LEFT JOIN 2 INNER JOIN Tables Without Subquery

mysql,left-join
I have 3 Tables: Jobs, Users, Applications. I'd like a list of all jobs and optionally "any" applications submitted per each job but only if I have data for the user who submitted the application. I vaguely remember seeing some obscure syntax in which 2 tables are joined to a...

left join with subquery with one result

sql,tsql,subquery,left-join
Here is a query: SELECT k.[mg_KarId] AS [mg_KarId], k.[SymKar] AS [SymKar], k.[OpiKar] AS [OpiKar], k.[Status] AS [Status], kmlg.SymLok AS Lok_GLS1, kmlk.SymLok AS Lok_KRS1, kmlw.SymLok AS Lok_WLS1 FROM dbo.[mg_vv_Kar_All] AS k WITH (NOLOCK) LEFT JOIN ( SELECT kml.SymKar, kml.SymLok FROM dbo.mg_KarMagLok kml WHERE kml.Mag LIKE 'GLS1' ) kmlg ON k.SymKar =...

PHP Select Count and Left Join where

php,mysql,join,left-join
Im not sure if it´s possible to combine SELECT COUNT + Left Join on this way but it would be very helpful for me. This is my code whitout Left Join attempt: $pp = mysql_result(mysql_query("SELECT COUNT(*) FROM app_tgp WHERE idt='$zaznam[id]' AND ( partic='' OR partic='y' OR (partic=='n' AND $zaznam[logoff]=='n'))"), 0);...

MySQL LEFT Join with multiple possibilities and Inner Join

mysql,sql,join,left-join,inner-join
I don't know if a similar question have been asked, but I looked fow more than hour on mysql in stackoverflow My problem is, i have multiple tables and I need to join them with both left join and inner join in mysql Entity table : id (key) | entityName...

How to join 2 tables with select and count in single query

sql,oracle,left-join
I need to join 2 tables (Person and PersonLine). The result should contain id and name column from Person table and count of personlineid column from PersonLine Table for each id. But sql query returns count of all personlineid. Can anyone help to form the sql. Person: ID NAME AGE...

How to do left joins in LINQ on multiple fields in single join

c#,linq,left-join,sql-to-linq-conversion
I am trying to do this simple sql query to LINQ. But its give me error. Here is the SQL query that need to conver to LINQ DECLARE @groupID int SET @groupID = 2 SELECT * FROM dbo.Person p LEFT JOIN dbo.PersonGroup pg ON ( p.PersonID = pg.PersonID AND pg.GroupID...

Unexpected behavior with merge and dplyr's left_join

r,merge,left-join
I have noticed an unexpected behavior with the merge function in base R as well as the left_join function of dplyr. Below is a minimal example of the data: df1 <- read.table(text="serialno var1 pos_var1 1 C001 NA NA 2 C002 NA NA 3 C003 0.1790000 1 4 C004 NA NA...

display Count of one column from another table even when the count is zero

sql,oracle,left-join,nvl
I have two tables A and B. In Table A (Oracle sql), an unique column(not primary key) code may have some records in table B. Example: Code "A" has 3 entries, Code "B" has 2 entries and code "C" has 0 entries in table B. I want the query to...

LEFT JOIN tables and null values that dont exist in the right

mysql,sql-server,left-join
I have two tables, Customers and customerTowns. I would like to store the town name in customerTowns and link to it from townID stored in Customers |Customers| |townID | |customerTowns| |townID | |townName | And my sql is as followed, SELECT townName, Customers.townID FROM customerTowns LEFT OUTER JOIN Customers ON...

Double left join in dplyr to recover values

r,left-join,dplyr
I've checked this issue but couldn't find a matching entry. Say you have 2 DFs: df1:mode df2:sex 1 1 2 2 3 And a DF3 where most of the combinations are not present, e.g. mode | sex | cases 1 1 9 1 1 2 2 2 7 3 1...

Query results are multiplied by 2

mysql,left-join
3 tables. app_forms, app_results, app_exhibitions I want to get the form ID, form name, the count of the results and the exhibitions linked to the form. At the moment I got this query: SELECT f.id as id, f.name as formname, count(res.form_id) as resultcount, group_concat(b.name separator ', ') as exhibitionname FROM...

MySQL Conditional Left Join With Default

mysql,left-join,conditional
Is there a way to make a conditional left join, which will left join on a default value if a match is not found? I have a client table and a preference table. Record 0 in the preference table is my global default preference. There are cases in which a...

SQL problems, JOINS

sql,ms-access,left-join
Im having some problem with my SQL code. My assignment is to present some information about teachers(Lärare.personnummer) who don't have teach the course "Java2"(Kurstilfälle.kurs). The code is right but my problem is that there is one teacher who don't have teach any course(kurs). So the information about her is not...

Select Last Record from LEFT JOIN Subquery

mysql,subquery,left-join
I'm playing with Subqueries and I need to display the most recent note from a customers_notes table in a LEFT JOIN. The query I've attempted is returning an error. What am I doing wrong below? SELECT c.*, n.datesubmitted FROM `customers` c LEFT JOIN (SELECT datesubmitted FROM `customers_notes` ORDER BY datesubmitted...

MS ACCESS SQL Join Syntax

sql,ms-access,join,access-vba,left-join
I can get this query to run if I remove the Join, but once I add the join, I get the following error: Run-Time error '3135': Syntax error in Join Operation sourceDB = "C:\sourcedb.accdb" SQL = "SELECT e1.lid " & _ "FROM (eventlog e1 IN '" & sourceDB & "'"...

MySQL - Issue with GREATEST() and LEFT JOIN returning NULL

mysql,sql,left-join
I have a problem with a GREATEST() and LEFT JOIN statement that looks like it is related to different versions of MySQL. I have a table that stores a school_id and school_type. There are 4 schools types, and for each school type I have separate table. I have a query...

Show null counts with a condition of <= 2

sql,oracle,count,null,left-join
Currently trying to find diagnosed diseases that have been diagnosed between 0-2 times. i am able to show how many times each disease has been diagnosed, including 0, but as soon as i try to view the ones that have a count of 0-2, my query doesn't work. this gets...

Duplicates on Left Join

sql-server,left-join,duplicate-removal
This is a SQL Server question. I'm linking three tables from the same database and keep getting duplicate values. Tables REF_Plan_Dictionary and REF_Plan_Assign_Default will produce duplicates on their own because some of the columns will reference multiple sub-options in other columns. Table dev_OUT_MasterEmp will only produce one record per EmpID....

Create view with aggregated columns from three levels of nested tables

sql,postgresql,view,left-join,aggregate
I'm using Postgres 9.4 and I have 3 tables: truck , container and container_metadata. A truck can have many container and a container many container_metadata. I'll add more description later with the tables below (I did my best creating them), so here goes: A truck can contain many containers referred...

LEFT JOIN on multiple tables in “FROM” part

mysql,left-join,multiple-columns
I'm unable to find an exact example on stackoverflow, so here is my problem. In MySQL 4.x it works fine, but 5.x seems not to like the following: SELECT some stuff.. FROM schools, member , applications LEFT JOIN courses ON courses.id = applications.course_id LEFT JOIN staff ON staff.id=member.staff_id This section...

Return rows where no match is found in another table

sql,table,join,left-join
Couldn't find a question that had this exact problem. I have 2 tables, I want to return the rows from one table where the row doesn't exist in the second table. I will make an example to show. The first table: teamName United Athletic Football Rugby The second table: Username...

SQL Duplicate Rows Multiple Joins

sql,duplicates,left-join
I'm pretty much a noob when it comes to SQL so any help would be appreciated. I have a large data set that I am filtering through for a hospital. I am pulling data from 6 different tables and one of my tables has duplicate rows for each visit. I...

LEFT OUTER JOIN does not work

sql,left-join,notin
Good Morning, I am on a SQL learning tour and trying to create a small database with a few queries to gain experience. Two databases where used, Person {id, name, age} and Knows {id, guest1_id → Persons, guest2_id → Persons} The query should result in a list of names of...

mysql select where left join syntax

php,mysql,left-join
I have a problem. I have 2 database tables. table 1 people: +----------+--------------+ | id | name | +----------+--------------+ | 1 | johanalj | | 2 | hjgjhggjh | +----------+--------------+ table 2 images of people: +----------+--------------+----------------+ | id | url | people_ID | +----------+--------------+----------------+ | 1 | 3765345.png | 1...

join 2 tables and get some columns from 1st table and max timestamp value from second table

sql,join,db2,max,left-join
I have a employee table empid empname status 1 raj active 2 ravi active 3 ramu active 4 dan active 5 sam inactive I have another table called facilities empid timestamp 1 2014-12-28 1 2015-05-05 1 2015-06-05 2 2015-05-03 2 2015-06-04 3 2015-02-01 I want my result like empid empname...

QueryDSL Left Join with additional conditions in ON

java,left-join,querydsl
Is it possible to do the following query in QueryDSL? SELECT p.* FROM parts_table p LEFT JOIN inventory_balance_table i ON (p.part_no = i.part_no AND i.month = MONTH(CURRENT_DATE) AND i.year = YEAR(CURRENT_DATE)); Inventory balance stores inventory data for every part number/month/year; I need the only the data for the current year...

nesting joins right to left in postgres

postgresql,join,left-join
I have a big query, but it all boils down to this: SELECT * FROM user LEFT JOIN tableA ON tableA.user_id = user.id JOIN tableB ON tableB.a_id = tableA.id Now, I get too few results. If the combination of user with (tableA x tableB) does not exist, I still want...

MYSQL Join Two tables with same data

php,mysql,left-join
I have two tables member and alumni. alumni table can have the same member in more than one row with a different year field. I want to select all the data from both the table. The table given is: Alumni: id, regd, name, status, year 1 1 kim inactive 2013...

MySQL / PDO - Select from other table (Join statement)

php,mysql,pdo,left-join,inner-join
I have a PDO statement, that selects some data from some different tables. Although, I am unsure on how I can select data from yet another table: SELECT c.forum_id as category_id, c.forum_name as category_name, t.forum_id as id, t.forum_name as name, t.forum_desc as description, (SELECT COUNT(*) FROM forum_topics WHERE forum_id=t.forum_id AND...

Which JOIN type in multiple joins

mysql,sql,join,left-join,right-join
I have 4 tables that I want to be joined. Customers Traffic Average Live I want to insert joined data of these tables to "Details" table. The relationship between the tables is here: each of Traffic, Average and Live tables have a "cid" that is the primary key of "Customers"...

Perform SQL query and then join

sql,sqlite3,left-join
Lets say I have two tables: ticket with columns [id,date, userid] userid is a foreign key that references user.id user with columns [id,name] Owing to really large tables I would like to first filter the tickets table by date SELECT id FROM ticket WHERE date >= 'some date' then I...

join table and where column similar to other column, while changing column value SQL

sql,sql-server,tsql,left-join
I have an issue with my code. This is an SQL issue. I need to combine two tables. At present I have this code: SELECT calldate,clid, '44'+dst AS [NumberDialed],dstchannel,billsec,disposition,accountcode,uniqueid,cnam, callRateImport.Description, callRateImport.Code, callRateImport.Day FROM callLogImport LEFT JOIN callRateImport ON callLogImport.dst LIKE callRateImport.Code + '%' WHERE callRateImport.Code(Select Code FROM callRateImport WHERE callLogImport.dst...

Data.Table Merge - Result is larger than input Datatables

r,merge,left-join,data.table
I have two data tables. df1, ref_df Internal Structures are as follows: Classes ‘data.table’ and 'data.frame': 10153986 obs. of 18 variables: $ chr_no : chr "1" "1" "1" "1" ... $ pos : int 238 324 340 353 355 357 380 420 435 571 ... $ ref : chr "C"...

Mysql syntax error on LEFT JOIN query [closed]

php,mysql,sql,mysqli,left-join
I am trying to execute a LEFT JOIN query however I am getting a syntax error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'LEFT JOIN lr_users ON lr_capped_members.user = lr_users.user_id WHER' at...

SQL find records from table 1 that are not in table 2 OR in table 2 with condition

mysql,sql,left-join,exists,impala
I see many examples on how to find records that are not in another table, but I'm having a lot of trouble finding records that are either not in table 2, or are in table two, but the freq column value is less than 10%. I'm first joining a list...

sql to LINQ with left join group by and sum() throws exception

sql,linq,group-by,left-join,sql-to-linq-conversion
I have a query which I converted to LINQ expression. But throwing Null exception. Objective: Payment Type can have three values (0,1,2).I need to query total sum amount for all currency for all three PaymentType. Also if the record for specific currency doesn't exist for either of three values of...

dplyr join define NA values

r,left-join,dplyr,na
Can I define a "fill" value for NA in dplyr join? For example in the join define that all NA values should be 1? require(dplyr) lookup <- data.frame(cbind(c("USD","MYR"),c(0.9,1.1))) names(lookup) <- c("rate","value") fx <- data.frame(c("USD","MYR","USD","MYR","XXX","YYY")) names(fx)[1] <- "rate" left_join(x=fx,y=lookup,by=c("rate")) Above code will create NA for values "XXX" and "YYY". In my...

LINQ many to many relationship - get a list of grouped objects

c#,linq,group-by,many-to-many,left-join
I have a bit of a complicated one (for me anyway): I have 2 entities in a many to many relationship. Projects {Project_Id, ProjectName} Users {User_Id, UserName} Projects-Users {Id, Project_Id, User_id} A project can be assigned to more users. Now I want to retrieve the List of all projects (listed...

R Data frame column update

r,dictionary,data.frame,left-join,no-match
I have a dataframe with names I have a second dataframe with a dictionary of names and the sex of those names I want to check if the name is in the dictionary if it is then add the sex from the dictionary table to the names dataframe My code...

mysql left join results in 4 records instead of 1

mysql,sql,left-join
I know I am doing something silly here Should this left join not give only one record? My sql fiddle: http://sqlfiddle.com/#!9/251f7/1 Also, If a kind soul can help me put the productName in one field separated by commas required output id1 id2 productName a1 x1 copier,headphone,cable,monitor ...

Primary key is null when left join is used

ruby-on-rails,ruby,left-join,rails-activerecord
When I use this request @questions = Question.joins("left join answers as a on a.user_id = #{current_user.id} and a.question_id = questions.id").select('questions.*, a.*') Question.id is null. Does anyone know why? Maybe it needs an alias or something like that. My schema: class Answer: belong_to: User belongs_to: Question end class User: has_many: answers...

Invalid reference to FROM-clause entry for table “mdl_user”

sql,postgresql,join,left-join,moodle
I'm trying to build a report of all users and their score for a particular course for a Moodle v2.7 database (which uses PostgreSQL 9.3. I am getting the following error and have looked at questions and other examples that produce the same error, but none of them provide an...

How does Left Join / IS NULL eliminate records which are there in one table and not in the other?

mysql,sql,left-join,isnull
I am having a tough time to understand why does LEFT JOIN / IS NULL eliminate records which are there in one table and not in the other. Here is an example SELECT l.id, l.value FROM t_left l LEFT JOIN t_right r ON r.value = l.value WHERE r.value IS NULL...

Count rows after specific one

mysql,sql,subquery,left-join
A table users has three columns: id, name, pass. Another table logins has user_id column, an isright boolean (tinyint) column which says whether the login was successful or not and a date column. I need a simple left join to get the user's name and his password (1), the last...

Condense multiple rows into one single, for many instances

php,sql,pdo,left-join
I have a table called Orders, and another called orderProducts. orderProducts contains a CustomerID field, listCustomerOrders() then looks for matching rows in the DB. The output returned would be as followed: array() { [0]=> object(Basket)#3 (11) { ["ProductName"]=> string(17) "Green Pesto Bagel" ["completeDate"]=> string(19) "2015-02-25 11:00:00" } [1]=> object(Basket)#4 (11)...

How to modify my existing query to take into account a particular column from a custom made table?

sql,sql-server,join,left-join
I'm using SQL Server 2014 and and extract of my current query stands as shown below. My question relates to the last SELECT statement of my query. Right now it works fine and the output is as follows: ReservationStayID ........... XBEV01 XBEV02 XBEV03 XFOOD1 XFOOD2 215 ........... 1500 0 0...

Combining UNION ALL and OUTER JOIN in SQL Statement

mysql,sqlite,left-join,union-all,right-join
I've got two queries, one uses a union all function. select * from ( Select ticket_id as Ticket_Num , name as Name , approved_at as Approval_Date , price as Price , quantity as Quantity , (price * quantity) as Cumulative_Price From purchase_list_items union all select '' , '' , datetime('now')...

LINQ C# Join with Left-Join result

c#,linq,join,left-join
I'm trying to do a left-join with the result of another left-join but in some cases it returns me null reference error (obviously), is there any way to do this without using foreach after select? var result = from f in foo from ex in exp.Where(w => w.id == f.idFoo).DefaultIfEmpty()...

How to use CASE in JOIN statement to join two tables based on condition?

php,mysql,join,left-join,case
I have three tables : email, customer_new and customers_old. Now in email table there is a field called type that contains 0 or 1. So if type contains 0 then I want to fetch name column from customer_new table else from customer_old table. For this I made below mysql query...

SQL - Joining together summed data from multiple tables

sql,sql-server,left-join,query-performance,nested-query
I've been having the following problem for days. I've kinda managed to resolve it, but the performance is what bothers me. Basically I have one table of persons, and two tables (debt and wealth) with references to person. debt/wealth tables can have multiple rows that refer to same personID. I...

MySQL - Join on comma-separated field

mysql,join,left-join,inner-join
I would like to do some new stuff (for me it's new, bc. I'm just a MySQL-beginner) and I did not find a solution for this. I got these entries in my database: mytable_items id | title | catids 1 | test | 32,14 mytable_categories id | title 32 |...

Query 2 tables join

php,mysql,left-join
I want to get all the films whose genre is equal to ID_GENRE = 8. This is the query that I'm doing: http://l4c.me/fullsize/2-tablas-1434140362.png $query_GetSimilar = sprintf("SELECT * FROM z_movie,z_movie_genre ORDER BY z_movie.visits DESC WHERE z_movie_genre.id_genre = 8 LIMIT 18"); $GetSimilar = mysql_query($query_GetSimilar, conect::dbconect()) or die(mysql_error()); $row_GetSimilar = mysql_fetch_assoc($GetSimilar); $totalRows_GetSimilar =...

Caused by: java.sql.SQLSyntaxErrorException: [SQL0205] Column MITMAS_MMCONO not in table OOLINE in schema

spring,hibernate,jpa,left-join,spring-data
I have 3 entity classes Ooline: @Entity @IdClass(OolineId.class) @NamedQuery(name="Ooline.findAll", query="SELECT o FROM Ooline o") public class Ooline implements Serializable { private static final long serialVersionUID = 1L; @ManyToOne(fetch = FetchType.LAZY) @JoinColumns({ @JoinColumn(name="mitmas_mmcono" , referencedColumnName="mmcono"), @JoinColumn(name="mitmas_mmitno" , referencedColumnName="mmitno") }) private Mitmas mitmas ; @ManyToOne(fetch = FetchType.LAZY) @JoinColumns({ @JoinColumn(name="mitfac_m9cono",...

How can I write a SQL query left joining 2 tables that lists all entries on the left table, but only once?

sql,left-join
My question is a little complicated so I'm not sure I summed it up so well in the title, but it's like this-- I have two tables: a BOOKDETAIL table with data on a lot of books, and a STOCKDIARY table with the sales history on books we've ordered before....

SQL : Left Join multiple time the same table with different types , Duplicates lines adding the value in it

sql,select,join,left-join
My problem is this query : select distinct o1.id_personne, o1.id_table, tel.li_num_tel as Telephone, mail.li_url as EMail, fax.li_num_tel as Fax from table o1 Inner join personne p1 on o1.id_personne = p1.id_personne left join table_adresse a1 on o1.id_table = a1.id_table and a1.co_type_adresse like 'TEL' left join table_adresse a2 on o1.id_table = a2.id_table...

MySQL: Remove JOIN for Matched Row if 2nd Round of Criteria Not Met

mysql,join,left-join,match
CONDENSED VERSION I'm trying to join a new list with my existing database with no unique identifier -- but I'm trying to figure out a way to do it in one query that's more specific than matching by first name/last name but less specific than by all the fields available...

MySQLi LEFT JOIN query is throwing a syntax error

php,mysql,join,mysqli,left-join
I have two tables 'lr_users' and 'lr_ranks' with the following table structures: lr_users: user_id(pk), username, rank(fk), job_id(fk), date_joined lr_ranks: rank_id(pk), name $qry= mysqli_query($con, "SELECT lr_users.username, lr_users.rank, lr_users.job_id, lr_users.date_joined lr_ranks.name AS rankname FROM lr_users LEFT JOIN lr_ranks ON lr_users.rank = lr_ranks.rank_id")or die(mysqli_error($con)); $rows = mysqli_num_rows($qry); Above is my PHP code for...

mysql query retrieve too slow from 10,000 of data (Query Optimizing)

php,join,mysqli,subquery,left-join
10,000 of data in memberships, members and payments table. Retreiving the query is too slow, while searching a particular payment status in each members latest payment. SELECT m.id AS member_id, m.full_name, m.unit, m.street, m.block, m.country, m.postal_code, cat . * , cat.id AS cat_id, mem.membership_num, mem.id AS membership_id FROM memberships mem...

yii2 gridview not showing all the left join values using hasMany retionship

gridview,left-join,yii2,relation
Model Search : $query = Countries::find()->joinWith(['states']); $dataProvider = new ActiveDataProvider([ 'query' => $query, ]); $dataProvider->setSort([ 'defaultOrder' => ['doc_date'=>SORT_DESC], ]); if (!($this->load($params) && $this->validate())) { return $dataProvider; } Model : public function getStates() { return $this->hasMany(States::className(), ['state_id' => 'state_id']); } I need result like Id Country State 1 India State 1...

Query to join two tables by mapping third table without returning all records from third table in Oracle

sql,oracle,join,left-join
I have 3 tables: Table Maths: ID Marks1 (1 , 80) Table English: ID Marks2 (2 , 85) Table Student: ID Name (1 , Raj) (2 , Sam) (3 , John) I need a SQL query (Oracle) to get the following output: ID Name Marks1 Marks2 (1 , Raj ,...

Sql Server Multiple Joins with Aggregate

sql-server,left-join,aggregate-functions
I'm trying to fix a bug in inherited code. This query is meant to bring back an amex_meal_amount_total of $33, but it is giving $99. The problem is with the second join - there are three associated items in the EE table that is making the aggregate sum up three...

Joining 3 tables returning mixed results (mysql)

mysql,left-join
I have 3 tables: EMPLEADOS ALTAS BAJAS This is some example data: EMPLEADOS +-------------+------------+-------------+ | id_empleado | nombres | ap_paterno | +-------------+------------+-------------+ | 1020 | JUAN | FUENTES | +-------------+------------+-------------+ | 1021 | JOSE | FLORES | +-------------+------------+-------------+ ALTAS +-------------+--------------+-------------+-----------+ | id_alta | id_empleado | fecha_alta | tipo | +-------------+--------------+-------------+-----------+...

MySQL LEFT JOIN is causing “duplicate” rows

php,mysql,left-join,inner-join
I have tables "customers" and "plans" and I want to list all customers regardless they have a plan or not. I'm using query to do it SELECT customer.name, plan.goal FROM customer LEFT JOIN plan ON plan.customerid=customer.customerid ORDER BY customer.name I also want to see the goal (plan.goal) with the customer...

Getting correct MAX result, but not the correct COUNT values in MySQL for message board

php,mysql,count,left-join
This is my first time here on Stack. I'm attempting to build a message board (forum) using PHP and MySQL. I'd like to display a table in PHP with the following information: one column displaying all current forums another column displaying the total number of threads and posts per forum....

MySQL left join excludes non-matching records?

mysql,left-join
When I execute this MySQL query, I get correct results as expected: select h.ID AS ProductID, h.ProductTitle, u.Username, ht.ProductType, hd.Difficulty-- , ifnull(sum(pv.UpVote),0) - ifnull(sum(pv.DownVote),0) AS NetVotes from Products h join Users u on h.UserID = u.ID join ProductTypes ht on h.ProductTypeID = ht.ID join ProductDifficulties hd on h.ProductDifficultyID = hd.ID...

Activerecord Rails 4 perform something like a join that still returns rows that don't have the association

actionscript-3,ruby-on-rails-4,join,left-join
If after reading my question you have a suggestion for a better title, please add a comment. I was having trouble succinctly saying what I wanted. I have a situation like this. class Artist < ActiveRecord::Base has_many :album_artists has_many :albums, :through => :album_artists end class Album < ActiveRecord::Base has_many :album_artists...

how to combine inner join, sub queries, sum and where condition

sql-server,left-join
suppose I have a table like the below: Employee No | Wage Type|Wages|Company Code| For each employee no, I want to create a table such as the following (I know the number of wage types) Unique Employee No|Sum(Wage Type 1)|Sum(Wage Type 2)| Company Code I am using the following code,...

T-SQL Left Join using “or” operator

sql-server,tsql,left-join
I have the following query where I would like to pull in either or both on a match. There can be more than one ID or HIC for a member. So if the member ID = member ID then pull the max (loaddate) for the most current HIC Likewise if...

LEFT JOIN - Sort and Group

php,mysql,sorting,left-join,group
I get only one row of the elements and the matching categories. $stmt = $conn->prepare("SELECT lt_category.id, lt_category.title, lt_category_elements.category_id, lt_category_elements.title as elementitle, lt_category_elements.text FROM lt_category LEFT JOIN lt_category_elements ON lt_category.id = lt_category_elements.category_id"); category 1 element1 (is there) element 2,3,4,5 (is missing) etc. category 2 element1 (is there) element 2,3,4,5 (is missing)...

Using left_join from dplyr with merge variables specified

r,left-join,dplyr
I'm trying to use the left_join from dplyr with the merge keys specified The function works fine, but the issue i'm having is that I would like to set the merge keys outside of the function (i.e. as variables client_1_key and client_2_key). Unfortunately I'm having issues with getting the syntax...

Mysql query ignoring the row if child row from left join table doesn't exists

php,mysql,wordpress,left-join
Hi there so first of all , i'm not that expert in mysql queries. i have tow tables wp_posts and wp_wti_like_post and i'm doing LEFT JOIN on wp_wti_like_post ON wp_posts.ID = wp_wti_like_post.post_id and SUM(wp_wti_like_post.value) < 2 BUT if there is no rowin wp_wti_like_post with id of post from wp_posts then...

How to joint Two customs Queries with two Joins in only One Query in MySQL

mysql,sql,select,subquery,left-join
The Queries are working perfectly each one separatedly: SELECT asf.surface_name, am.* FROM atp_matchs_to_surfaces m2s LEFT JOIN atp_surfaces asf ON m2s.surfaces_id = asf.surfaces_id LEFT JOIN atp_matchs am ON am.matchs_id = m2s.matchs_id; SELECT att.tournament_type_name, am.* FROM atp_matchs_to_tournament_type m2s LEFT JOIN atp_tournament_type att ON m2s.tournament_type_id = att.tournament_type_id LEFT JOIN atp_matchs am ON am.matchs_id...

how to filter repeated rows using DATE in Mysql

mysql,join,left-join
i have 4 tables and i am using LEFT JOIN to merge the tables and all work fine. This is the query i am using SELECT cont_details.* FROM depot_tracking LEFT JOIN depot_details ON depot_tracking.depot_details_id = depot_details.depot_details_id LEFT JOIN cont_details ON depot_tracking.cont_details_id = cont_details.cont_no WHERE depot_details.depot_city = "maa" AND depot_tracking.status =...

MySQL query that performs LEFT JOIN but includes parent record's ID?

mysql,left-join
I have a table of articles with some articles having children (but only 1 level of children). I want to have a view that shows articles grouped by their parent ID. This is what I have so far. select `articles`.`id` AS `group_id`, `child`.`id` AS `article_id`, `articles`.`created` AS `date` from `articles`...

Average grade of student temporary table in SQL Server

sql,sql-server,sql-server-2008,left-join,select-into
I have a database with the following tables: Students_T (SNo, SDedc, SAddress) Courses_T (CNo, CDesc) CoursesRegister_T (CRNo, CR_CNo, CR_SNo, CRGrade) I need to represent the following data: For each student show: Student desc, Course desc, Course grade, Grades average And I need to do this for every course that the...