Given I have the following view where N=1..100 detail_view_N Pant Quantity Fieldx ... A 20 A 13 B 4 Currently we have summary views which insert to a table like summary_view_N Report_Name Plant Count summary_view_1 A 2 summary_view_1 B 1 The table is then used to create an overall summary l...
Can somebody help get the following pseudocode in mysql? The resulting selects in the IF statement all return the same columns (4) and multiple rows (unknown) so that's not really the problem i'm facing.. How can I get the following structure in Mysql? //parameters @p1; @p2; @v1; @v2; //vars @t1= 15000; @t2 = 15000; //calculated vars...
I have to pull a list of integer IDs from a table using only records that match some criteria. For example: Select ProdID From Products Where (ProdType='XYZ'); The catch is that I have to return it as a set of comma separated values so I can use it to select items in a multi-select list: 111,231,554,112 rather than as records. I d...
Hi there, I have this table that represents a weak entity and is a typical table for introducing items ordered: insert into ITEM_FORNECIMENTO values (a_orderId,a_prodId,a_prodQtd, a_buyPrice); I want my trigger to update the last column (the total price of products WITHOUT iva) to do this : totalPrice= totalPrice*(1+(iva/100), each t...
I have a SQLite database of notes that have columns _id, title, details, listid _id is the auto incremented primary key title and details are string data fields listid is a foreign key pointing to a list name in another table. I'd like to find a way to have notes that are in multiple lists or notes that are linked in such a way tha...
I have a table called 'bandsplusrating' and a table called 'ratings' The table 'bandsplusrating' is filled with bands, the table 'ratings' starts empty. Both tables have field called 'naam' with which they can be joined. Table 'ratings' has a field called 'fullname' Multiple Ratings per band (bandsplusrating) will be stored in table 'r...
I have executed a query and included the Actual Execution Plan. There is one Hash Match that is of interest to me because it's subtree uses a Index Scan instead of an index seek. When I mouse over this Hash Match there is a section called "Probe Residual". I had assumed that this is whatever values I am joining on. Am I correct here ...
I am wanting to select all images that a user does not already have. I have three tables: user, image, and user_image: _user (id, user, pass) _image (id, data) _user_image (id, user_id, image_id) ...
I'd like to know when I write a query like SELECT id FROM users WHERE expiry > curdate() How can I work out what the curdate() is returning? I just switched servers of a site and it appears to be throwing it out, by perhaps returning a different curdate(). Thanks ...
I have an xml file from an external system that looks like this. <?xml version="1.0" encoding="UTF-8"?> <Envelope xmlns="http://schemas.xmlsoap.org/soap/envelope/"> <Body> <Element1> <Element2 day="2009-10-18"> <Element3 name="Joe"> <Element4 time="1"> ...
Is there a way to map one of the the columns contents of a MySQL table to an enum on another table in MySQL? I thought this would be a no brainer, but there doesn't seem to be any info that I can find on the subject. Any advice or help on this matter would be cool and if it's not possible, does anyone know of an internal reason why it w...
I've got a query that outputs the following table: (0) Age <= 19----------76-------0.12 (1) Age 20 – 24--------661------1.06 (2) Age 25 – 29-------4060------6.53 (3) Age 30 – 34-------7231------11.64 (4) Age 35 – 39-------9281------14.94 (5) Age 40 – 44-------9539------15.35 Total ----------------30848 -----49.65 The first...
I've come across an example of doing this on the net which also fails on my DB (10g) but here's my version. ... TYPE prog_rec_type IS TABLE OF NUMBER INDEX BY PLS_INTEGER; prog_rec_list prog_rec_type; begin ... EXECUTE IMMEDIATE 'SELECT PROGRESS_RECID FROM ' || v_table_name || v_where BULK COLLECT INTO prog_rec_list; --ERROR FOUND...
If i were to organise data on tourism and hotel for the continent of Australia and south America can you let me know a best way to do it. Like if i organise the tables thus: continents countries states zipcodes cities destinations hotels lnkhotelszipcodes (in case a hotel exists in multiple places) lnkhotelscities It will be search ...
How can I do a SQL query with the like operator from two different tables? I need something like: select * from table1 where name like %table2.name It's not a common field but a substring of a field on another table. ...
I have a block of XML in a database which is easy enough to pull out using ASP.NET MVC, however I would like to access and modify the XML in an way more consistent with class instances. Is there a way to get the MVC (or any other model) to generate a data access (or perhaps Entity) class set from the DB-stored XML? If the above is rathe...
Let's imagine that we are having "cars" table with such a simple structure... car_id INT color ENUM('black','white','blue') weight ENUM('light','medium','heavy') type ENUM('van','sedan','limo') Fist, I'm selecting car (1, black, heavy, limo), then I'd like to get list of related cars sorted by number of matching columns (without any c...
I need something like: select * from table1 where name like %table2.name ...
Hi Guys, I think I have a straight forward question. I'm writing a system that allows users from company A to single sign on to the system and for this I go back to the central database of users at company A and validate the user credentials passed to me. Currently my implementation involves building up my query using a stringbuilder a...
Hi, I need some help figuring out and SQL Statement. I know what I want I just cant express it. Im using php, so it doesnt need to be exclusivly SQL, its to act as a filter. Pseudo code $query="SELECT * FROM MyTable WHERE 'TIS' is not older than 2 days or empty = ''$ORDER"; } TIS in the name of the column in my table were I stor...