Store and retrieve user favorites in mysql

I have an android app that pulls information from mysql db and displays it in a list. Each list item has a favorite button that upon click updates the db with the username and with the list item.

I can get all user favorites using relational and foreign key. What I am trying to achieve is when the user re-logs in to the App and scrolls for available items he can already see which items he marked as favorite without going to his favorites page.

So for example when you are scrolling in your facebook page you can already see which posts you liked.

How can I achieve that?

First table

|------------------|--------------|----|-------|
|      Column      |     Type     |Null|Default
|------------------|--------------|----|-------|
|    //**id**//    |   int(11)    | No |
|    car_vendor    |  varchar(20) | No |
|    car_model     |  varchar(20) | No |
|    car_petro     |  varchar(10) | No |
| car_transmition  |  varchar(10) | No |
|     car_hand     |   int(11)    | No |
|  car_spedometer  |  varchar(7)  | No | 
|  car_engine_vol  |  varchar(4)  | No |
|   car_category   |   int(11)    | No |
|  car_post_date   |     date     | No |

second table

|----------------|------------|----|
|     Column     |    Type    |Null| Default
|----------------|------------|----|
|   //**id**//   |   int(11)  | No |
|    username    | varchar(20)| No |
|  favorites_id  |   int(11)  | No | <<< foreign key points to ID on first table 

Writing a query to find the following result

Employees Table:

EmpID EmpName

---

1 John Torres

2 Irina Williams

Payroll Week Table:

WeekID EmpID WeekStart WeekEnd

---

1 1 11-20-2011 11-26-2011

2 2 11-27-2011 12-03-2011

3 1 11-27-2011 12-03-2011

Employee Visits Table:

ID EmpID VisitDate StartTime EndTime Earningcode

---

1 1 11-20-2011 10:00 12:00 Sat-Sun1

2 1 11-21-2011 13:30 16:00 Mon-Fri1

3 1 11-22-2011 14:00 15:00 Mon-Fri1

4 1 11-24-2011 10:00 14:00 Mon-Fri1

5 1 11-25-2011 13:30 16:00 Mon-Fri1

6 1 11-26-2011 14:00 15:00 Sat-Sun1

7 2 11-27-2011 09:00 11:00 Sat-Sun1

8 2 11-28-2011 07:00 12:00 Mon-Fri1

9 2 11-29-2011 09:00 11:00 Mon-Fri1

10 2 12-03-2011 07:00 12:00 Sat-Sun1

Expected Results

RecordType EmpID EmpName WeekStart WeekEnd Earning code Hours

---

H 1 John Torres 11-20-2011 11-26-2011

D Sat-Sun1 3.00

D Mon-Fri1 10.00

H 2 Irina Williams 11-27-2011 12-03-2011

D Sat-Sun1 7.00

D Mon-Fri1 7.00

Sql Error when running queires file

I am trying to run CREATE TABLE persons (id int(4) NOT NULL auto_increment, name varchar(30) NOT NULL, favorite_color varchar(30) NOT NULL, weight int(5) NOT NULL, favorite_movie varchar(20), state varchar(20), PRIMARY KEY (id));

I am recieving syntax error near unexpected token `('

Any help appreciated what im doing wrong.

HQL query to get min id

This query returns the number of places available for the same table. Of all the id table returned I need one with smaller IDs. The id of the table is a string.

String SQL_QUERY = "   FROM PostoDBWrapper p"
                +    " WHERE p.tavolo  in"
        + "                     (SELECT p.tavolo "
        + "                     FROM PostoDBWrapper p "
+ "                             WHERE p.disponibilita = true  "
+ "                             GROUP BY p.tavolo             "
+ "                             HAVING COUNT(p.tavolo) >= :posRichiesti)"
+ "                    AND p.disponibilita = true" ;


Query query = session.createQuery(SQL_QUERY);  
query.setParameter("posRichiesti",posRichiesti);  

SELECT column WHERE time_type = 'Break' but only the rows that are after(below) time_type = 'Start'

currently I'm stuck in an issue, hope some good PostgreSQL fellow programmer could give me a hand with it. This is my table...

I would like to SELECT all 'time_elapse' WHERE time_type = 'Break' but only the rows that are after(below) the last(descendent) time_type = 'Start' and sum them up.

So in the table above I would SELECT...

time_elapse           |   time_type  |  time_index
----------------------+--------------+-------------
00-00-00 01:00:00.00  |   Break      |  2.1
00-00-00 01:00:00.00  |   Break      |  2.2

So totalbreak = 00-00-00 02:00:00.000

I know how to convert character varying to timestamp in order to sum them up (please don't bother with that, I'm not looking for help with that, let's image 'time' and 'time_elapse' columns are proper timestamps), I just don't know how would be the syntax to select all possible 'Breaks' and sum them up (lets say the max Breaks between each 'Start' is nine).

I can hardly imagine a way to do that, so I would like to ask for suggestions.

Troubles with using GROUP BY in SQL Query

I'm trying to write a propper SQL query in MS SQL Server. First of all, i have the following tables: Towns, Employees, Addresses. Almost every employee has Manager, whom ManagerID is foreign key in Employees also. (Self relation). My goal is to display the number of managers from each town. So far i have this code:

SELECT t.Name, COUNT(*) AS [Managers from each town] 
FROM Towns t
JOIN Addresses a
ON t.TownID = a.TownID
JOIN Employees e
ON a.AddressID = e.AddressID
GROUP BY t.Name
ORDER BY [Managers from each town] DESC

This query returns the number of Employees, from each town, not Managers. If i try the second query bellow, I get something totally wrong:

SELECT t.Name, COUNT(*) AS [Managers from each town] 
FROM Towns t
JOIN Addresses a
ON t.TownID = a.TownID
JOIN Employees e
ON a.AddressID = e.AddressID
JOIN Employees m
ON e.ManagerID = m.ManagerID

GROUP BY t.Name
ORDER BY [Managers from each town] DESC

Here is the structure of 'Employees' table:

EmployeeID, FirstName, LastName, MiddleName, JobTitle,DepartamentID, ManagerID, HireDate, Salary, AddressID

The correct query must return this result set:

Town          | Managers from each town
Issaquah      | 3
Kenmore       | 5
Monroe        | 2
Newport Hills | 1

Avoid Duplicate values for INSERT in SQL

  <?php 
mysql_query("INSERT INTO `users_badges` (`user_id`, `badge_id`, `badge_slot`) VALUES ('$my_id', 'VIP', '1')");
?>

I have this code to insert something in the table but every time if you visit the home page the same user gets the item dubplicated. How can I fix this.

So basicilly I want IF already you have VIP,the code doesn't have a value to avoid duplicate.

mysql query optimiser better performance from subquery with partitions than left join with index

when using mysql version 5.6.14-enterprise-commercial-advanced-log on an ubuntu 12.04 LTS, I encounter the following behaviour when querying data from these tables:

CREATE TABLE `a` (
          `id` varchar(32) DEFAULT NULL,
          `request_time` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
        ) ENGINE=MyISAM DEFAULT CHARSET=latin1
        !50100 PARTITION BY RANGE (UNIX_TIMESTAMP(request_time))
        (PARTITION p15062116 VALUES LESS THAN (1434895200) ENGINE = MyISAM,
         PARTITION p15062117 VALUES LESS THAN (1434898800) ENGINE = MyISAM,
         PARTITION p15062118 VALUES LESS THAN (1434902400) ENGINE = MyISAM,
        ...
        PARTITION rest VALUES LESS THAN MAXVALUE ENGINE = MyISAM)

CREATE TABLE `b` (
          `id` varchar(50) NOT NULL,
          `start_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
          `item` int(11) DEFAULT NULL,
          `item2` int(11) DEFAULT NULL,
          PRIMARY KEY (`id`,`start_time`)
        ) ENGINE=MyISAM DEFAULT CHARSET=utf8
        !50100 PARTITION BY RANGE (UNIX_TIMESTAMP(start_time))
        (PARTITION p15062516 VALUES LESS THAN (1435240800) ENGINE = MyISAM,
         PARTITION p15062517 VALUES LESS THAN (1435244400) ENGINE = MyISAM
        ....
        PARTITION rest VALUES LESS THAN MAXVALUE ENGINE = MyISAM)

