CODEWITHSUNDEEP
phpmysqlsql
Linesofcode
Linesofcode

Reputation: 5891

Query to retrieve values that don't exist between tables

I need to retrieve values that doesn't exist in another table. I'm working with Shares and Share_types tables.

Currently, I have this working with PHP but I'm always looping all over the 2,500~ rows of Share_types and I think it is possible to solve the amount of rows with a query.

The user currently goes through this process:

  1. Select the type of share - Anual share
  2. Select the year that will be extracted - 2016
  3. Code will generate all shares until the year of 2016 that weren't yet generated. This means, that the years behind of 2016 will also be generate if they don't exist.

That said, my PHP code is like the following:

// Retrieves always the 2,500~ rows
$listOfShareTypes = "SELECT user_id, date_start FROM share_types WHERE type = 'Anual share'";

foreach($listOfShareTypes as $type)
{
   // Now is where I validate if the share already exists
   $hasShare = "SELECT COUNT(*) FROM shares WHERE the_year = $yearSelectedByUser, user_id = $type->user_id, share_type_id = $type->id";

   if($hasShare == TRUE)
      continue;
   else
      // Create the share..
}

So usually, to retrieve via query the results that doesn't exist in another table I would do two select in a query, but after a few searches it points to the use of LEFT JOIN. However I have no clue how to accomplish this since I need to match several fields (user_id, share_type_id, year, etc.)

Looking at this example I created on SQLFiddle, the result should be:

(5, 3, 'Anual', '2015-06-28')

And with this result and since the user selected the year of 2016 I should loop (in PHP) from 2015 until 2016.

Upvotes: 3

Views: 66

Answers (2)

Naisa purushotham
Naisa purushotham

Reputation: 913

try this query

SELECT user_id, date_start FROM share_types st,shares sh on sh.share_type_id=st.user_id   WHERE  type = 'Anual share' and sh.user_id=st.id and sh.the_year=$yearSelectedByUser and  Having COUNT(sh.user_id) < 1;

Upvotes: 0

Vamsi Prabhala
Vamsi Prabhala

Reputation: 49260

You were using the wrong column in the join condition. The tables should be joined on user_id.

SQL Fiddle

SELECT stype.id, stype.user_id, stype.type, stype.date_start
FROM share_types AS stype
LEFT JOIN shares AS share ON share.user_id = stype.user_id
WHERE share.share_type_id IS NULL

Upvotes: 2

Related Questions