Join them on id and then call ToList: var productResponses = from p in products join pd in productDescriptions on p.id equals pd.id select new ProductResponse { id = p.id, language = pd.language, // ... } var list = productResponses.ToList(); ...
python,join,network-programming,irc,hexchat
151.232.114.48 is server address that you are trying to connect.
sql,postgresql,join,aggregate-functions
Query The query is not as simple as it looks at first. The shortest query string does not necessarily yield best performance. This should be as fast as it gets, being as short as possible for that: SELECT p.username, COALESCE(w.ct, 0) AS won, COALESCE(l.ct, 0) AS lost FROM ( SELECT...
php,mysql,database,join,database-design
a view can be thot of as like a table for the faint of heart. https://dev.mysql.com/doc/refman/5.0/en/create-view.html views can incorporate joins. and other views. keep in mind that upon creation, they take a snapshot of the columns in existence at that time on underlying tables, so Alter Table stmts adding columns...
You say that the native SQL query runs in less than a millisecond. So stick with that. But that doesn't mean you have to wrestle with the List that comes from query.getResultList(). You can execute a native query (either from EntityManager.createNativeQuery() or from EntityManager.createNamedQuery() referring to a @NamedNativeQuery) and still...
mysql,sql,join,left-join,right-join
With the LEFT JOIN, you will be joining to the table (e.g. Traffic) even when there is not a record that corresponds to the Customers.id, in which case, you will get the null value for the columns from this table where there is no matching record. With the RIGHT JOIN,...
You shouldn't be using a LEFT JOIN, since you only want to update rows in table1 that have a matching row in table2. Try: UPDATE table1 AS t1 JOIN table2 AS t2 ON t1.id = t2.id SET t1.job = t2.job, t2.inserted = 1 WHERE t2.inserted = 0 DEMO...
php,mysql,json,codeigniter,join
That's a normal practice to sort result from SQL with PHP, so You're on the right way. $result = array(); foreach($query->result_array() as $row) { $result[$row['id']] = array( 'id'=>$row['id'], 'name'=>$row['name'], 'price'=>isset($result[$row['id']]['price']) ? array_push($result[$row['id']]['price'],$row['price']) : array($row['price']) ); } $this->output ->set_content_type('application/json') ->set_output(json_encode(array('products'=>array_values($result)))); ...
This is very bad form (using *) but you can simply keep adding tables with out specifying columns as so: select * from table1 inner join table2 on table1.id1 = table2.id2; add another table by changing the above to: select * from table1 inner join table2 on table1.id1 = table2.id2...
SELECT u.* FROM tblBridge b JOIN tblUsers u ON u.ID = b.tblUsersID JOIN tblAssignments a ON a.ID = b.tblAssignmentsID WHERE u.isAdmin = 'yes' AND a.name = 'searched assignment' It's MANY-TO-MANY Relation so you need select many-to-many table and join it to assigments and users also put in where condition for...
php,join,mysqli,subquery,left-join
ISSUE The page is slowing down (3-5 mins instead of seconds) in mysql queries which has multiple joins and sub-queries to retrieve massive amount of data (~10,000) in tables. SOLUTION - Used Index to the columns Added indexes and done various search queries, It is retrieving better. NOTES Indexes...
If your columns are fixed, you can do this with group by + case + max like this: select fname, lname, email, max(case when name = 'utm_medium' then value end) as utm_medium, max(case when name = 'utm_term' then value end) as utm_term from lead l join leadcustom c on l.id...
mysql,sql,database,join,foreign-keys
SELECT * FROM Player INNER JOIN Stats ON Player.SSN = Stats.SSN WHERE Player.DOB >= '1994-01-01' If you just want specific fields then specify these instead of SELECT *...
Lasted email recieved and count of unread (recieved). SELECT e.MAIL_NO, e.BIZ_ID, e.FROM_ADD, e.TO_ADD, e.EMAIL_SUBJECT, DATE_FORMAT(e.UPDATED_DATE,'%d %b %y, %I:%i %p') AS DATE, e.MAIL_STATUS, CONCAT(ufrom.USER_FIRST_NAME,' ',ufrom.USER_LAST_NAME) AS U_NAME,if(UNREAD_MESSAGE_COUNT is null,0,UNREAD_MESSAGE_COUNT) FROM EMAIL e LEFT JOIN USER_CONFIG ufrom ON ufrom.USER_ID = e.TO_ADD left join (SELECT COUNT(*) AS UNREAD_MESSAGE_COUNT,TO_ADD FROM EMAIL_MESSAGE inner join EMAIL...
What you probably need is a union of two sets: songs created by people user with id=6 follows songs reshared by people user with id=6 follows Since you haven't provided table schemas I'm using names you provided in the query. ( SELECT DISTINCT s.id, s.last_updated FROM follows f JOIN tracks...
You have to join communication twice, depending on medium. This should do the job (untested): SELECT con.contact_id, con.f_name, con.l_name, com1.value AS phone_number, com2.value AS email_address, con.creation_date FROM contact con LEFT JOIN communication com1 ON com1.contact_id = con.id AND com1.medium = 'PHONE' LEFT JOIN communication com2 ON com2.contact_id = con.id AND...
actionscript-3,ruby-on-rails-4,join,left-join
It seems that LEFT OUTER JOIN is what I am looking for. I created scopes in the models like this: class Artist < ActiveRecord::Base has_many :album_artists has_many :albums, :through => :album_artists scope :joins_albums, -> {joins('LEFT OUTER JOIN "album_artists" ON "album_artists"."artist_id" = "artists"."id" LEFT OUTER JOIN "albums" ON "albums"."id" = "album_artists"."album_id"')}...
sort -o df_ns.csv df_ns.csv && \ sort -o df_ip.csv df_ip.csv && \ join -t'|' df_ns.csv df_ip.csv > df_combined.csv Reference: http://linux.die.net/man/1/join...
If you need to group the counts by JobID and SubID you can try SELECT s.JobID, s.SubID, c.ClickCount, o.OpenCount FROM Staging_SendLog s LEFT JOIN (SELECT JobId, SubscriberId, COUNT(*) AS ClickCount FROM Staging_DailyClicks WHERE JobID IN (63809,89993) GROUP BY JobId, SubscriberId ) c ON c.JobID = s.JobID AND c.SubscriberId = s.SubID...
mysql,join,count,subquery,having
Please give this query a shoot SELECT COUNT(DISTINCT(u.id)) AS users_count FROM users AS u INNER JOIN ( SELECT user_id, COUNT(DISTINCT profile_option_id) AS total FROM profile_answers WHERE profile_option_id IN (37,86,102) GROUP BY users.id HAVING COUNT(DISTINCT profile_option_id) = 3 ) AS a ON a.user_id = u.id If you have lots of data...
What I think you should take note of here is that each post has a department and a postType. If you take a step back, you can select all posts that belong to a certain department and a certain postType like this: SELECT p.* FROM posts p JOIN department d...
sql-server,sql-server-2008,join
You are actually looking for LEFT JOIN: SELECT pd.fname, pd.lname, pp.drug_name, pp.drug_strength FROM patient_data pd FULL OUTER JOIN patient_prescr pp on pp.pid = pd.pid FULL OUTER JOIN formulary f on pp.med_id = f.id LEFT JOIN formulary_categories fc on f.category = fc.id AND fc.id in (34,36,37,38,5) WHERE pd.lname = 'Test' A...
You need rows, where student's ID is present in at least one of tables MATHS, ENGLISH. These queries gives output you wanted: select id, s.name, m.Marks1, e.Marks2 from maths m full join english e using (id) join student s using (id); ...or: select s.id, s.name, m.Marks1, e.Marks2 from student s...
self.dest_path = join(destination, self.new_name) # error here self.new_name is not a string, it's a tuple, so you can't use it as the second argument to join. Perhaps you meant to join destination with just the last element of self.new_name? self.dest_path = join(destination, self.new_name[1]) ...
sql,sql-server,join,sql-server-2014
Try this: SELECT A.sifKorisnikPK, IsNull(BrojDobrih,0) BrojDobrih, IsNull(BrojLosih,0) BrojLosih FROM (select distinct sifKorisnikPK from Rezervacija) A LEFT JOIN #LosaRez B ON A.sifKorisnikPK = B.sifKorisnikPK LEFT JOIN #DobraRez C ON A.sifKorisnikPK = C.sifKorisnikPK ORDER BY (IsNull(BrojDobrih,0) - IsNull(BrojLosih,0)) ...
Is this what you are looking for? SELECT <choose your columns here> FROM Orders o LEFT JOIN XrefOrdersStatuses x ON x.xos_order_id = o.order_id LEFT JOIN (SELECT xos_order_id, MAX(xos_datetime) AS maxdate FROM XrefOrdersStatuses GROUP BY xos_order_id ) xmax ON xmax.xos_order_id = x.xos_order_id AND xmax.maxdate = x.xos_datetime; The LEFT JOIN is only...
You can left join table2 and if the item isn't in table2 then it will be null: SELECT table1.item, CASE WHEN table2.item IS NULL THEN 'No' ELSE 'Yes' END AS ItemIsInTable2 FROM table1 LEFT JOIN table2 ON table1.item = table2.item ...
Consider adding an index on section.last_member, section.id. ALTER TABLE section ADD KEY(last_member, id); If they are innodb you can omit ID since it is already a PK....
You'll want to use a MERGE. It allows you to join two tables and specify how to update the values if they match. The general structure of a MERGE statement looks like: MERGE INTO driver_table USING other_table ON ( driver_table.column1 = other_table.column1 AND driver_table.column2 = other_table.column2 AND ... ) WHEN...
sql,postgresql,join,cloudfoundry,cascading-deletes
You may use WITH construction. with _deleted_service_instances as ( delete from service_instances where guid = 'daf67426-129b-4010-832c-692bcfe98f62' returning id ) , _deleted_service_instance_operations as ( delete from service_instance_operations op using _deleted_service_instances i where op.service_instance_id = i.id ) , _deleted_service_bindings as ( delete from service_bindings b using _deleted_service_instances i where b.service_instance_id = i.id...
mysql,join,database-normalization
Seems you missed one condition: hole_scores.user_id = users.id which you should add as ... INNER JOIN users ON users.id = score_card_user.user_id AND hole_scores.user_id = users.id .... ...
mysql,sql,select,join,cartesian-product
A Cartesian join joins every record in the first table with every record in the second table, so since your table has 7 rows and it's joined with itself, it should return 49 records had you not had a where clause. Your where clause only allows records where a's balance...
sql-server,tsql,join,view,alias
You have two instances of cauditnumber at position 1 and 3, you need to alias or remove one. CREATE VIEW KFF_Sales_Data_Updated AS SELECT CustSalesUpdated.cAuditNumber -- HERE ,CustSalesUpdated.Account ,CustSalesUpdated.cAuditNumber --HERE ,CustSalesUpdated.Name ,StkSalesUpdated.cAuditNumber as AuditNumber1 ,StkSalesUpdated.Code ,StkSalesUpdated.Credit ,StkSalesUpdated.Debit ,StkSalesUpdated.Description_1 ,StkSalesUpdated.Id ,StkSalesUpdated.ItemGroup ,StkSalesUpdated.Quantity ,StkSalesUpdated.Reference ,StkSalesUpdated.TxDate FROM...
Use windows functions: SELECT * FROM( SELECT Laptops.Laptop_ID, Laptops.Model_Name, ... Users.Firstname + Users.Lastname AS Name, Loans.Date_Loaned, row_number() over(partition by Laptops.Laptop_ID order by Loans.Date_Loaned desc) rn FROM Users INNER JOIN Loans ON Users.User_ID = Loans.User_ID RIGHT OUTER JOIN Laptops ON Loans.Laptop_ID = Laptops.Laptop_ID) t WHERE rn = 1 ...
Your variable $path contains an array. PS C:\> $path = 'C:\Temp' PS C:\> $res = Join-Path $path 'file.txt' PS C:\> $res.GetType().FullName System.String PS C:\> $res C:\Temp\file.txt PS C:\> $path = 'C:\Temp', 'C:\Windows' PS C:\> $res = Join-Path $path 'file.txt' PS C:\> $res.GetType().FullName System.Object[] PS C:\> $res C:\Temp\file.txt C:\Windows\file.txt...
So in this case, I used a subquery to get the initial results, and then used a join. SELECT table1.nisinfo.* FROM table1.nisinfo JOIN (SELECT distinct(fqhn) FROM table2.hosts WHERE package = 'bash') AS FQ ON ((SUBSTRING_INDEX(FQ.fqhn, '.', 1)) = table1.nisinfo.shortname); ...
You need to include the month in the second join condition. SELECT * FROM product_data A LEFT JOIN (SELECT prod_id, sale_date, sum(sales) FROM sales_data group by prod_id, MONTH(sale_date) ) AS B ON A.prod_id=B.prod_id RIGHT JOIN (SELECT prod_id, exp_date, media_expenditure --don't need to SUM since it's only 1 row per month...
This is wrong in your query $this->db->join('documents as D', 'D.name="declaration"','left'); you can compare column using where clause public function get_all_orders($user_id){ $this->db->select('ordersheader.*, customer.name as customerName,documents.date_make'); $this->db->from('ordersheader'); $this->db->join('customer', 'ordersheader.idCustomer = customer.idCustomer'); $this->db->join('documents', 'ordersheader.idOrder = documents.idOrder','left'); // $this->db->join('documents as D',...
For star schema you should join each table exactly with central table. Should be something like: select * from table_3 central join table_1 t1 on central.id = t1.central_id join table_2 t2 on central.id = t2.central_id join table_4 t4 on central.id = t4.central_id ; For case when table_1 - out of...
short story: as pointed out in the comment already, i was comparing strings with integers. long story: i didn't expect python to parse the id-columns of two input csv files to different datatpyes. df1.id was of type Object. df2.id was of type int. and i needed to find out why...
This will work assuming varchar: SELECT * FROM tbl1 JOIN tbl2 on concat(MatNr,'.1') = MART This will work assuming number: SELECT * FROM tbl1 JOIN tbl2 on MatNr = floor(MART) ...
Per your posted query it looks like there is a relation exists b/w the tables `post`.`post_id` = `comments`.`post_id` So you can try using a INNER JOIN like SELECT c.`comment_id`, p.`post_id`, c.`friendly_url`, c.`heading` FROM `post` p JOIN `comments` c ON p.`post_id` = c.`post_id` WHERE `username` = 'u1' ...
This should gives you the correct result: select * from TrackingDetails as a join ( SELECT tr.trackNo AS 'TrackNo', MAX(trD.DatePosted) AS 'Date_Time' FROM Tracking AS tr INNER JOIN TrackingDetails AS trD ON tr.trackNo = trD.trackNo WHERE tr.ClientID='client01' AND trD.trackNo IN ('xx000001','xx000002','xx000003') AND trD.DatePosted IS NOT NULL AND trD.Status IN (...
regex,list,join,ironpython,findall
You can simply do x="a85b080040010000" print re.sub(r"(.{2})",r"\1 ",x) or x="a85b080040010000" print " ".join([i for i in re.split(r"(.{2})",x) if i]) ...
c#,.net,sqlite,join,windows-phone-8
Create a class which has properties from both the class and use that classes. In your case it will have ITMID_PK,ITMNAME, description ,iCON and PRICE. Just keep the properties name same as column name. That class can be a separate class or Base class for these two classes. Edit: You...
Maybe not the best way to solve this problem, but I would join tableB two times with aliases and pick the value as needed like this : SELECT tableA.ISBN, author.value as Author, title.Value as Title FROM tableA left join tableB author on tableA.ID = author.tableA_id and author.key = "Author" left...
Try FULL OUTER JOIN. This is the sqlfiddle. It will produce the op you are expecting SQLFiddle select t1.years, t1.numOfppl, t2.years, t2.numOfppl from t1 full outer join t2 on t1.years=t2.years ...
This can easily be done with a join, all you have to do is use the item_id as the related column: SELECT i.id, i.name, t.name FROM items i JOIN tags t ON t.item_id = i.id; If you want to see all tags for an item grouped together, you can consider...
python,django,join,django-models,django-queryset
from datetime import date def add_years(d, years): try: return d.replace(year = d.year + years) except ValueError: return d + (date(d.year + years, 1, 1) - date(d.year, 1, 1)) today = datetime.date.today() ago18 = add_years(today, -18) ago25 = add_years(today, -25) # < 18 User.objects.filter(profile__birthdate__gt=ago18) # 18 <= and < 25 User.objects.filter(profile__birthdate__lte=ago18,...
You should definitely use contains because it will be executed in database and reduce the size of data that will be transferred between client and database. Also using foreach will reduce readability of your code
If I understood correctly this should be what you're looking for SELECT A.UserName, A.[Email ID], A.[Supervisor Email ID] FROM A INNER JOIN B ON A.UserCode = B.UserCode WHERE B.ACTIVE_FLAG = 'Y' ...
ruby-on-rails,postgresql,join,has-and-belongs-to-many
The equivalent active record query would be Technology.select('name').where("id IN (?)", ProjectTechnology.where("project_id = ?", 5).pluck(:technology_id)) Hope this helps!...
In order to perform a left join to df1 and add H column from df2, you can combine binary join with the update by reference operator (:=) setkey(setDT(dt1), A) dt1[dt2, H := i.H] See here and here for detailed explanation on how it works With the devel version (v >=...
Easier to verify if we build some DDL and fillwith sample data, but I think this would do it. It takes a full join to find records with a partial match and then filters out records with a full match. sqlfiddle.com CREATE TABLE Table1 (ID INT, FPRICE INT, FPRODUCT CHAR(1))...
sql,sql-server,join,pivot,key-value
SELECT Contacts.FirstName, Contacts.LastName, Properties.Name, ContactsExtra.PropertyValue FROM Contacts LEFT OUTER JOIN ContactsExtra ON Contacts.Id = ContactsExtra.ContactId LEFT OUTER JOIN Properties ON ContactsExtra.PropertyId = Properties.Id Okay, here's the update with a PIVOT (not UNPIVOT as I first thought)... SELECT * FROM ( SELECT Contacts.FirstName, Contacts.LastName, Properties.Name, ContactsExtra.PropertyValue FROM Contacts LEFT OUTER JOIN...
sql,sql-server,database,select,join
I think you want this kind of scenario Retrieve Drug_ID from Given Criteria (Brand_Name , Drug_Id, Generic Name) Select Record on base of That Retrieved Drug_ID SELECT distinct(Company_List.Company_Name), Drug_Details.* from Drug_Details , Company_List where Company_List.Company_ID=Drug_Details.Company_ID and Drug_Details.Drug_ID in ( select distinct(Drug_Details.Drug_ID) from Drug_Details where Drug_Details.Brand_Name like '%arnica%' or Drug_Details.Drug_ID in...
I believe this is what you're looking for: (This is assuming that 1 to 1 relationship) SET @UNITRN := 0; SET @TRANSRN :=0; SELECT A.`unit_date`, A.`unit_id`, B.`tran_date`, B.`tran_id`, A.`unit_sku` FROM (SELECT @UNITRN := @UNITRN + 1 AS ROWNUM, UNIT_DATE, UNIT_ID, UNIT_SKU FROM UNITS ORDER BY UNIT_SKU, UNIT_DATE ASC) A JOIN...
Yes, you certainly can. You need to qualify the table name with project name, i.e. projectname:dataset.table Here is an example of my joining one of my tables against table in publicdata project: select sum(a.is_male) from (select is_male, year from [publicdata:samples.natality]) a inner join (select year from [moshap.my_years]) b on a.year...
sql,ms-access,join,ms-access-2010,outer-join
Well one solution is to split the query so that it doesn't have these conflicting joins So create a query e.g q1 SELECT * FROM ( ( ITA INNER JOIN YEARS ON ITA.ID_YEAR = YEARS.ID ) LEFT JOIN ITA_INSPECTORS ON ITA.ID = ITA_INSPECTORS.ID_ITA ) and then create a 2nd query...
In your sql condition may be wrong i think, i modified that sql. select p.* FROM products p JOIN strings s ON p.title = s.id and s.language_en = "Product name" you can execute and see the result. Thank you....
You can use a LEFT JOIN on the project table to make sure that all projects appear in the result set even if they have no matching value in the source table. Projects from the project table which do not match will have NULL for their value. SELECT project.description AS...
The problem is here $zaznam[logoff]=='n'. There must be any column name where you have written $zaznam[logoff]. For Applying Left Join you should do something like this: SELECT COUNT(*) FROM app_tgp t1 LEFT JOIN app_training t2 on t1.idt = t2.id WHERE t1.idt='$zaznam[id]' AND ( t1.partic='' OR t1.partic='y' OR (t1.partic=='n' AND t2.your_column_name_here=='n'))...
You should: SELECT u.id, u.name, u.location_id, l.city, l.state FROM user u -- identify columns from user table with 'u.' LEFT OUTER JOIN location l -- identify columns from location with 'l.' ON u.location_id = l.id --- the join predicate when the two ids match WHERE l.state = 'Arkansas' -- other...
I tried this out, and I noticed a couple things. First: this is minor, but I think you're missing a comma in your -o specifier. I changed it to -o 1.1,2.1. But then, running it on just the fragments you posted, I got only three lines of output: 1|1 3|3...
sql-server,join,query-performance
You can try using LIKE WITH AgentRecordings AS ( SELECT a.name, r.recordingId AS rawrecordingid, r.filename, r.recordtime, CONCAT( 'C:\Recordings\' + a.name + '\' + CONVERT(NVARCHAR(4), DATEPART(yyyy, recordtime)) + '\' + CONVERT(NVARCHAR(2), DATEPART(m, recordtime)) + '\' + FILENAME, 'C:\' + SUBSTRING(a.name, 0, CHARINDEX(' ', a.name, 0)) + '-Recordings\' + CONVERT(NVARCHAR(4), DATEPART(yyyy, recordtime))...
You do have a complicated mess with those calculated fields. Why not join more directly? This query below leaves one '/' unaccounted for, but should tell you what I'm thinking of. SELECT t1.sb, left(st1.uchbegriff,7) & val(right(t1.suchbegriff,4)) AS suchbegriff2, t1.menge FROM kvks AS t1 INNER JOIN konf AS t2 WHERE (t1.suchbegriff...
sql-server,join,sql-update,concat,coalesce
You can use FOR XML to concatenate update x set attended_conf = (select e.key_value + '; ' as "text()" from TX_CUST_KEYWORD e where e.customer_no = x.customer_no and e.keyword_no = 704 order by e.key_value for xml path('')) from #work x ...
sql-server,join,group-by,sum,sql-update
Try this: update w set hist_amt_due = d.s from @work w join (select pmt_customer_no, sum(isnull(due_amt, 0)) s from @due_cte group by pmt_customer_no)d on w.pmt_customer_no = d.pmt_customer_no ...
UNION ALL SELECT field1, field2, field3 FROM table1 WHERE condition UNION ALL SELECT field1, field2, field3 FROM table2 WHERE condition; Or to simplify your WHERE condition SELECT * FROM ( SELECT field1, field2, field3 FROM table1 UNION ALL SELECT field1, field2, field3 FROM table2 ) WHERE condition; ...
Your cursor does select d.dlocof the table dept d, but it is named loc in your screenshot. CURSOR staff_cursor IS SELECT e.empno, e.ename, e.sal, d.dname, d.loc -- ... ...
Use MAX and GROUP BY SELECT MAX(m.kw), t.category FROM model m INNER JOIN type t ON m.type_id = t.id GROUP BY t.category OUTPUT MAX(m.kw) category 10 1 7 2 SQL FIDDLE: http://sqlfiddle.com/#!9/5d0df/5/0...
You can try $query = $this->Annonces->find('all', ['contain' => ['Adresses']])->where(['Annonces.id' => $id]); $annonce = $query->firstOrFail(); OR public function view($id = null) { if (!$id) { throw new NotFoundException(__('Annonce invalide!')); } $annonceEntity = $this->Annonces->get($id); $query = $this->Annonces->find('all', ['contain' => ['Adresses']])->where(['Annonces.id' => $annonceEntity->id]); $annonce = $query->firstOrFail(); $this->set(compact('annonce')); } ...
Here is an example for your usecase. First build up your structure (like in task description): CREATE TABLE table1( type varchar(20) ); CREATE TABLE table2( name varchar(20), color varchar(20), icon varchar(20), type varchar(20) ); Then insert some sample data: INSERT INTO table1 VALUES ('value1'); INSERT INTO table1 VALUES ('value2'); INSERT...
ruby-on-rails,ruby-on-rails-4,join,migration,has-and-belongs-to-many
The create_join_table command in migrations is new in Rails 4. This: create_join_table :questions, :sub_topics do |t| # other commands end is essentially shorthand for this: create_table :questions_sub_topics do |t| t.integer :question_id, null: false t.integer :sub_topic_id, null: false # other commands end You can add additional columns in the block; you...
mysql,sql-server,join,distinct
What if you add a GROUP BY to your existing query like SELECT customer_info.customerid, customer_info.firstname, customer_info.lastname, customer_info.city, customer_info.state, GROUP_CONCAT( purchases.item), customer_favorite_colors.favorite_color FROM customer_info INNER JOIN purchases ON customer_info.customerid = purchases.customerid INNER JOIN customer_favorite_colors ON purchases.customerid = customer_favorite_colors.customerid GROUP BY customer_info.customerid, customer_info.firstname, customer_info.lastname, customer_info.city,...
mysql,sql,performance,join,subquery
Whatever comments you supply to this answer, I will continue with helping try revision for you, but was too much to describe in a comment to your original post. You are looking at services within a given UNIX time range, yet your qualifier on service_id in sub-selects is looking against...
You need to move the condition in the where clause to the on clause. When no rows match, then the column has a value of NULL and the WHERE condition fails: SELECT B.bookTitle, L.lendBorrowerName AS takenName, count(L.lendStatus) AS taken FROM books as B LEFT JOIN lends AS L ON B.bookId...
sql,join,greatest-n-per-group,amazon-redshift
You can do this with Postgres' DISTINCT ON: select distinct on(userId) userId, tstamp, event from events order by userId, tstamp desc; For Redshift, you might be able to this variant from one of my previous answers: select userId, tstamp, event from ( select userId, tstamp, event, row_number() over (partition by...
The problem comes from the fact you store string representation of the brands in brandResponses, thus you collect them as strings in the final JSON. Note that for producing JSON, there's no big gain in using Groovy. Building Maps/Lists with MEL and serializing them to JSON Objects/Arrays with the json:object-to-json-transformer...
select CONCAT((select username from table1 t1 where t.user_id=t1.user_id=t.user_id) ,'-',(select username from table1 t1 where t.user_id=t1.user_id=t.user_id)) from table2 t OR select CONCAT(t1.username,'-',t3.username) from table2 t2 inner join table1 t1 on t1.user_id=t2.user_id inner join table1 t3 on t3.user_id=t2.user_id ...
If the column is stored as a date, then you would use: SELECT * FROM wp_simple_login_log l INNER JOIN wp_users u ON u.id = l.uid WHERE l.time >= date_sub(curdate(), interval 3 month); If it is stored as a string, then do an explicit conversion: SELECT * FROM wp_simple_login_log l INNER...
sql,sql-server,select,join,where
The conceptual order of query processing is: 1. FROM 2. WHERE 3. GROUP BY 4. HAVING 5. SELECT 6. ORDER BY But this is just a conceptual order. In fact the engine may decide to rearrange clauses. Here is proof. Lets make 2 tables with 1000000 rows each: CREATE TABLE...
Just have a look at Below query, Hope this may help you! Later you can Modify Accordingly This will join to all 5 tables. Here I kept Location as the Left Table so that All locations will be Displaying after firing this query and matching Id's will be matched to...
Given we have data presented in the question we can write something like this: This will result in a table with 3 columns. Let's assume the resulting table will be called A. SELECT id, SUBSTRING_INDEX( 'name' , ' ', 1 ) AS a, SUBSTRING_INDEX(SUBSTRING_INDEX( 'name' , ' ', 2 ),'...
Try it the other way round. Start with the info table and then LEFT JOIN multiple times to antwoorden: select i.id,i.start_bt1,i.start_bt2, a1.ant as "bt1_vl1", a2.ant as "bt1_vl2", a3.ant as "bt2_vl1", a4.ant as "bt2_vl2" from info i left join antwoorden a1 on a1.id = i.id and a1.vl=1 and a1.bt=1 left join...
Your first query: SELECT *, @MidMatch := IF(LEFT(l.middle,1)=LEFT(d.middle,1),"TRUE","FALSE") MidMatch, @AddressMatch := IF(left(l.address,5)=left(d.address,5),"TRUE","FALSE") AddressMatch, @PhoneMatch := IF(right(l.phone,4)=right(d.phone,4),"TRUE","FALSE") PhoneMatch, @Points := IF(@MidMatch = "TRUE",4,0) + IF(@AddressMatch = "TRUE",3,0) + IF(@PhoneMatch = "TRUE",1,0) Points FROM list l LEFT JOIN database d on IF(@Points <> 0,(l.first = d.first AND l.last = d.last),(l.first = d.first...
database,postgresql,join,sql-update
Unfortunately, you can't use the JOIN...ON syntax to join to the table that is being updated to the rest of the tables. You have to move those join conditions into the WHERE clause, and remove the updated table itself product_template from the FROM clause. UPDATE product_template SET list_price = product_uom.factor...
php,mysql,join,mysqli,left-join
Syntax error (comma missing before lr_users.date_joined): $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); ...
Try this select m.id, m.major, count(distinct s.id) as number_of_student , count(distinct c.id) as number_of_classroom from major m left join classroom c on (m.id = c.major_id) left join student_classroom s on (s.classroom_id = c.id and c.major_id = m.id and s.status = 'active') group by m.id order by m.id ...