Mysql INNER JOIN does not work for me

I'm trying to make my SQL query work and I fail, so I decided to ask more experienced and familiar with SQL people since I'm not so.

What I have: 2 tables in my DB, one is "DEV" table that contains: id, lat, lon, login, password second one is "TASK" table which contains: id, lat, lon, address, id_dev. Id_dev is a foreign key to table "DEV".

What I'm trying to do is: Make query to get all DEVs that have NO task assigned (there is no record in table "task" with given dev.id) and get another list of DEVs that have tasks.

I want them separated. I tried something from a tutorial:

SELECT * FROM `dev` INNER JOIN 'task' ON dev.id=task.id_dev ORDER BY dev.id;

But it didn't work for me. Any suggestions please? Kind regards!

---

If you want the 'dev' records with no 'task' you shouldn't use INNER JOIN as that brings back the intersection of the sets.

One option is to use a LEFT JOIN, so something like:

SELECT      dev.* 

FROM        dev

LEFT JOIN   task
    ON      dev.id=task.id_dev 

WHERE       task.id_dev IS NULL

ORDER BY    dev.id;




Mysql Right Join works, Inner Join works, Left join does not? Why?

I have some SQL joining multiple tables in Access. When attempting to run it, I get an error in the "JOIN" (specifically "JOIN expression not supported"). I believe I have narrowed down the problem to one join but why it isn't working doesn't make any sense to me. The original full SQL FROM clause is thus:

FROM  (
     (
      Customers RIGHT JOIN
            (
            Sales LEFT JOIN SaleType ON Sales.SalesForID = SaleType.SalesForID
            )
      ON Customers.CustomerID = Sales.CustomerID
     ) LEFT JOIN
          (
           StudentContracts LEFT JOIN
               (
               StudentsClasses INNER JOIN Classes ON StudentsClasses.ClassID = Classes.ClassID
                )
            ON StudentContracts.CustomerID = StudentsClasses.CustomerID
           )
       ON Customers.CustomerID = StudentContracts.CustomerID
 )

The part I believe the query fails is on this "LEFT" join:

(
  StudentContracts LEFT JOIN
          (
          StudentsClasses INNER JOIN Classes ON StudentsClasses.ClassID = Classes.ClassID
          )
   ON StudentContracts.CustomerID = StudentsClasses.CustomerID
)

I've tried switching the the "LEFT" to an "INNER" and it works. I've switched it to a "RIGHT" and it works. Why will it not work for a "LEFT" join but work for the others? What I need is a result showing the records in joined "Classes" table linked to the StudentContracts but also the StudentContracts without a record in the Classes table. As per the answer on this post: Difference between left join and right join in SQL Server I am fairly certain I want a left join and this should work.

What am I missing here?

---

You have far too many parentheses and things are not in the right order. It would be easiest to build this in the query design window of MS Access, and then everything will be generated for you, you can switch to SQL view to see.

For example, the above should read something like:

SELECT *
FROM (Customers
RIGHT JOIN Sales
ON Customers.CustomerID = Sales.CustomerID)
LEFT JOIN SaleType
ON Sales.SalesForID = SaleType.SalesForID

It is the convention that RIGHT JOIN is avoided because they can easily be written as LEFT JOIN, so it avoids any confusion.

Mysql query with count subquery, inner join and group

I'm definitely a noob with SQL, I've been busting my head to write a complex query with the following table structure in Postgresql:

CREATE TABLE reports
(
  reportid character varying(20) NOT NULL,
  userid integer NOT NULL,
  reporttype character varying(40) NOT NULL,
)

CREATE TABLE users
(
  userid serial NOT NULL,
  username character varying(20) NOT NULL,
)

The objective of the query is to fetch the amount of report types per user and display it in one column. There are three different types of reports.

A simple query with group-by will solve the problem but display it in different rows:

select count(*) as Amount,
       u.username,
       r.reporttype
from reports r,
     users u
where r.userid=u.userid
group by u.username,r.reporttype
order by u.username

---

