Tag Info

New answers tagged

0

Ok, but how to modify this query to get all users that did not vote in some/any election returning user_id and election_id ?


1

then u can use this one SELECT * FROM tablex WHERE Number<>123 OR VAT<>15


-2

Try this one, SELECT * FROM tablex WHERE Number<>123 AND VAT<>15


2

If I interpret your question correctly: "Pick all rows where a has one of two given values, and b only exists in combination with one of them." You can use any of the standard techniques laid out in the referenced answer. Help with this SELECT in the same table Just restrict your base table to the two given a. For instance SELECT * FROM tbl t1 WHERE ...


1

I think this solution is pleasingly simple: SELECT coalesce(a1.a, a2.a) AS a, coalesce(a1.b, a2.b) AS b FROM ( SELECT a, b FROM tbl1 WHERE a = 1 ) a1 FULL OUTER JOIN ( SELECT a, b FROM tbl1 WHERE a = 2 ) a2 ON (a1.b = a2.b) WHERE a1.a IS NULL or a2.a IS NULL; What it does: self-join tbl1, filtering it into two distinct sets, a1 with values a=2, and ...


0

I haven't run this so please forgive the odd typo: with Table1 as ( select b from tbl where a = 1 ) , Table2 as ( select b from tbl where a = 2 ) , Added as ( select * from Table1 UNION ALL select * from Table2 ) , Subtracted as ( select * from Table1 INTERSECT ALL select * from Table2 ) , Unmatched as ( select * from ...


4

If your pairs are unique, as in the example you gave, you want the values of b that appear only once, and this would be a solution: SELECT a,b FROM tbl1 WHERE b in ( SELECT b FROM tbl1 GROUP BY b HAVING (count(b)) = 1 ) You can see it in this fiddle http://sqlfiddle.com/#!15/c82d3/3


0

My original solution: SELECT a, b FROM tbl t1 WHERE t1.a = 1 AND NOT EXISTS ( SELECT 1 FROM tbl t2 WHERE t2.a = 2 AND t2.b = t1.b UNION SELECT a, b FROM tbl t1 WHERE t1.a = 2 AND NOT EXISTS ( SELECT 1 FROM tbl t2 WHERE t2.a = 1 AND t2.b = t1.b );


2

You're mixing apples and oranges, my friend. Your query is equivalent to: Select p.ID, p.Date, s.Name AS [Seller's Name], b.Name AS [Buyer's Name] FROM tblPurchase AS p LEFT JOIN tblPerson AS s ON p.SellerID=s.ID LEFT JOIN tblPerson AS b ON p.BuyerID=b.ID; .. with one exception: Your subquery won't work if it returns more than one record ...


0

Simpler, just remove the click_value from the inner group and SUM it. No need for the outer sum: SELECT s.shop_id as shop_id, s.shop_name as shop_name, s.pay_type as payment_schedule, res.num_clicks, res.cost FROM Shop AS s INNER JOIN ( SELECT c.shop_id count(c.id) as num_clicks, SUM(click_value) as cost ...


3

select a.host, b.host, b.volume from hosts as a join hosts as b on b.volume like '%'||a.volume||'%' and a.host <> b.host; Instead of LIKE you can also use position which might be easier to read: select a.host, b.host, b.volume from hosts as a join hosts as b on a.host <> b.host and position(a.volume in b.volume) > 0 ...


2

below should help you : CREATE TABLE dbo.Trading ([Ticker] int, [iOrder] int, [Price] int, [Amount] int) ; INSERT INTO dbo.Trading ([Ticker], [iOrder], [Price], [Amount]) VALUES (1, 1, 90, 180), (1, 1, 89.5, 179), (1, -1, 91, 91), (2, 1, 5, 20), (2, -1, 4, 16), (2, -1, 3, 9) ; SELECT Ticker ,sum(CASE ...


0

SELECT GREATEST ( IFNULL(tableb.create_time,FROM_UNIXTIME(18000)), IFNULL(tablea.create_time,FROM_UNIXTIME(18000)) ); It just so happens that FROM_UNIXTIME(18000) is '1970-01-01 00:00:00'


0

The query select username from USERS where username like 'test%' .. returns more than one row. If you limit it to return only the first row, or rephrase the WHERE clause to return only a single row, you should be ok.


1

When you use the values clause in Oracle, you can only provide comma-separated values. If you want to select one or more rows from another table, you have to use this syntax: insert into <table>(<col1>,<col2>,...,<coln>) select <col1>,<col2>,...,<coln> from ...; In your case: insert into MEMBERS(GR_id, username) ...


0

I just figured it out: SELECT EXISTS( SELECT `clockcards`.`date` FROM `clockcards` JOIN `users` ON `clockcards`.`user` = `users`.`id` WHERE `users`.`rfid` = 1234567890 AND `clockcards`.`date` = '2014-08-08' ) AS `clockedin` Returns TRUE if clocked in, or FALSE if not.


0

updated class_teachers table to class_teachers_section to include section. the idea is a class can include different sections with different teachers. for example a grade 1 PE class can include all sections ... teacher for boys will be different from girls select class.name as classname, class.hours as hours, teachers.first_name as teacherFirstName, ...


0

What was useful for me was something like: mysqldump -u db_user -p db_name table_name --no_create_info \ --lock-all-tables --where 'id in (SELECT tn.id FROM table_name AS tn \ JOIN related_table AS rt ON tn.related_table_id = rt.id \ WHERE rt.some_field = 1)' > data.sql From http://krosinski.blogspot.com/2012/12/using-table-join-with-mysqldump.html


5

The performance difference in your query is well explained by MG. I am going to address this: I've always believed that * queries should be avoided specifically for performance reasons. select * carries no particular penalties by itself, it is problematic when misused. In a single-table query it works just fine. now join that table to another with 20 ...



Top 50 recent answers are included