using this query results in a 1 second runtime:

SELECT SQL_NO_CACHE count(*)  FROM a left join (select * from b where start_time between '2015-06-28 10:00:00' and '2015-06-28 11:00:00') c using(id) where request_time between '2015-06-28 10:00:00' and '2015-06-28 11:00:00';

explain output:

+----+-------------+------------+------+---------------+-------------+---------+------+--------+-------------+
| id | select_type | table      | type | possible_keys | key         | key_len | ref  | rows   | Extra       |
+----+-------------+------------+------+---------------+-------------+---------+------+--------+-------------+
|  1 | PRIMARY     | a   | ALL  | NULL          | NULL        | NULL    | NULL | 336972 | Using where |
|  1 | PRIMARY     | <derived2> | ref  | <auto_key0>   | <auto_key0> | 152     | func |     10 | Using where |
|  2 | DERIVED     | b  | ALL  | NULL          | NULL        | NULL    | NULL |  39508 | Using where |
+----+-------------+------------+------+---------------+-------------+---------+------+--------+-------------+
3 rows in set (0.00 sec)

and using this query results in 30 seconds runtime:

SELECT SQL_NO_CACHE count(*)  FROM a  left join b using(id) where request_time between '2015-06-28 10:00:00' and '2015-06-28 11:00:00' and start_time between '2015-06-28 10:00:00' and '2015-06-28 11:00:00';

explain output:

+----+-------------+-----------+------+---------------+---------+---------+------+--------+--------------------------+
| id | select_type | table     | type | possible_keys | key     | key_len | ref  | rows   | Extra                    |
+----+-------------+-----------+------+---------------+---------+---------+------+--------+--------------------------+
|  1 | SIMPLE      | a  | ALL  | NULL          | NULL    | NULL    | NULL | 336972 | Using where              |
|  1 | SIMPLE      | b | ref  | PRIMARY       | PRIMARY | 152     | func |    395 | Using where; Using index |
+----+-------------+-----------+------+---------------+---------+---------+------+--------+--------------------------+
2 rows in set (12.17 sec)

I expected similar or better results from the second query based on the usage of the id index and the 1 hour partition. both tables have 1000000~ records each.

can you explain why is the first query much more efficient than the second one?

can we refactor the first query so that it might become a view or a reusable query instead of rebuilding the subquery for each join?

thanks

Why does MAX(Column) as Column give error, and MAX(Column) as max_Column does not?

I was just wondering, why does:

select max(run_id) as run_id from my_table where run_id > 50;

It gives an error and

select max(run_id) as max_run_id from my_table where run_id > 50;

select max(run_id) from my_table where run_id > 50;

the above two queries does not give an error.

Let's say the structure of the table is,

create table my_table(
run_id int,
something varchar(10))

This table has 100 run_id's.

I know you can't use where clause with aggregate functions.

Is it because we rename the column (as max_run_id) and the sql is treating it as a separate column, where if the name was the same as the original column it sees the aggregate function and gives the error because of it? Or can someone explain that with better terms.

insert from multiple tables into other

This is the table structure for the seven tables I'm trying to join into just one:

-- tables: en, fr, de, zh_cn, es, ru, pt_br
`geoname_id` INT (11),
`continent_code` VARCHAR (200),
`continent_name` VARCHAR (200),
`country_iso_code` VARCHAR (200),
`country_name` VARCHAR (200),
`subdivision_1_name` VARCHAR (200),
`subdivision_2_name` VARCHAR (200),
`city_name` VARCHAR (200),
`time_zone` VARCHAR (200)

And this is the new table structure, where all data will be stored:

CREATE TABLE `geo_lists` (
    `city_id` int (11), -- en.geoname_id (same for all 7 tables)
    `continent_code` varchar (2), -- en.continent_code (same for all 7 tables)
    `continent_name` varchar (200), -- en.continent_name (just in english)
    `country_code` varchar (2), -- en.country_iso_code (same for all 7 tables)
    `en_country_name` varchar (200), -- en.country_name
    `fr_country_name` varchar (200), -- fr.country_name
    `de_country_name` varchar (200), -- de.country_name
    `zh_country_name` varchar (200), -- zh_cn.country_name
    `es_country_name` varchar (200), -- es.country_name
    `ru_country_name` varchar (200), -- ru.country_name
    `pt_country_name` varchar (200), -- pt_br.country_name
    `en_state_name` varchar (200), -- en.subdivision_1_name
    `fr_state_name` varchar (200), -- fr.subdivision_1_name
    `de_state_name` varchar (200), -- de.subdivision_1_name
    `zh_state_name` varchar (200), -- zh_cn.subdivision_1_name
    `es_state_name` varchar (200), -- es.subdivision_1_name
    `ru_state_name` varchar (200), -- ru.subdivision_1_name
    `pt_state_name` varchar (200), -- pt_br.subdivision_1_name
    `en_province_name` varchar (200), -- en.subdivision_2_name
    `fr_province_name` varchar (200), -- fr.subdivision_2_name
    `de_province_name` varchar (200), -- de.subdivision_2_name
    `zh_province_name` varchar (200), -- zh_cn.subdivision_2_name
    `es_province_name` varchar (200), -- es.subdivision_2_name
    `ru_province_name` varchar (200), -- ru.subdivision_2_name
    `pt_province_name` varchar (200), -- pt_br.subdivision_2_name
    `en_city_name` varchar (200), -- en.city_name
    `fr_city_name` varchar (200), -- fr.city_name
    `de_city_name` varchar (200), -- de.city_name
    `zh_city_name` varchar (200), -- zh_cn.city_name
    `es_city_name` varchar (200), -- es.city_name
    `ru_city_name` varchar (200), -- ru.city_name
    `pt_city_name` varchar (200), -- pt_br.city_name
    `time_zone` varchar (30) -- en.time_zone (same for all 7 tables)
);

