Hi, what is the best method to insert a search query into MySql
and check for double words? (for showing up the last searches and a collection of searches)
Assume I have the following tables:
TABLE: foo
- foo_id (PK)
TABLE: tag
- tag_id (PK)
- name
TABLE: foo_tag
- foo_tag_id (PK)
- foo_id (FK)
- tag_id (FK)
How do I query this so that I get a result like this:
==========================
| foo_id | tags |
==========================
| 1 | foo, bar |
| 2 | foo |
| 3 | bar |
--------------------------
Basically, I need all of foo's tags in one column, comma separated. Possible in MySQL?
Hi! I have the next query:
var bPermisos = from b in ruc.Permisos
where b.IdUsuario == cu.Id
select new Permisos(){
Id=b.Id,
IdUsuario=b.Id,
IdPerfil=b.Id,
Estatus=b.Estatus
};
var qSisPer = from perm in bPermisos
select new { perm.IdPerfil,perm.Cat_Perfil.Nivel,perm.Cat_Perfil.Nombre,Nombre_Sistem=perm.Cat_Perfil.Cat_Sistema.Nombre};
And is throwing me an exception, plz help!
Are there any ressources on what the parameters in a Google query mean? Any analysis how the Google search pages work internally?
Examples would be
http://www.google.com/#hl=en&source=hp&q=lol&aq=f&aqi=&aql=&oq=&fp=45675624562456
or
http://www.google.com/url?sa=t&source=web&ct=res&cd=11&ved=KJSGHFKSDJF&url=sfdgagasdgasdgasgasg&rct=j&q=fghthwrteghedgf&ei=asdfasdfsa&usg=asdfasdfasf
The following query returns one row as expected when run from phpmyadmin.
SELECT units . * , locations . *
FROM units, locations
WHERE units.id = '1'
AND units.location_id = locations.id
LIMIT 0 , 30
But when I try to do it in Kohana 3:
$unit = DB::select('units.*', 'locations.*')
->from('units', 'locations')
->where('units.id', '=', $id)->and_where('units.location_id', '=', 'locations.id')
->execute()->as_array();
var_dump($unit);
It prints
array(0) { }
What am I doing wrong?
Hi guys,
I guess no one will have a definative answer to this but considered predictions would be appriciated.
I am in the process of developing a mySQL database for a web application and my question is:
Is it more efficient to make a single query that returns a single row using AJAX
or
To request 100 - 700 rows when the user will likely only ever use the results of two or three?
Really I am asking what is heavier for the server 2-3 requests with one result or 1 request with 100 - 700 results?
Thanks,
Mark
I would like to limit the results to those whose KEY_HOMEID is equal to journalId.
I've been on this for a couple days any help would be appreciated.
public Cursor fetchAllNotes(String journalId)
{
return mDb.query(DATABASE_TABLE, new String[] {KEY_ROWID, KEY_HEIGHT,
KEY_BODY, KEY_HOMEID},"FROM DATABASE_TABLE WHERE KEY_HOMEID = journalId",null, null, null, null,null);
}
I'm trying to find a nice and elegant way to query database content based on DDD "specifications".
In domain driven design, a specification is used to check if some object, also known as the candidate, is compliant to a (domain specific) requirement. For example, the specification 'IsTaskDone' goes like:
class IsTaskDone extends Specification<Task> {
boolean isSatisfiedBy(Task candidate) {
return candidate.isDone();
}
}
The above specification can be used for many purposes, e.g. it can be used to validate if a task has been completed, or to filter all completed tasks from a collection. However, I want to re-use this, nice, domain related specification to query on the database.
Of course, the easiest solution would be to retrieve all entities of our desired type from the database, and filter that list in-memory by looping and removing non-matching entities. But clearly that would not be optimal for performance, especially when the entity count in our db increases.
Proposal
So my idea is to create a 'ConversionManager' that translates my specification into a persistence technique specific criteria, think of the JPA predicate class. The services looks as follows:
public interface JpaSpecificationConversionManager {
<T> Predicate getPredicateFor(Specification<T> specification, Root<T> root, CriteriaQuery<?> cq, CriteriaBuilder cb);
JpaSpecificationConversionManager registerConverter(JpaSpecificationConverter<?, ?> converter);
}
By using our manager, the users can register their own conversion logic, isolating the domain related specification from persistence specific logic. To minimize the configuration of our manager, I want to use annotations on my converter classes, allowing the manager to automatically register those converters.
JPA repository implementations could then use my manager, via dependency injection, to offer a find by specification method. Providing a find by specification should drastically reduce the number of methods on our repository interface.
In theory, this all sounds decent, but I feel like I'm missing something critical. What do you guys think of my proposal, does it comply to the DDD way of thinking? Or is there already a framework that does something identical to what I just described?
Hi all,
I have a silly question. I would like to know if there is performance deference in these two quries:
var cObject = from cust in entities.Customer
where cust.id == cid
select cust;
and
var cObject = entities.Customer.First( c=> c.id == cid);
My query return only one record as I am querying with the primary key. But do they make any difference?
Hi
I have a table with two columns:
column A column B
1 2
1 2
2 1
I want to return total of ones = 3 total of twos = 3
The best I can come up with is two queries like so:
SELECT sum(
CASE WHEN columnA =1 THEN 1 ELSE 0 END )
+ sum(CASE WHEN columnB =1 THEN 1 ELSE 0 END )
SELECT sum(
CASE WHEN columnA =2 THEN 1 ELSE 0 END )
+ sum(CASE WHEN columnB =2 THEN 1 ELSE 0 END )
Can this be done in one query?
Thanks
i have a table with coloumn id , name and city
I want to fetch those rows where city is same and the number of rows are greater than 3. Is it possible in single sql query?
I need to performance tune following SQL query by removing "OR" statements
Please help ...
SELECT a.id, a.fileType, a.uploadTime, a.filename, a.userId, a.CID, ui.username, company.name companyName, a.screenName
FROM TM_transactionLog a, TM_userInfo ui, TM_company company, TM_airlineCompany ac
WHERE
(
a.CID = 3049
)
OR
(
a.CID = company.ID
AND
ac.SERVICECID = 3049
AND
company.SPECIFICCID = ac.ID
)
OR
(
a.USERID = ui.ID
AND
ui.CID = 3049
);
Can any one please let me know the best way to use IF statement in mysql query to show if the "email" field is NULL then it should show as "no email"...
Postcode Telephone Email
----------------------------------------------------------
BS20 0QN 1275373088 no email
BS20 0QN 1275373088 no email
PO9 4HG 023 92474208 [email protected]
SO43 7DS 07801 715200 [email protected]
----------------------------------------------------------
i want maximim CTC of each employee
following is the design of my table
Ecode Implemented Date Salary
7654323 2010-05-20 350000
7654322 2010-05-17 250000
7654321 2003-04-01 350000
7654321 2004-04-01 450000
7654321 2005-04-01 750000
7654321 2007-04-01 650000
i want oracle query for following out put
Ecode Salary
7654321 650000
7654322 250000
7654323 350000
thanks in advance
Vivek Shah
Just doing an assignment for my database course and I just want to double check that I've correctly wrapped my head around relational algebra.
The SQL query:
SELECT dato, SUM(pris*antall) AS total
FROM produkt, ordre
WHERE ordre.varenr = produkt.varenr
GROUP BY dato
HAVING total >= 10000
The relational algebra:
stotal >= 10000(
?R(dato, total)(
sordre.varenr = produkt.varenr(
datoISUM(pris*antall(produkt x ordre))))
Is this the correct way of doing it?
This SQL query gives me today's number of users active in the last 30 days:
SELECT COUNT(*) FROM table.users WHERE creation_tsz >= (now() - interval '30 days')
How can I modify it to get not a single value, but a table of active users for a range of dates?
Thanks,
Rob
I'm trying for last hour solve this problem. I have a long form and I'm trying to upload info into mysql database. Into table I made. This is the mysql query I'm using:
mysql_query("INSERT INTO `users_temp`(`first_name`, `surname`, `birthday`, `nationality`, `email`, `mobile`, `landline`, `address`, `town`, `post_code`, `country`, `password`, `code_conf`) VALUES ([$f_name],[$s_name],[$bday],[$nationality],[$email],[$mobile],[$landline],[$address],[$town],[$post_code],[$country],[$pass],[$conf_code])");
If anyone see any problem why it doesn't work pls let me know. Thank you...
Can any one please let me know that, I need to add a column dynamically when executing mysql query
Table: Table1
--------------------------
col1 col2 col3
--------------------------
Test OK Test3
Test OK Test5
Test OK Test6
from the above example i need to introduce "col2" as new column and its value to be as "OK"
gorup by query issue
i have one table,
which has three fields and data.
Name , Top , total
cat , 1 ,10
dog , 2, 7
cat , 3 ,20
hourse 4, 4
cat, 5,10
Dog 6 9
i want to select record which has highest value of "total" for each Name
so my result should be like this.
Name , Top , total
cat , 3 , 20
hourse , 4 , 4
Dog , 6 , 9
i tried group by name order by total, but it give top most record of group by result.
any one can guide me , please!!!!
Hey,
I'm looking at the nhibernate interceptor. It seems to be able to intercept save, update and delete queries but is there anyway I can intercept a select query.
The problem I have is that I automatically want to append some additional sql filters to the executing sql statement in certain cases.
Any thoughts
Thanks
Mat
Please help optimize this query:
SELECT ts.SiteId, COUNT(ts.SiteId) AS Count
FROM ts
WHERE ts.SiteId not in
(SELECT ts.SiteId FROM ts WHERE ts.uniqueid = 'xxx')
GROUP BY ts.SiteId ORDER BY Count DESC
Does this query will be faster with a index on "t.type1" and "x.type1" or only index on "x.type1" is enought?
SELECT t.id, x.id
FROM t
INNER JOIN x ON x.type1=t.type1
WHERE t.id=1
In mysql a result of a query is
say select timestamp from newbie;
| 2010-03-12 14:50:46 |
| 2010-03-12 14:50:46 |
| 2010-03-12 14:50:51 |
| 2010-03-12 14:50:52 |
| 2010-03-12 14:50:54 |
| 2010-03-12 14:51:04 |
| 2010-03-12 14:51:07 |
| 2010-03-12 14:51:08 |
Is there a way to subquery the above and sum up the i.e, the final result should be the delta of each row in hh:mm:ss format
Is there a way to select records based using an if statement?
My table looks like this:
id | num | dis
1 | 4 | 0.5234333
2 | 4 | 8.2234
3 | 8 | 2.3325
4 | 8 | 1.4553
5 | 4 | 3.43324
And I want to select the num and dis where dis is the lowest number... So, a query that will produce the following results:
id | num | dis
1 | 4 | 0.5234333
4 | 8 | 1.4553