Tag Info

New answers tagged

2

I've written this in SQL Server so you may have to translate to MySQL but this should work for you. CREATE TABLE #ExcludeList (ExcludeVal varchar(50)) CREATE TABLE #VideoList (VideoName varchar(500)) INSERT INTO #ExcludeList VALUES ('Prob1'),('Prob2'),('DontUse1') INSERT INTO #VideoList VALUES ('This is a prob1'), ('This is clean'),('This is ...


2

Your query is correct and it should return one row (5,6) DECLARE @table1 TABLE ( a INT, b INT ) DECLARE @table2 TABLE ( x INT, y INT ) DECLARE @table3 TABLE ( x INT, y INT ) INSERT INTO @table1 ( a, b ) VALUES ( 1, 2 ), ( 5, 6 ), ( 7, 8 ) INSERT INTO @table2 ( x, y ) VALUES ( 1, 0 ), ( 0, 0 ), ( 9, 5 ) ...


2

Try this select used_for_lookup.id, used_for_lookup.datetime, fid_table.fid_important_string, did_table.did_important_string from used_for_lookup join fid_table on fid_table.id = used_for_lookup.fid join did_table on did_table.id = used_for_lookup.did where datetime between '2014-10-09' and '2014-10-11';


1

The following SQL performs the join you need. select used_for_lookp.id ,used_for_lookup.datetime ,fid_table.fid_important_string ,did_table.did_important_string from used_for_lookup join fid_table on fid_table.id = used_for_lookup.fid join did_table on did_table.id = used_for_lookup.did


1

It is not possible to view accessed information unless and untill General Query Log is enabled in Mysql you can enable general query log in mysql 5.5 as follows, which would help you in future OS : Ubuntu 12.04.1 LTS MySQL version : 5.5.28 1. Locate and edit my.cnf file. We need to change two variables in this file to enable general query log. a. ...


0

The following script list all the databases and its tables that where updated within the last 5 days. You can change the interval as per your requirement. USE information_schema; SELECT DISTINCT TABLE_SCHEMA , TABLE_NAME FROM TABLES WHERE UPDATE_TIME IS NOT NULL AND UPDATE_TIME > NOW() - INTERVAL 5 DAY AND TABLE_SCHEMA <> ...


3

GROUP BY and the HAVING clause are what you are looking for. GROUP BY allows you to use aggregate functions without having to only have 1 row of data as you currently do. Here, we're going to compute the count of all the users and to total bandwidth for all the users at that company. I don't quite get your tipping point so the logic there may be off. I guess ...


2

Try this: select emp.*, loc.* from employee as emp inner join location as loc on emp.locationId = loc.id where not exists ( select * from location_flag as lf1 where lf1.locationId = loc.id and lf1.flag_id = 1 and lf1.value <> 'YES' ) and not exists ( select * from ...


1

Using NOT EXISTS should work, eg SELECT * --!!TODO add column list FROM dbo.CompanyDomains cd WHERE CompanyId = @CompanyId AND NOT EXISTS ( SELECT * FROM @domains t WHERE cd.Domain = t.domain )


0

Just use GROUP_CONCAT with a WHERE condition SELECT user_id,GROUP_CONCAT(meta_value ORDER BY id) FROM t WHERE meta_key IN('first_name','last_name','street_add','city','state') GROUP BY user_id


0

I think you might be looking for the USING clause: EXECUTE format('SELECT COALESCE(%I,col,$1) FROM public.TableMix WHERE name = $2', ruleName) USING (username, whateverTheSecondParameterIs); You use $1, $2, etc for placement-parameters, passed by USING. You use %I for itentifiers expanded by format. Update: If you want to ...


2

Neither is "faster" - if you select every column from every table, and you don't need every column from every table, then SELECT * and SELECT every,single,column are both equally wasteful. @ypercube makes a good point: columns you've joined on are obviously redundant, so as soon as you've removed just one of those columns from the output list, you've already ...



Top 50 recent answers are included