I'd like to join them all, using the locale (language) code as prefix for the column names.

SQL query with AND OR operator

I have this sql query

SELECT entries.*, categories.name as name_cat, areas.name as name_area, sub_categories.name as name_sub
FROM entries, areas, sub_categories, categories
WHERE sub_categories.id= entries.id_sub
  AND **areas.id = entries.area**
  AND categories.id = entries.id_cat
ORDER BY entries.id DESC

It works good but i do have some BLANK fields in entries.area .. which is not coming in result..

I try few changes in query but some give me result while some give less nothing gives me proper Answer

How to use partition to select only the desired row?

Here is my table:

table1:
id_nbr                 op_nbr
123E0100               HTM
123E0102               FO
145E0102               HTM
145E0104               FO
198E0100               HTM
234E0100               JAV
234E0102               FO

123E0102, 145E0104 are the id_nbrs I need selected.

For both of them, there exists an id_nbr that matches on the first 6 characters. Op_nbr associated with the originl ID_nbrs are HTM. Last 2 characters increment by 2.

Give me all rows where first 6 characters are equal and last 2 characters is 02 higher than the row with op_nbr HTM associated.

Any help or input is appreciated. Thank You

Beginner learning sql joins in mySQL

I have created 3 tables: dog, customer and owner. It is a many to one relationship with many dogs owned by 1 customer.

CREATE TABLE dog
(
DogID int(6) NOT NULL,
DogName varchar(15),
medicalID int (6),
Gender character(1) check(gender in ('m', 'f')),
Age int(2),
Breed varchar(15),
size character (1) check(size in ('s', 'm', 'l')),
primary key (DogID));

CREATE TABLE Owns
(
DogID int (6)primary key,
CustomerID int (6),
foreign key (CustomerID) references customer (CustomerID),
foreign key (DogID) references dog (DogID));

CREATE TABLE customer
(
CustomerID int(6) NOT NULL,
FirstName varchar(15),
Surname varchar(15),
Address varchar(225),
email varchar (30), 
TelNo varchar (15),
MobNo Varchar (15), 
EmergencyName varchar (40), 
EmergencyPhoneNo varchar(15),
primary key (CustomerID));

I have queried with but nothing is returned.

select dog.dogName 
from dog, owns, customer
where owns.dogID = dog.dogId
  and customer.customerID = owns.customerID
  and customer.FirstName = "CustomersName";

Remove duplicates in MySQL table - set group_id when city_id is the same

I have table units in my database. In schema I have fields id, unit_id, group_id, city_id.

For simple I have 3 units:

(1, 1, 1, 1)
(2, 1, 2, 1)
(3, 1, 3, 2)

How can I remove useless groups id, when city id is the same. I have next result:

(1, 1, 1, 1)
(2, 1, 1, 1)
(3, 1, 3, 2)

I know how do this in PHP, but I think 'maybe MySQL has inbuild functions which i don't know' ;)

Regards

how to return more than 1 row in PostgreSQL function?

Basicly my quesion is how to do this How to return more than 1 record? in PostgreSQL.

I have a query which returns 80 rows, and I want to put it in a function.

I defined a type:

CREATE TYPE typ_new AS
   (id integer,
    pn text,
    name citext,
    qty numeric,);

and I wrote a function :

CREATE OR REPLACE FUNCTION stock.func()
  RETURNS typ_new AS
  $BODY$
  begin

            QUERY

end;
 $BODY$
 LANGUAGE plpgsql VOLATILE

but it returns only 1 row instead of 80. I know I should do some sort of loop because I need to add each row to the return type but I just can find the proper syntax to do that.

I'm trying to do what is written here under 39.6.1.2. RETURN NEXT and RETURN QUERY but I don't understand what is foo%rowtype , I know it's 99% of the answer but I just can't make it work...

How to change the SqlStatementSource in a SSIS package through job step advanced tab

I have a ssis package deployed and created a sql agent job which executes the package.I need to change the SqlStatementSource in one of the sql task in package through job step advanced tab. Can any one help me how to do that? I somewhere read its possible but not able to recall how exactly it can be done?

Login with EJB sessionbean and servlet

I am trying to build a login stateless session bean that i will be calling from my servlet.The query selects ACCESS(boolean value) from the user table and if the value is true access will be granted. if false it means account is deactivated and if the resultlist is empty user shout get failed login. am just having trouble implementing it.

package Dao;

import Model.Account;
import javax.ejb.Stateless;
import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
import javax.persistence.Query;

@Stateless
public class AccountAccessDao implements AccountAccessDaoLocal {
    boolean status=false;

    @PersistenceContext
    private EntityManager emlogin;

    @Override
    public boolean accountLogin(String username,String password) {
    Query query = emlogin.createNativeQuery("SELECT e.access FROM Account e WHERE e.username='" + username + "'AND e.password='" + password + "'",Account.class);

    return query.getResultList().contains(this);
}
}








package Controller;

import Dao.AccountAccessDaoLocal;
import java.io.IOException;
import static java.lang.System.out;
import javax.ejb.EJB;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

public class AccessServlet extends HttpServlet {
@EJB
    private AccountAccessDaoLocal userlogin;

    protected void doPost(HttpServletRequest request, HttpServletResponse response)
    throws ServletException, IOException {

            String username = request.getParameter("username");
            String password = request.getParameter("password");

            boolean status = userlogin.accountLogin(username, password);

            if(status==true){
                out.println("Success");
                //Accout confirmation page
            }else if(status==false){
                out.println("account locked");
                //Accout confirmation page
            }else {
                out.println("Failure");
                //failure page here
            }
 } }

Writing a query to display the following result?

There are 3 tables

Table A:
code        aname
------- ----------
1           A
2           B
3           C


Table B:
code        bname
----------- ----------
1           aaa
1           bbb
2           ccc
2           ddd


Table C
code        cname
----------- ----------
1           xxx
1           yyy
1           zzz
2           www

We need to write a query that would display the following result.

code        aname      bname            cname
----------- ---------- ----------      ----------
1                  A    aaa              xxx
1                  A    bbb              yyy
1                  A    NULL             zzz
2                  B    ccc              www
2                  B    ddd              NULL
3                  C    NULL             NULL

How to speed up the query in PostgreSQL

