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.