You can just change the last part to have a subquery (I will use in so you can have multiple values in the WHERE clause) SELECT a.CLIENTID, a.CLIENTNAME, a.CREATEDATE, b.AREACODE, b.PHONENUMBER FROM CLIENT a INNER JOIN PHONE b ON a.CLIENTID = b.CLIENTID WHERE b.AREACODE IN /* You can put your query inside the ()s */ … Read more
You have a comma too much at the end here: $sqlPrescriptionQuery .= “concat(tas.vch_first_name, ‘ ‘, tas.vch_last_name) as vch_resource_name, “; It should be: $sqlPrescriptionQuery .= “concat(tas.vch_first_name, ‘ ‘, tas.vch_last_name) as vch_resource_name “; solved Cannot find sql syntax error [closed]
select id, column_1, column_2, case when id in (12,22,456,56) then 1 else 0 end as zzz from tablename The IN clause in the case statement can be replaced with a table name having those id’s like: select id, column_1, column_2, case when id in (select distinct id from table2) then 1 else 0 end as … Read more
If I understand correctly, you basically need SUM() of all the Get_val values where either ID_One or ID_Two is 44. Afterwards, you want to display all the unique combinations of ID_One and ID_Two with the “overall sum”. We can get the “overall sum” in a Derived Table; and then CROSS JOIN it back to the … Read more
Something like this might work. I’m not sure on the efficiency yet – it may depend on the amount of hits that you’re looking for: create type HitBlocks as table ( HitIndex int not null ) go create procedure FindMaxCover @Hits HitBlocks readonly as ;With DecomposedBlocks as ( select (HitIndex/8)+1 as ByteIndex,POWER(2,(HitIndex%8)) as BitMask from … Read more
This should do: SELECT * FROM peopleaffiliation pa LEFT OUTER JOIN addresses addr ON pa.addressid = addr.addressid LEFT OUTER JOIN addresstypes addtyp ON addtyp.addresstypeid = addr.addresstypeid WHERE (addr.preferredaddress = 1 ) OR( COALESCE(addr.preferredaddress, 0) = 0 AND addtyp.addresstypeconst=”Work” ) 0 solved Select preferred address if exists otherwise select ‘work’ address
This should do it: SELECT e.[Name], e.[Year], e.[Status] FROM employee AS e INNER JOIN ( SELECT [Name], MAX([Year]) FROM employee GROUP BY [Name] ) AS m ON e.[Name] = m.[Name] AND e.[Year] = m.[Year]; solved Find most recent Status for each Employee, based on annual updates
Of course this is specific to the data in the insert. If one user has a longer name than the field you are entering it into for example and another doesn’t, the first will error and the second will not. This is why it is critical to understand your data when setting up tables. and … Read more
This gets your desired results. Not sure why you would desire them though! (particularly the completely made up id values 9 and 10) ;WITH Employee AS ( select 1 as ID, ‘Alex’ AS Name UNION ALL select 2 as ID, ‘James’ AS Name UNION ALL select 3 as ID, ‘John’ AS Name UNION ALL select … Read more
MINUS is the set minus operator in Oracle and is called EXCEPT in SQL Server and doesn’t seem to exist in MySQL (at least I couldn’t find it). But you don’t seem to want a set minus but an arithmetic minus. Try SELECT (SELECT avg(sal) FROM employee WHERE dept=”SC”) – (SELECT avg(sal) FROM employee WHERE … Read more
You can do conditional aggregation : select id, sum(case when type=”x” and location = ‘L’ then amount else 0 end) as sumofXandL, . . . from table t group by id; 0 solved Making a query with a single row out
As I wrote in my comment, I would not set the existing value to null. Instead, a computed column seems like a better option to me. Also, varchar(1) is the second worst data type you can choose (nvarchar(1) is even worst). First, if you know you only ever going to have a fixed length string, … Read more
It’s normal. When you do : $codering = [‘RF-013.12’]; Is same as : $codering = array(‘RF-013.12’); So if you try to use your array like string, php write array instead. If you wanna store [something] (a string with [] character) you need : $codering = “[something]”; If you realy need to store an array, ou … Read more
You have a table called reports and you are extracting a table that consists of the id, user, item_id, and created fields. This new table will only have rows that contain the max value of created for each distinct item_id. This part extracts the fields you want: select a.id, a.user, a.item_id, a.created From a table … Read more
Maybe something like this: SELECT b.id FROM iv_stock_details a, iv_stock_details b WHERE a.id + 1 = b.id AND b.stock_stat_no >= a.stock_stat_no AND b.stock_stat_no < a.stock_end_no AND b.stock_end_no <= a.stock_end_no AND b.stock_end_no > a.stock_stat_no; SQLFiddle: http://sqlfiddle.com/#!2/94722/7 solved Compare nth row with n+1 th row and if it lies in range of n th row print n+1th … Read more