I have DB in PostgreSQL with a big data(now it is somewhere about 46 GB and db will continue to grow). I create index on often used column. Config file change too - shared_buffers = 1GB, temp_buffers = 256MB, work_mem = 512MB.. But query answered very slowly. Query example:

select distinct us_category_id as cat, count(h_user_id) as res from web_hits 
inner join users on h_user_id = us_id 
where (h_datetime)::date = ('2015-06-26')::date and us_category_id != ''
group by us_category_id

Explain Analyse:

HashAggregate (cost=2870958.72..2870958.93 rows=21 width=9) (actual time=899141.683..899141.683 rows=0 loops=1)

Group Key: users.us_category_id, count(web_hits.h_user_id)

-> HashAggregate (cost=2870958.41..2870958.62 rows=21 width=9) (actual time=899141.681..899141.681 rows=0 loops=1)

Group Key: users.us_category_id

-> Hash Join (cost=5974.98..2869632.11 rows=265259 width=9) (actual time=899141.679..899141.679 rows=0 loops=1)

Hash Cond: ((web_hits.h_user_id)::text = (users.us_id)::text)

-> Seq Scan on web_hits (cost=0.00..2857563.80 rows=275260 width=7) (actual time=899141.676..899141.676 rows=0 loops=1)

-> Seq Scan on web_hits (cost=0.00..2857563.80 rows=275260 width=7) (actual time=899141.676..899141.676 rows=0 loops=1)

Filter: ((h_datetime)::date = '2015-06-26'::date)

Rows Removed by Filter: 55051918

-> Hash (cost=4292.99..4292.99 rows=134559 width=10) (never executed)

-> Seq Scan on users (cost=0.00..4292.99 rows=134559 width=10) (never executed)

Filter: ((us_category_id)::text <> ''::text)

"Planning time: 1.309 ms" "Execution time: 899141.789 ms"

Date is changed. How can I speed up the query?

how to make Wildcards consider spaces between

I'm working with a table with three columns( ID | NOTE |TAG)

ID- primary key

NOTE- some things

TAG- contains values separated with space

imagine that id-1 contains TAG- computer javaScript java forton cellphone

and id-2 contains TAG- computer java forton c++ android

When using a Wildcard to search for term java which is in middle of a string the SQLITE also shows the row containing javascript

My query " LIKE '%" + "java" + "%'"; this shows the rows if the word java is found anywhere inside (i.e) it shows java as well as javaScript as well as Scriptinjava

how to make it show only java ??

SQL Server Database recovery from corrupt database

Last week I backed up my SQL Server by using Backup Exec 2012. I named the file "SQL Server BAK" which contained copies of my SQL Server databases. A few days ago I lost some part of my data due to accidental deletion. I backed it up, so I tried to restore the database from the .bkf file. The problem comes here, when I try to to restore my .bkf file, it becomes inaccessible.

Does anyone know what causes this? I'm suspecting corruption here (it's just a suspicion as I'm not sure). Please help me. The copies of my databases are very crucial.

Many thanks in advance.

Combining 2 queries - getting column names in one and using results in another query

Building my first MS Access SQL queries. That should not be this hard!
I have 2 tables:

A user belonging to GroupA logged in. I want to show him only those Data table rows and columns which GroupA is assigned to, like this:

╔════════╦════════╦════════╗
║ Group  ║  Data3 ║ Data4  ║
╠════════╬════════╬════════╣
║ GroupA ║   9    ║   4    ║ 
╠════════╬════════╬════════╣
║ GroupA ║   1    ║   5    ║
╚════════╩════════╩════════╝

I tried this silly option:

SELECT (select Data from AccessRights where GroupA = "y")
FROM Data
WHERE Data.Group = "GroupA";

SQL Server float data type understanding

http://ift.tt/1fY2rQ0

After I insert a value (0.12346789123456789123456789) for example in the table that has a float type column, I query and get back 0.1234567891234568 which contains 17 digits. I have 3 questions

1. How can I back track the binary representation of the input and output ? The document says it uses 53 bits as default. I am using Management Studio SQL Server and I don't know how to specify n value during declaration of my column type.
2. The number 17 isn't included in the document, I wish to know where it comes from.
3. In Big or Little Endian systems, I'd like to know how such an input is treated and translated into the output at the low-level byte system. If anyone knows an explanation, I would be thankful.

Does order matter in SQL?

Let's say I have to run the SQL query:

SELECT data FROM table WHERE condition1 AND condition2 AND condition3 AND condition4 

Is that any different than

SELECT data FROM table WHERE condition3 AND condition1 AND condition4 AND condition2

?

---

If it's not different:

I know from my own experience that condition1 is less expensive than condition2 is less expensive than condition3 is less expensive than condition4.

If any of the prior conditions are not met, the remaining conditions should never be checked. It wouldn't be immediately obvious to the optimizer, as stored functions are involved. How should I write a query that does this?

Check whether particular name order is available in my table

I have the following table stops how can I check whether the following stops name order GHI, JKL, MNO is available in my stops table?

stops table:

CREATE TABLE IF NOT EXISTS stops
(
  stop_id INT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY, 
  name varchar(30) NOT NULL, 
  lat double(10,6), 
  longi double(10,6)
);

Simple:

1 ABC
2 DEF
3 GHI
4 JKL
5 MNO
6 PQR
7 SDU
8 VWX

Get SUM() from mysql JOIN query

I am trying to get a sum(vote_amount) from a joined table 'votes' in this mysql query. Unfortunately it gives me an error. How do I get this query to work?

SELECT d.discussion_id, d.discussion_google_id, d.discussion_title, d.discussion_text, d.discussion_views, d.discussion_last_view_ip, d.discussion_created, d.discussion_updated, u.google_picture_link, v.sum(vote_amount)
FROM discussions AS d
INNER JOIN google_users AS u
ON u.google_id = d.discussion_google_id
INNER JOIN votes AS v
ON v.vote_discussion_id = d.discussion_id
WHERE d.discussion_deleted = 0

The error it gives is:

#1630 - FUNCTION v.sum does not exist. Check the 'Function Name Parsing and Resolution' section in the Reference Manual

can i have an alias for the result table of inner join

Can I have an alias of the result of the join of 2 tables? So that I can call the result of the table in its alias name.

SELECT emp_table.Name, dept_table.dept
FROM dept_table
inner join emp_table
on emp_table.dept_id = dept_table.dept_id;

How do I loop thought each DB field to see if range is correct

I have this response in soapUI:

 <pointsCriteria>
    <calculatorLabel>Have you registered for inContact, signed up for marketing news from FNB/RMB Private Bank, updated your contact details and chosen to receive your statements</calculatorLabel>
    <description>Be registered for inContact, allow us to communicate with you (i.e. update your marketing consent to 'Yes'), receive your statements via email and keep your contact information up to date</description>
    <grades>
       <points>0</points>
       <value>No</value>
    </grades>
    <grades>
       <points>1000</points>
       <value>Yes</value>
    </grades>
    <label>Marketing consent given and Online Contact details updated in last 12 months</label>
    <name>c21_mrktng_cnsnt_cntct_cmb_point</name>
 </pointsCriteria>