SELECT
  username,
  (
  SELECT
    COUNT(*)
  FROM reports
  WHERE users.userid = reports.userid && reports.reporttype = 'Type1'
  ) As Type1,
  (
  SELECT
    COUNT(*)
  FROM reports
  WHERE users.userid = reports.userid && reports.reporttype = 'Type2'
  ) As Type2,
  (
  SELECT
    COUNT(*)
  FROM reports
  WHERE users.userid = reports.userid && reports.reporttype = 'Type3'
  ) As Type3
FROM
  users
WHERE
  EXISTS(
    SELECT
      NULL
    FROM
      reports
    WHERE
       users.userid = reports.userid
  )

How to optimize a simple MySQL query with lots of INNER JOINS

I've found info on how to optimize MySQL queries, but most of the tips seem to suggest avoiding things MySQL isn't built for (e.g., calculations, validation, etc.) My query on the other hand is very straight forward but joins a lot of tables together.

Is there an approach to speeding up simple queries with many INNER JOINS? How would I fix my query below?

SELECT t_one.id FROM table_one t_one
INNER JOIN entr_to_state st
INNER JOIN entr_to_country ct
INNER JOIN entr_to_domain dm
INNER JOIN entr_timing t
INNER JOIN entr_to_weather a2w
INNER JOIN entr_to_imp_num a2i
INNER JOIN entr_collection c
WHERE t_one.type='normal'
AND t_one.campaign_id = c.id
AND t_one.status='running'
AND c.status='running'
AND (c.opt_schedule = 'continuous' OR (c.opt_schedule = 'schedulebydate'
AND (c.start_date <= '2011-03-06 14:25:52' AND c.end_date >= '2011-03-06 14:25:52')))
AND t.entr_id = t_one.id AND ct.entr_id = t_one.id
AND st.entr_id = t_one.id AND a2w.entr_id = t_one.id
AND (t_one.targeted_gender = 'male' OR t_one.targeted_gender = 'both')
AND t_one.targeted_min_age <= 23.1 AND t_one.targeted_max_age > 23.1
AND (ct.abbreviation = 'US' OR ct.abbreviation = 'any')
AND (st.abbreviation = 'CO' OR st.abbreviation = 'any')
AND t.sun = 1 AND t.hour_14 = 1
AND (a2w.weather_category_id = 1 OR a2w.weather_category_id = 0)
AND t_one.targeted_min_temp <= 46
AND t_one.targeted_max_temp > 46 GROUP BY t_one.id

---

Index all relevant fields, of course, which I'm sure you have

Then find which joins are the most costly ones by running EXPLAIN SELECT...

Consider splitting them off into a seperate query i.e. narrow down the record(s) you're looking for, then perform the joins on those records rather than all the records

i.e.

SELECT c.*, ....
FROM (SELECT x, y, z .... ) AS c




MYSQL where statement does not work as expected

I'm trying to count the number of rows in this query, however it's not working as expected, this returns an extra row, it should be 12, but it's 13.

$numPhotos = mysql_num_rows(mysql_query("
SELECT albums.id
FROM albums, albumData
WHERE
(albumData.id=albums.albumID OR albums.albumID=0)
AND
albums.userID=$id
AND albums.state=0
AND albumData.state=0
"));

When I remove the OR statement part, and not count the rows with albumID=0, it returns 11. There is only one row where the albumID is 0, but it counts it as two?

$numPhotos = mysql_num_rows(mysql_query("
SELECT albums.id
FROM albums, albumData
WHERE
albumData.id=albums.albumID
AND
albums.userID=$id
AND albums.state=0
AND albumData.state=0
"));

---

Try to write query using JOIN:

SELECT albums.id FROM albums
LEFT JOIN albumData
   ON albums.albumID=albumData.id
  AND albums.state=0
  AND albumData.state=0
WHERE albums.userID=$id

or

SELECT albums.id FROM albums
LEFT JOIN albumData
   ON albums.albumID=albumData.id
WHERE albums.userID=$id
  AND albums.state=0
  AND albumData.state=0

Does this solve your trouble?

EDITED:
Try this

SELECT DISTINCT albums.id FROM albums
INNER JOIN albumData
   ON (albums.albumID = albumData.id OR albums.albumID = 0)
  AND albums.state=0
  AND albumData.state=0
WHERE albums.userID=$id