swdev:howto:perform_sql

Performing SQL Queries

SQL JOINs

INNER JOIN: returns rows when there is a match in both tables.

SELECT *
FROM `t1`
INNER JOIN `t2` ON `t1`.`id` = `t2`.`id`;

LEFT JOIN: returns all rows from the left table, even if there are no matches in the right table.
```
SELECT *
FROM `t1`
LEFT OUTER JOIN `t2` ON `t1`.`id` = `t2`.`id`;
```
RIGHT JOIN: returns all rows from the right table, even if there are no matches in the left table.
```
SELECT *
FROM `t1`
RIGHT OUTER JOIN `t2` ON `t1`.`id` = `t2`.`id`;
```

FULL JOIN: returns rows when there is a match in one of the tables.

/* 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 */)

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:

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:

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

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.

SELECT countryprefixid,
COUNT(countryprefixid) AS NumOccurrences
FROM temp_rates
GROUP BY countryprefixid
HAVING ( COUNT(countryprefixid) > 1 )

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.

SELECT *
   FROM temp_rates
   WHERE (countryprefixid) IN
   (SELECT countryprefixid
       FROM temp_rates
       GROUP BY countryprefixid
       HAVING COUNT(*) > 1)
   ORDER BY countryprefixid, rate

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.

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

Now delete all the lowest rates. Run this query the same number of occurences that were found the first time.

DELETE
   FROM temp_rates
   WHERE (countryprefixid,Rate) IN
   (SELECT countryprefixid, MIN(Rate)
       FROM temp_rates
       GROUP BY countryprefixid
       HAVING COUNT(*) > 1)

When you are done if you Listed the lowest rates you should not find any duplicates.

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