== Performing SQL Queries ==

== SQL JOINs ==

{{:swdev:howto:sql_joins.svg.png?700|Visual Representation of SQL Joins}}

  * INNER JOIN: returns rows when there is a match in both tables. <code sql>SELECT *
FROM `t1`
INNER JOIN `t2` ON `t1`.`id` = `t2`.`id`;</code>
  * LEFT JOIN: returns all rows from the left table, even if there are no matches in the right table. <code sql>SELECT *
FROM `t1`
LEFT OUTER JOIN `t2` ON `t1`.`id` = `t2`.`id`;</code>
  * RIGHT JOIN: returns all rows from the right table, even if there are no matches in the left table. <code sql>SELECT *
FROM `t1`
RIGHT OUTER JOIN `t2` ON `t1`.`id` = `t2`.`id`;</code>
  * FULL JOIN: returns rows when there is a match in one of the tables. <code sql>/* MySQL requires a UNION */
(SELECT ... FROM tbl1 LEFT JOIN tbl2 ...) 
UNION ALL /* to see duplicates (instead of UNION) */
(SELECT ... FROM tbl1 RIGHT JOIN tbl2 ... 
WHERE tbl1.col IS NULL /* To display duplicates in MySQL */)</code>
  * SELF JOIN: is used to join a table to itself as if the table were two tables, temporarily renaming at least one table in the SQL statement.
  * CARTESIAN JOIN: returns the Cartesian product of the sets of records from the two or more joined tables.

Source:
  * [[http://www.codeproject.com/Articles/33052/Visual-Representation-of-SQL-Joins|CodeProject: Visual Representations of SQL Joins]]
  * [[http://en.wikipedia.org/wiki/Join_(SQL)|Wikipedia: JOIN (SQL)]]
  * [[http://stackoverflow.com/questions/5706437/whats-the-difference-between-inner-join-left-join-right-join-and-full-join|StackOverflow: Difference between JOINs]]
  * Image: [[http://commons.wikimedia.org/wiki/File%3aSQL_Joins.svg|SQL JOINs]]
== Sample Queries ==

=== Find Distinct Records (keeping the higher value) ===

Sample Data
  id	name	value
  1	aaaa	4
  1	aaaaa	5
  1	aaa	4
  2	bbb	3
  3	ccc	2
  5	ddd	1
  5	ddddd	2


Find the distinct records only and retrieve those with the highest value:
<code sql>
SELECT d1.id, d1.name, d1.value, MAX(d1.value) AS max1, 
  /* --- id --- */
  ( SELECT id FROM duprecs D2
    WHERE d2.id = d1.id
      AND d2.value = MAX(d1.value)
	) AS maxid,
  /* --- name --- */
  ( SELECT name FROM duprecs D2
    WHERE d2.id = d1.id
      AND d2.value = MAX(d1.value)
	) AS maxname,
  /* --- value --- */
  ( SELECT value FROM duprecs D2
    WHERE d2.id = d1.id
      AND d2.value = MAX(d1.value)
	) AS maxvalue
FROM duprecs D1
GROUP BY id
</code>



=== Delete Duplicates (keeping the highest value) ===
Sample Data

  tariffid	countryprefixid	rate	blockcall
  GEONETCOMCC	1800		6.5	TRUE
  GEONETCOMCC	1888		6.5	TRUE
  GEONETCOMCC	1866		6.5	TRUE
  GEONETCOMCC	1877		6.5	TRUE
  GEONETCOMCC	9375		25.5	FALSE
  GEONETCOMCC	9340		25.5	FALSE
  GEONETCOMCC	9360		30.55	FALSE
  GEONETCOMCC	9320		25.5	FALSE
  GEONETCOMCC	9330		25.5	FALSE
  GEONETCOMCC	9350		30.55	FALSE
  GEONETCOMCC	9370		27.85	FALSE
  GEONETCOMCC	9377		26.5	FALSE
  GEONETCOMCC	9378		27.85	FALSE
  GEONETCOMCC	9379		26.25	FALSE
  GEONETCOMCC	930		30.55	FALSE
  GEONETCOMCC	931		30.55	FALSE

Same data, but displaying only the duplicate records:
  rate		tariffid	countryprefixid	blockcall
  8.8299999	GEONETCOMCC	1809985		f
  8.8299999	GEONETCOMCC	1809985		f
  18.4		GEONETCOMCC	202		f
  18.4		GEONETCOMCC	202		f
  17.700001	GEONETCOMCC	2252		f
  17.700001	GEONETCOMCC	2252		f
  27		GEONETCOMCC	23222		f
  27		GEONETCOMCC	23222		f
  11.5		GEONETCOMCC	23321		f
  12		GEONETCOMCC	23321		f
  12		GEONETCOMCC	23322		f
  12		GEONETCOMCC	23322		f
  14		GEONETCOMCC	2341		f
  15.75		GEONETCOMCC	2341		f
  46.5		GEONETCOMCC	25262		f
  46.5		GEONETCOMCC	25262		f
  46.5		GEONETCOMCC	25265		f
  46.5		GEONETCOMCC	25265		f

  * This SQL will go through a rates list table and remove all duplicates, but keep the highest rates for duplicates.

  * First find out the maximum number of Occurrences you have.  That will tell you how many times you will need to run the delete SQL routine. <code sql>
SELECT countryprefixid,
COUNT(countryprefixid) AS NumOccurrences
FROM temp_rates
GROUP BY countryprefixid
HAVING ( COUNT(countryprefixid) > 1 )
</code>

  * Now you have made note of the number of Occurrences lets do a select to see the list and get a count of the total number of duplicates. <code sql>
SELECT *
   FROM temp_rates
   WHERE (countryprefixid) IN
   (SELECT countryprefixid
       FROM temp_rates
       GROUP BY countryprefixid
       HAVING COUNT(*) > 1)
   order by countryprefixid, rate
</code>

  * List duplicates with the highest rates and get a count.  That will be the records that should be remaining after we have deleted everything else. <code sql>
SELECT *
   FROM temp_rates
   WHERE (countryprefixid,Rate) IN
   (SELECT countryprefixid, Max(Rate)
       FROM temp_rates
       GROUP BY countryprefixid
       HAVING COUNT(*) > 1)
   order by countryprefixid, rate
</code>

  * Now delete all the lowest rates.  Run this query the same number of occurences that were found the first time. <code sql>
DELETE
   FROM temp_rates
   WHERE (countryprefixid,Rate) IN
   (SELECT countryprefixid, Min(Rate)
       FROM temp_rates
       GROUP BY countryprefixid
       HAVING COUNT(*) > 1)
</code>

  * When you are done if you Listed the lowest rates you should not find any duplicates. <code sql>
SELECT *
   FROM temp_rates
   WHERE (countryprefixid,Rate) IN
   (SELECT countryprefixid, Min(Rate)
       FROM temp_rates
       GROUP BY countryprefixid
       HAVING COUNT(*) > 1)
   order by countryprefixid, rate
</code>