Search Results

Search found 1369 results on 55 pages for 'clause'.

Page 29/55 | < Previous Page | 25 26 27 28 29 30 31 32 33 34 35 36  | Next Page >

  • Complex multiple join query across 3 tables

    - by Keir Simmons
    I have 3 tables: shops, PRIMARY KEY cid,zbid shop_items, PRIMARY KEY id shop_inventory, PRIMARY KEY id shops a is related to shop_items b by the following: a.cid=b.cid AND a.zbid=b.szbid shops is not directly related to shop_inventory shop_items b is related to shop_inventory c by the following: b.cid=c.cid AND b.id=c.iid Now, I would like to run a query which returns a.* (all columns from shops). That would be: SELECT a.* FROM shops a WHERE a.cid=1 AND a.zbid!=0 Note that the WHERE clause is necessary. Next, I want to return the number of items in each shop: SELECT a.*, COUNT(b.id) items FROM shops a LEFT JOIN shop_items b ON b.cid=a.cid AND b.szbid=a.zbid WHERE a.cid=1 GROUP BY b.szbid,b.cid As you can see, I have added a GROUP BY clause for this to work. Next, I want to return the average price of each item in the shop. This isn't too hard: SELECT a.*, COUNT(b.id) items, AVG(COALESCE(b.price,0)) average_price FROM shops a LEFT JOIN shop_items b ON b.cid=a.cid AND b.szbid=a.zbid WHERE a.cid=1 GROUP BY b.szbid,b.cid My next criteria is where it gets complicated. I also want to return the unique buyers for each shop. This can be done by querying shop_inventory c, getting the COUNT(DISTINCT c.zbid). Now remember how these tables are related; this should only be done for the rows in c which relate to an item in b which is owned by the respective shop, a. I tried doing the following: SELECT a.*, COUNT(b.id) items, AVG(COALESCE(b.price,0)) average_price, COUNT(DISTINCT c.zbid) FROM shops a LEFT JOIN shop_items b ON b.cid=a.cid AND b.szbid=a.zbid LEFT JOIN shop_inventory c ON c.cid=b.cid AND c.iid=b.id WHERE a.cid=1 GROUP BY b.szbid,b.cid However, this did not work as it messed up the items value. What is the proper way to achieve this result? I also want to be able to return the total number of purchases made in each shop. This would be done by looking at shop_inventory c and adding up the c.quantity value for each shop. How would I add that in as well?

    Read the article

  • UpdateLayout throwing an ambigious exception

    - by Ouais
    the stack trace displays my method's name then "external code" then app_unhandledexception method, I'm developing within VS2008 SP1 for silverlight on firefox and windows 7(if that makes a difference). the weird thing is, the updatelayout method is called elsewhere and it generates no exception there, that said, if I put the method within a try-catch clause, it doesn't catch the exception, so if anyone has an idea, I'd appreciate it.

    Read the article

  • pluralize function in ruby NOT RAILS!

    - by DJTripleThreat
    Writing some ruby code (not rails) and I need to handle something like this: found 1 match found 2 matches I have rails installed so maybe I might be able to add a require clause at the top of the script, but does anyone know of a RUBY method that pluralizes strings? Is there a class I can require that can deal with this if the script isn't rails but I have rails installed? Thanks in advance!

    Read the article

  • error with linq join

    - by Luca Romagnoli
    I have this linq query: var segreterie = from s in db.USR_Utenti join h in db.USR_Accounts on new {s.ID, settings.GruppoSegreteria} equals new {h.USR_UtentiReference,h.ID_Gruppo} select s; that has this problem: The type of one of the expressions in the join clause is incorrect. Type inference failed in the call to 'Join'. how can i do to solve it?

    Read the article

  • SQL - Conditionally evaluate WHERE clauses

    - by Crimius
    I need to get a WHERE clause to only evaluate certain statements. something like: WHERE field_a = field_b AND (CASE WHEN <PARAM>type</PARAM> = 5 THEN {field_c = 1 OR field_c = 2} WHEN <PARAM>type</PARAM> = 6 THEN {field_c = 3 OR field_c = 4} ELSE field_c = <PARAM>type</PARAM> so that when the Param type = 5, it only checks if field_c = 1 or 2. Any thoughts?

    Read the article

  • MySQL 5 left join unknown column

    - by NP
    I had the below query working in mysql 4.1, but does not in 5.0: SELECT * FROM email e, event_email ee LEFT JOIN member m on m.email=e.email WHERE ee.email_id = e.email_id The error: 1054 (Unknown column 'e.email' in 'on clause')

    Read the article

  • Doctrine: how to create a query using "LIKE REPLACE" ?

    - by user248959
    Hi, this SQL clause is working OK: SELECT * FROM `sf_guard_user` WHERE nombre_apellidos LIKE REPLACE('Mar Sanz',' ','%') Now I'm trying to write this query for Doctrine. I have tried this: $query->andWhere(sprintf('%s.%s LIKE REPLACE (?," ","%")', $query->getRootAlias(), $fieldName), 'Mar Sanz')); but I get this error: Warning: sprintf() [function.sprintf]: Too few arguments Any idea? Regards Javi

    Read the article

  • SQLite user_version() expression

    - by ralf.w.
    how can I get user_version into an expression (for SELECT or WHERE) ? I would also appreciate any clue to how to save ANY global variable in SQLite. Does anyone know of such an extension (dll) ? BTW: I know that PRAGMA user_version; gives me the right answer, but I cannot use that inside a WHERE-clause !

    Read the article

  • Running query from scratch with something like exec function?

    - by Steel Plume
    Hi, is it possible to make something similar to the following with Postgresql without using a function? pseudo sql code: select * from sometable where somecol = somevalue AND someothercol IN exec( 'select something from exclusionlist' ) My primary intention is to build up a table with predefined queries to call inside a where clause pseudo sql code: select * from sometable where somecol = somevalue AND someothercol IN exec( select query from predefinedqueries where id=someid )

    Read the article

  • include link list depending on category

    - by user117701
    I want to display the linklist widget only on a certain category. The current way the sidebar is fetched is: if (!function_exists('dynamic_sidebar') || !dynamic_sidebar(1)) Which doesnt give me a say in the calling of the links widget. If i put an if clause there, then the rest of the widgets dont get loaded. So i either need to customize the links widget it self or find a way to call the links widget directly? And how do i check which category i'm on?

    Read the article

  • Stored Procedure in postgresql, multiple queries w/ agreggates.

    - by fenix
    I'm trying to write a store procedure that can take some input parameters (obviously), run multiple queries against those, taking the output from those and doing calculations, and from those calculations and the original queries, outputting a formatted text string like: Number of Rows for max(Z) matching condition x and y of total rows matching x (x&y/x*100). To explain the max(Z) bit, this will be the username field, it won't matter which actual entry is picked, because the where clause will filter the results by user id, is there a saner way to do this?

    Read the article

  • C++ variable alias - what's that exactly, and why is it better to turn if off?

    - by Poni
    I've read the essay Surviving the Release Version. Under the "Aliasing bugs" clause it says: You can get tighter code if you tell the compiler that it can assume no aliasing.... I've also read Aliasing (computing). What exactly is a variable alias? I understand it means using a pointer to a variable is an alias, but, how/why does it affect badly, or in other words - why telling the compiler that it can assume no aliasing would get me a "tighter code"

    Read the article

  • Modifying MySQL Where Statement Based on Array

    - by Ryan
    Using an array like this: $data = array ( 'host' => 1, 'country' => 'fr', ) I would like to create a MySQL query that uses the values of the array to form its WHERE clause like: SELECT * FROM table WHERE host = 1 and country = 'fr' How can I generate this query string to use with MySQL?

    Read the article

  • Understanding PHP's way of reading if statements

    - by Masi
    How does PHP read if statements? I have the following if statements in this order if ( $number_of_figures_in_email < 6) { -- cut: gives false } if($number_of_emails > 0) { -- cut: gives false } if ( $number_of_emails == 0) { -- cut: gives true } The code behaves randomly. It sometimes goes to the third if clause and gives me a success, while sometimes to the one of the first two if clauses when the input variables are constant. This suggests me that I cannot code only with if statements.

    Read the article

  • implict type cast in generic method

    - by bitbonk
    why do I get a compiler error in the following code stating: Cannot implicty convert type SpecialNode to T even though T must derive from NodeBase as I defined in the where clause and even though SpecialNode actually derived from NodeBase? public static T GetNode<T>() where T : NodeBase { if (typeof(T) == typeof(SpecialNode)) { return ThisStaticClass.MySpecialNode; // <-- compiler error } if (typeof(T) == typeof(OtherSpecialNode)) { return ThisStaticClass.MyOtherSpecialNode; // <-- compiler error } ... return default(T); }

    Read the article

  • Forcing the use of an index can improve performance?

    - by aF.
    Imagine that we have a query like this: select a.col1, b.col2 from t1 a inner join t2 b on a.col1 = b.col2 where a.col1 = 'abc' Both col1 and col2 don't have any index. If I add another restriction on the where clause, one that is always correct but with a column with an index: select a.col1, b.col2 from t1 a inner join t2 b on a.col1 = b.col2 where a.col1 = 'abc' and a.id >= 0 -- column always true and with index May the query perform faster since it may use the index on id column?

    Read the article

  • "Ambiguous column name" error on one particular server

    - by jazbit
    This simple query throws the "Ambiguous column name TaskID" error on one db-server only. This is ridiculous. We tested this with the same database structure on different servers and different versions of ms sql (2005/2008), and it's only THIS particular client's server that throws the error. I'm actually frustrated. SELECT Tasks.TaskID FROM Tasks INNER JOIN TaskHelpers ON TaskHelpers.TaskID = Tasks.TaskID order by TaskID Yes, I know I can put "Tasks.TaskID" into the order by clause, but for some reasons I can't.

    Read the article

  • SQL - Add up all row-values of one column in a singletable

    - by ThE_-_BliZZarD
    Hello Everybody, I've got a question regarding a SQL-select-query: The table contains several columns, one of which is an Integer-column called "size" - the task I'm trying to perform is query the table for the sum of all rows (their values), or to be more exact get a artifical column in my ResultSet called "overallSize" which contains the sum of all "size"-values in the table. Preferable it would be possible to use a WHERE-clause to add only certain values ("WHERE bla = 5" or something similar). The DB-engine is HSQLDB (HyperSQL), which is compliant to SQL2008. Thank you in advance :)

    Read the article

  • How to append an integer to a const char value in iphone?

    - by Warrior
    I have sql query statement which used to display the contents in the table.The sql statement consist of a where clause which is to be appended with numeric value as 1 ,2 3 etc depends upon the previously selected content.I am having the numeric value as int and i want it to append to sql statement which is const char.how can i append both the values.Please help me out. My query is == select * from Book where id=1; i have the id value is integer Thanks.

    Read the article

  • MongoDB using NOT and AND together

    - by Stankalank
    I'm trying to negate an $and clause with MongoDB and I'm getting a MongoError: invalid operator: $and message back. Basically what I want to achieve is the following: query = { $not: { $and: [{institution_type:'A'}, {type:'C'}] } } Is this possible to express in a mongo query? Here is a sample collection: { "institution_type" : "A", "type" : "C" } { "institution_type" : "A", "type" : "D" } { "institution_type" : "B", "type" : "C" } { "institution_type" : "B", "type" : "D" } What I want to get back is the following: { "institution_type" : "A", "type" : "D" } { "institution_type" : "B", "type" : "C" } { "institution_type" : "B", "type" : "D" }

    Read the article

  • insert contacts into database but does not want to duplicate already existing contact.

    - by Frank Nwoko
    I am trying to insert contacts into database but does not want to duplicate already existing contact. Not sure INSERT has WHERE CLAUSE. Any ideas? //Insert INTO contact database $user_id = userid; $sql_insert = "INSERT into `jt_members_external_contacts` (`j_user_id`,`contact_email`,`firstname` ) VALUES ('$user_id','$email','$name') WHERE j_user_id !=$user_id AND contact_email != $email;";

    Read the article

  • Check value existance while performing a UPDATE query

    - by nimo
    Hi, I need to perform a simple update query where the update should only be done, if there is no value with updating value (item_name). For example, DB table [item_types] item_id(PK) | item_name Assuming there exist item_id with 6, My attempt is UPDATE item_types as k SET k.item_name = 'item_1' WHERE NOT EXISTS (SELECT * FROM item_types as a WHERE a.item_name = 'item_1') AND k.item_id = '6' But this gives me error "You can't specify target table 'k' for update in FROM clause" Could you please explain the error here and how can I achieve this ? Thank you

    Read the article

< Previous Page | 25 26 27 28 29 30 31 32 33 34 35 36  | Next Page >