There are many many many pointsCriteria and I use the below xquery to give me the DB value and Range of what that field is meant to be:

<return>
{
for $x in //pointsCriteria
return <DBRange>
<db>{data($x/name/text())}</db> 
<points>{data($x//points/text())}</points>
</DBRange>
}
</return>

And i get the below response

<return><DBRange><db>c21_mrktng_cnsnt_cntct_cmb_point</db><points>0 1000</points></DBRange>

That last bit sits in a property transfer. I need SQL to bring back all rows where that DB field is not in that points range (field can only be 0 or 1000 in this case), my problem is I dont know how to loop through each DBRange/DBrange in this manner? please help

How can compare between times in while loop in stored procedure?

Please help me on this, I have been working to do conditions to get the time within the 24 hours only.

So I have something like this. Can anyone help me or tell me if the conditions is correct.

my @DisplayStartTime = 11:00PM

Basically my @nextStartTime = 11:00PM

OPEN cPhosLineTimeSlot

FETCH NEXT FROM cPhosLineTimeSlot
      INTO @Parameter, @DisplayStartTime, @DisplayEndTime, @CodeEndTime, @Frequency, @Tolerance 

WHILE @@FETCH_STATUS = 0
BEGIN
    Declare @nextStartTime nvarchar(30)
    set @nextStartTime = @DisplayStartTime

    --insert into #ActualTimeSlot
    --select @Parameter, @DisplayStartTime, @DisplayEndTime, @CodeEndTime, @Frequency, @Tolerance 

    WHILE (convert(varchar, convert(time, @nextStartTime), 100) < DATEADD(day, -1, GETDATE()))
    BEGIN
        SET @DisplayStartTime = @nextStartTime

        SELECT @nextStartTime = ltrim(right(convert(nvarchar(100), DATEADD(minute, @Frequency, @nextStartTime)), 8))

        INSERT INTO #ActualTimeSlot
            SELECT @Parameter, @DisplayStartTime, 
                   @nextStartTime, @CodeEndTime, @Frequency, @Tolerance 


        SET @intFlag +=1
    END 

    SET @intFlag = 1

    FETCH NEXT FROM cPhosLineTimeSlot
        INTO @Parameter, @DisplayStartTime, @DisplayEndTime, @CodeEndTime, @Frequency, @Tolerance 
END

CLOSE cPhosLineTimeSlot
DEALLOCATE cPhosLineTimeSlot

This is the sample:

Create a view of an account showing hierarchy of tags

we have a database structured as follows:

-Cnsmr_accnt_tag Table (Important fields cnsmr_accnt_id, tag_id)
-Tag Table (Important fields Tag_id, tag_nm, Tag_typ_id)
-Tag_typ Table (Important fields Tag_typ_id, Tag_typ_nm)

The cnsmr_accnt can have multiple tags. Some types of tags are exclusive and other types are not.

So we have an exclusive tag type called 'Area'. (Tag_typ.tag_typ_nm = 'Area') and an account will only have tag with type 'Area'. Then within this we have another exclusive tag type called 'Process'. And finally we have a bunch of other tags which are non-exclusive.

So an account will be in one area and one process but could then have multiple non-exclusive tags.

I am trying to create a query to present data as follows:

CNSMR_ACCNT_ID       |    AREA_TAG     |     PROCESS TAG     |     OTHER TAGS
12345                |Prelegal         |   Early Stage       | System Status Tag
12345                |Prelegal         |   Early Stage       | Active Tag
12352                |Prelegal         |   Early Stage       | System Status Tag
12352                |Prelegal         |   Early Stage       | Active Tag
12370                |Legal            |   Early Stage       | System Status Tag
12370                |Legal            |   Early Stage       | Active Tag
12370                |Legal            |   Early Stage       | Other Tag1
12370                |Legal            |   Early Stage       | Other Tag2

Any suggestions as to how to do this?

What is the Max function equivalent of SQL in LINQ?

I have this SQL query that I need to convert to LINQ:

SELECT * FROM USER U
INNER JOIN (
    SELECT USERID , MAX(SALESDATE) AS MAXDATE
    FROM SALES
    GROUP BY USERID
) S ON U.ID = S.USERID

so far, this is what I've come up with LINQ:

var Users = (from d in db.Users
             join s in db.Sales on d.Id equals s.UserId
             select new Models.User
             {
                Id = d.Id,
                UserName = d.UserName,
                FirstName = d.FirstName,
                LastName = d.LastName,
                EmailAddress = d.EmailAddress,
                PhoneNumber = d.PhoneNumber,
                LastPurchase = s.SalesDate
             }).Max(x => x.SalesDate);

However, I'm pretty sure the result is not the same. Can anyone suggest the correct way of converting this to LINQ? I'm still new to learning LINQ. I really appreciate the help.

How to add company names to biggest file list in postgres

Postgres 9.1+ database contains different schema for every company named firma and company number, like firma1, firma5, firma99, firma12.

Every schema contains table with company name:

-- this table contains always exactly one row:
create table firma5.company ( company char(50) not null );

Following query list biggest tables:

select
(n.nspname||'.'||relname)::char(45) as tablename
    , pg_size_pretty(pg_total_relation_size(c.oid))::char(10) as totalsize
    , case
        when c.relkind='i' then 'index'
        when c.relkind='t' then 'toast'
        when c.relkind='r' then 'table'
        when c.relkind='v' then 'view'
        when c.relkind='c' then 'composite type'
        when c.relkind='S' then 'sequence'
        else c.relkind::text
      end ::char(14) as "type"
from
    pg_class c
    left join pg_namespace n on n.oid = c.relnamespace
    left join pg_tablespace t on t.oid = c.reltablespace
where
    (pg_total_relation_size(c.oid)>>20)>0 and c.relkind!='t'
order by
    pg_total_relation_size(c.oid) desc

This query shows company schemas like firma1, firma5 etc.

How to show company names ( firman.company.company fields ) also in this query result ? Query may return also tables from schemas other than firmaN . In this case company name column should be empty or null.

How can i get the path of .sql file in my php website

I have a website code and i don't know where to place the .sql file in my website folder.

How to get all rows that has same ID but diffrent value in other column?

I'm having a trouble writing a query in PostgreSQL. I have a View that contains IDs with subID. for example:

ID  quantity partID 
100   50       10
100   20       10
100   30       11
101   50       13
101   70       13
102   20       17

I want to get all rows that has same ID but different partIDs. for the given example I would like to get:

ID  quantity partID 
100   50       10
100   20       10
100   30       11

i tried this query query:

select id  ,quantity ,partid 
from A
group by id,quantity,partid
having count(id)>2
order by id

but it doesn't work. It accualty checks if ID appears in more than 2 rows... in the given example it will take ID 101 as well. I also don't know how to make it choose only partid which are diffrent per ID.

Entity Framework - code first - Too many navigation properties

I have two tables created with Entity Framework code first that I would like some help with..!

Tables

1. AccountLinks, 3 composite keys
2. Guest, 3 composite foreign keys (?)

Table overview

SQL overview

As you can see I have ALOT of navigation properties in my database which I dont want.

Code for AccountLink

public class AccountLink
    {
        public AccountLink()
        {
            AccountLinkPermissionAccountLinkID = new HashSet<AccountLinkPermission>();
            AccountLinkPermissionAccountOwnerID = new HashSet<AccountLinkPermission>();
            AccountLinkPermissionGuestID = new HashSet<AccountLinkPermission>();
        }

        public AccountLink(int accountOwnerID, int guestID, DateTime dateCreated, DateTime dateStart, DateTime dateExpires)
        {
            AccountLinkPermissionAccountLinkID = new HashSet<AccountLinkPermission>();
            AccountLinkPermissionAccountOwnerID = new HashSet<AccountLinkPermission>();
            AccountLinkPermissionGuestID = new HashSet<AccountLinkPermission>();
            this.AccountOwnerID = accountOwnerID;
            this.GuestID = guestID;
            this.DateCreated = dateCreated;
            this.DateStart = dateStart;
            this.DateExpires = dateExpires;
        }

        [Key, Column(Order = 0)]
        public int AccountLinkID { get; set; }
        [Key, Column(Order = 1)]
        public int AccountOwnerID { get; set; }
        [Key, Column(Order = 2)]
        public int GuestID { get; set; }

        public DateTime DateCreated { get; set; }
        public DateTime DateStart { get; set; }
        public DateTime DateExpires { get; set; }

        [ForeignKey("AccountOwnerID")]
        public virtual AccountOwner AccountOwner { get; set; }

        [ForeignKey("GuestID")]
        public virtual Guest Guest { get; set; }

        public virtual ICollection<AccountLinkPermission> AccountLinkPermissionAccountLinkID { get; set; }
        public virtual ICollection<AccountLinkPermission> AccountLinkPermissionAccountOwnerID { get; set; }
        public virtual ICollection<AccountLinkPermission> AccountLinkPermissionGuestID { get; set; }
    }

Code for AccountLinkPermissions

public class AccountLinkPermission
    {
        public AccountLinkPermission()
        {

        }

        public AccountLinkPermission(int accountLinkID, int accountOwnerID, int guestID, int permissionID)
        {
            this.AccountLinkID = accountLinkID;
            this.AccountOwnerID = accountOwnerID;
            this.GuestID = guestID;
            this.PermissionID = permissionID;
        }

        [Key, Column(Order = 0)]
        public int AccountLinkID { get; set; }
        [Key, Column(Order = 1)]
        public int AccountOwnerID { get; set; }
        [Key, Column(Order = 2)]
        public int GuestID { get; set; }
        [Key, Column(Order = 3)]
        public int PermissionID { get; set; }

        [InverseProperty("AccountLinkPermissionAccountLinkID")]
        public virtual AccountLink AccountLink { get; set; }

        [InverseProperty("AccountLinkPermissionAccountOwnerID")]
        public virtual AccountLink AccountLinkAccountOwner { get; set; }

        [InverseProperty("AccountLinkPermissionGuestID")]
        public virtual AccountLink AccountLinkGuest { get; set; }

        [ForeignKey("PermissionID")]
        public virtual Permission Permission { get; set; }
    }

The reason to why I want 3 composite keys is because I want to prevent duplicates.

Why I use InverseProperty instead of ForeignKey

Because I'm using multiple foreign keys linked to the same table, EF is not able to determine by convention which navigation properties belong together. Instead of using the property [ForeignKey] I have to use [InverseProperty] which defines the navigation property on the other end of the relationship.

What I need help with

How do I remove all the navigation properties in my database using code first? I know I messed it up somewhere but that's all I know :)

1 Navigation property in AccountLinks, (User_UserID)

9 Navigtion properties in AccountLinkPermissions

Bonus question

In AccountLink table I have three composite keys, AccountLinkID, AccountOwnerID and GuestID. Is it possible to put auto increment, (identity seed), on AccountLinkID? How would I do that in EF code first?

PL/Python: How to return an empty set?

I have a function that operates on int[]. I want to have the ability to check if the array is empty and in that case to stop since there is nothing to do.

this is my funcion:

CREATE OR REPLACE FUNCTION func2(c integer[])
  RETURNS SETOF func_type AS
$BODY$
result=plpy.execute("SELECT * FROM func1(ARRAY%s)"%c)
return result;
$BODY$
  LANGUAGE plpythonu VOLATILE

func_type defined as:

CREATE TYPE func_type AS
   (x integer,
    y numeric,
    z integer,
    zz integer);

when I added a check:

$BODY$
if not c:
    return  

I got an error:

ERROR:  returned object cannot be iterated
DETAIL:  PL/Python set-returning functions must return an iterable object.

I understand that I have to return func_type and func_type was not built since there was no plpy.execute but still how can I make it return at this point? Also, assume func3 called func2. How would func3 can check that there are 0 rows returned?

Literature list to master SQL / Oracle DB asap

I'm primary a web developer. During last 4 years I got experience with different programming languages and technologies. And now I made a decision to go on and to try something new. The result of my choice is diving into database administration.

Currently my knowledge in this sphere consist of:

• base SQL knowledge (I know how to create/update/delete database structures, how to perform SELECT/INSERT queries with JOINS etc.),
• experience in working with MySQL,
• brief experience with Oracle DB

My biggest problem is that I'm relocating to another country after one month, so this is the time I have to prepare myself for the DBA vacancy. To avoid misunderstandings, I have to tell that I am a realist. And I perfectly understand that 30 days is an extremely short period of time and it's definitely not enough to become an expert in one of the IT spheres. What I am trying to achieve is to learn as much as possible during this period of time.

Could you please give me a peace of advice about what books may be useful for me in this situation? Currently I am finishing reading Learning SQL by Alan Beaulieu. I find it really useful to fresh up SQL language itself.

How to gather hierarchical data items and build a tree considering their dependencies

There is a table containing hierarchical data, e.g.:

| table "attribute_instances"                           |
+----+----------+------------+---------------+----------+
| id | tree_ref | parent_ref | attribute_ref | data_ref |
+----+----------+------------+---------------+----------+
|  1 |        1 |         -1 |             1 |        1 |
|  2 |        1 |          1 |             2 |        2 |
|  3 |        2 |         -1 |             1 |        3 |
|  4 |        2 |          3 |             2 |        2 |

It contains many separate trees (see tree_ref), each of them instantiating some attributes (see attribute_ref) and have a data reference data_reference, where data might be referenced in other trees, too.

Now, those trees should be merged into a single tree, in which (by now) up to 5 attributes may be chosen as level for that tree, e.g.:

attribute => level
------------------
        2 =>     1
        1 =>     2

What I need is one or more queries, that collects the data from table attribute_instances and gives a result as follows:

| table "merged_attribute_instances"         |
+----+------------+---------------+----------+
| id | parent_ref | attribute_ref | data_ref |
|  5 |         -1 |             2 |        2 |
|  6 |          5 |             1 |        1 |
|  7 |          5 |             1 |        3 |

This is the desired merged tree:

id:5 - data_ref:2
  id:6 - data_ref:1
  id:7 - data_ref:3

Note, that attribute_ref = 2 occurs only once in the resulting tree, as all instances of it have same data_ref value (that is 2).

I've tried some joins like

select *
  from attribute_instances a
  join attribute_instances b on a.tree_ref = b.tree_ref

But that seems to me being bad for having user-defined tree depth. I'm sure there is a better solution.

UPDATE: I should add, that table merged_attribute_instances is a temporary table. And the collecting query is iterated with for..do. In the loop the collected attribute_instances are then added to the temporary table.

SQL Server Update Query says table already exits

I have two tables in my database - MPRS and ALL_SSC. I want to update the ALL_SSC table with data from MPRS and in Access (where I test my SQL) it works.

However, when I run it in my VB 2010 program on the SQL Server database, it says table MPRS already exists.

I know it does! I'm not trying to create it. I'm updating FROM it... any ideas where this SQL is wrong?

SQL = "UPDATE ALL_SSC LEFT JOIN MPRS ON MPRS.MPAN = ALL_SSC.MPAN1 SET ALL_SSC.PC1 = Format([mprs].[pc],'00'), ALL_SSC.MSPCDC1 = Mid([mprs].[PC_EFD],7,4) & Mid([mprs].[PC_EFD],4,2) & Mid([mprs].[PC_EFD],1,2), ALL_SSC.MTSC1 = [mprs].[MTC], ALL_SSC.MSMCDC1 = Mid([mprs].[MTC_EFD],7,4) & Mid([mprs].[MTC_EFD],4,2) & Mid([mprs].[MTC_EFD],1,2), ALL_SSC.LLF1 = [mprs].[LLF], ALL_SSC.SUPPLIER1 = [mprs].[SUPPLIER], ALL_SSC.REGI1 = Mid([mprs].[SSD],7,4) & Mid([mprs].[SSD],4,2) & Mid([mprs].[SSD],1,2), ALL_SSC.ENG_STATUS1 = 0 WHERE (((ALL_SSC.MPAN1) Is Not Null) AND ([mprs].[ENERG_STATUS]='E'));"
cmd = New SqlCommand(sSQL, cNN)
Try
    Try
    If cNN.State <> ConnectionState.Open Then
        cNN.Open()
    End If

    Catch exCnn As Exception
    MsgBox(exCnn.Message)
    End Try
    cmd.ExecuteNonQuery()
Catch ex As Exception
    MsgBox("Cannot continue. " & ex.Message)
    Exit Sub
End Try

Troubles with using GROUP BY in SQL Query

I'm trying to write a propper SQL query in MS SQL Server. First of all, i have the following tables: Towns, Employees, Addresses. Almost every employee has Manager, whom ManagerID is foreign key in Employees also. (Self relation). My goal is to display the number of managers from each town. So far i have this code:

SELECT t.Name, COUNT(*) AS [Managers from each town] FROM Towns t
JOIN Addresses a
ON t.TownID = a.TownID
JOIN Employees e
ON a.AddressID = e.AddressID
GROUP BY t.Name
ORDER BY [Managers from each town] DESC

This query returns the number of Employees, from each town, not Managers. If i try the second query bellow, I get something totally wrong:

SELECT t.Name, COUNT(*) AS [Managers from each town] FROM Towns t
JOIN Addresses a
ON t.TownID = a.TownID
JOIN Employees e
ON a.AddressID = e.AddressID
JOIN Employees m
ON e.ManagerID = m.ManagerID

GROUP BY t.Name
ORDER BY [Managers from each town] DESC

Here is the structure of 'Employees' table:

EmployeeID, FirstName, LastName, MiddleName, JobTitle,DepartamentID, ManagerID, HireDate, Salary, AddressID

The correct query must return this result set:

Town          | Managers from each town
Issaquah      | 3
Kenmore       | 5
Monroe        | 2
Newport Hills | 1

how do i delete a row from the table that has the primary key used as foreign key in my other table?

i have created a foreign key relationship between two tables in my database diagram in sql server 2008. when i try to delete a row from the table containing the primary key there is an error. how do i delete a row from the table that has the primary key used as foreign key in my other table?

how to get deleted data from tables in Oracle

Can anyone help me retrieve deleted data in Oracle SQL*Plus?

here are the two tables that the data has been deleted from.be informed that only data of current date (T_DT = 28-JUN-15') has been accidentally deleted and the sales center id (SC_CD) selected as 27.

SQL> desc targ_mas
 Name                            Null?    Type
 ------------------------------- -------- ----
 SC_CD                           NOT NULL VARCHAR2(2)
 FP_ID                           NOT NULL VARCHAR2(4)
 T_DT                            NOT NULL DATE
 T_PCT                                    NUMBER(3)
 T_TARG                                   NUMBER(9,2)
 FL_MVH                          NOT NULL VARCHAR2(1)

SQL> 
SQL> 
SQL> desc prod_targ
 Name                            Null?    Type
 ------------------------------- -------- ----
 ASM_ID                                   VARCHAR2(4)
 RM_ID                                    VARCHAR2(4)
 FM_ID                                    VARCHAR2(4)
 FP_ID                           NOT NULL VARCHAR2(4)
 SC_CD                           NOT NULL VARCHAR2(2)
 TYP_CD                          NOT NULL VARCHAR2(2)
 DIV_CD                                   VARCHAR2(1)
 CAT_CD                          NOT NULL VARCHAR2(2)
 PRD_CD                          NOT NULL VARCHAR2(4)
 T_DT                            NOT NULL DATE
 QTY_TARG                                 NUMBER(8,2)
 T_VAL                                    NUMBER(10,2)
 TP_VAL                                   NUMBER(9,2)
 BR_CD                                    VARCHAR2(3)
 BS_CD                                    VARCHAR2(4)
 RMBS_CD                                  VARCHAR2(2)

queries used to delete data from both tables :

delete from targ_mas
where sc_cd = '27'
and t_dt = '28-JUN-15'


delete from prod_targ
where sc_cd = '27'
and t_dt = '28-JUN-15'

thanks. Iftekhar

Ordering dot-delimited numeric sequences (e.g., version numbers)

In my database I've column fill this data:

1
1.1
1.1.1
1.1.1.1
1.1.2
1.10
1.11
1.2
1.9

I want to sort it, to get result looks like this:

1
1.1
1.1.1
1.1.1.1
1.1.2
1.2
1.9    
1.10
1.11

How can I accomplish this? Simple use "ORDER BY" gives wrong result because it's lexicographic order.

NOLOGGING is not functioning

This is a part of my code. Everything is ok, no error. But I have million of lines to be inserted. I searched the internet for ways to finish my job faster.

What I found, is that insert in table using nologgin and +append, but is not working. Time to insert the same lines in table is the same even I use nologging and append.

    create or replace procedure read_files(input varchar2, extensie varchar2) as  
........................................ 
    Loop
     BEGIN
..............................
        UTL_FILE.GET_line(F1,V1);
    insert /*+ append */ into alarms(alarm_id,property_name,property_value) 
    =values(alarm_counter,f_property_name,f_property_value) ;

     End loop;
    end; 


alter table alarms nologging;
execute read_files('occ','cap');
alter table alarms logging;

Steps for my work:

• first compile the procedure
• alter table nologging
• execute procedure

Where is my mistake?

SQL Server: IF EXISTS massively slowing down a query

(SQL Server 2012 being used)

I found some topics on query optimization, and comparing EXISTS to COUNT, but I couldn't find this exact problem.

I have a query that looks something like this:

select * from
tblAccount as acc
join tblUser as user on acc.AccountId = user.AccountId
join tblAddress as addr on acc.AccountId = addr.AccountId
... **a few more joins**
where acc.AccountId in (
    select * accountid from
    (select accountid, count(*) from tblUser
    where flag = 1
    group by accountId) as tbl where c != 1

This query runs in an instant (although the db is quite big, around 70Gb).

When I wrap the query in an EXISTS as in:

if exists
(
  **Exact same query as above**
)
begin
RAISERROR('Account found without exactly one flagged user.', 16, 1);
end
else
begin
  print 'test passed.'
end

Suddenly the query takes about 5-6 seconds to complete. I've tried specifying IF EXISTS (SELECT TOP 1 FROM... and also tried NOT EXISTS (which was even slower). But neither work to speed this up.

If the normal select query completes basically instantly, then does anyone know why wrapping it in the EXISTS causes so much extra computation? And/or anyone have any ideas to work around this (I'm just trying to throw an error if any records are found at all by the original query).

Thanks!

SQL Server | Is my stored procedure is OK?

CREATE PROCEDURE spCountTableRowWHere
    @TblName VARCHAR(50),
    @TblID VARCHAR(10) = 'Id',
    @WhereClause NVARCHAR(500) = '1=1'
AS
BEGIN
    DECLARE @Query NVARCHAR(500)
    DECLARE @ParamDefinition NVARCHAR(40)
    DECLARE @Count INT
    SET @Query = 'SELECT @C = COUNT('+@TblID+') FROM '+@TblName+' WHERE '+@WhereClause
    SET @ParamDefinition = '@C INT OUTPUT'

    EXECUTE SP_EXECUTESQL @Query, @ParamDefinition, @C = @Count OUTPUT
    SELECT @Count
END

I am new in SQL and I am wondering if this kind of procedure is better than a separate procedures for different tables.

How to store XML field values in database using php

I have an XML in local server. I want to store values in databse using Php. How to do it. Any sample code plz to achieve it.

SQL - Count of Sessions and transactions between session

I am trying to write a HiveQL (or even ANSI SQL) query that can answer the following:

I have a database with events (transactions), each event is stored with a user_id and a trans_time (transaction time). A user_id can have unlimited events, even possible to have multiple events with the same trans_time.

If I establish an activity window of time (i.e. 10 seconds), how many windows had how many events within that window? I'm thinking the result would be something like this (obviously without the words, just numbers)... 100 windows had only 1 event 50 windows had 2 events . . 1 window that had 30 events

To describe the first result...There were 100 instances in the data set where only 1 event took place within the 10 second window.

Is there a way to pull another metric out of the same data that says, 50 user_ids had only 1 event 25 user_ids had 2 events . . 1 user_id had 30 events

To describe the first result...There were 50 user_ids that had only 1 event within the 10 second window.

I hope this is not too nebulous. As always, thank you!!

Display the number of before held training

I am new in Laravel and I used Laravel 5 for my project. My specification is count the number of before held training according to category of training so i used join and group by and count function to get the number before held training:

In my controller code is as below:-

<?php

namespace App\Http\Controllers;
use Request;
use App\Training;
use App\Schedule;
use App\User;
use App\Http\Requests;
use App\Http\Controllers\Controller;
use App\Http\Requests\TrainingRequest;
use Carbon\Carbon;


  class TrainingController extends Controller
  {
/**
 * Display a listing of the resource.
 *
 * @return Response
 */

public function __construct(){

$this->middleware('auth',['only'=>'index']);
}
public function index()
{
    //


    $training=Training::all();

        $before_held=Schedule::join('training','training.id','=','schedule.training_id')
                 ->groupBy('training.category')->where('schedule.training_end_date','>=',carbon::now())->get();
   // echo count($before_held);
   //die();


    return view('Training.index',compact('training','before_held'));
}

In View:-

 <td><a href="/training/before_held">{{count($before_held)}}</a></td>

This always show 0 count in the table like this:-

|category|Held Before|
|test    |   0       |
|test    |   0       |

But I need count two if training_end_date greater than or equal to current time.

SO I am thankful if anyone can solve this issue.

Symfony2 twig print values from query on 2 non related tables

I have 2 entities: "Requests" and "Partners" There is a 1to1 relation between Requests and Partners so there is a partners_id field in Requests table I want to display a Partners list, but I need to take a value from a "price" field in Requests table

On my controller I have this query:

$listPartners = $em
    ->getRepository('OandPboBundle:Partners')
    ->createQueryBuilder('p')
    ->select('p')
    ->leftJoin('OandPboBundle:Requests', 'r', 'WITH', 'r.partners = p.id')        
    ->where('p.date LIKE :date AND p.active = :active')
           ->setParameter('date', '%'.$year.'-'.$month.'%')
           ->setParameter('active', 1)

    ->orderBy('p.date', 'DESC')
    ->addOrderBy('p.id', 'DESC')
    ->getQuery()
    ->getResult();

And in my TWIG file I have

{% for partners in listPartners %}
            <tr>
                <td>{{ partners.id }}</td>
                <td>{{ partners.price }}</td>
            </tr>
{% endfor %}

And of course there is an error because they say there is no "price" field in "Partners" (of course I want to take it from "Quotations")

Is there a way to do that?