CODEWITHSUNDEEP
mysql
Simmy
Simmy

Reputation: 13

how to select unique rows from multiple tables with like columns using mysql

I have a program that downloads information from and outside source and provides me with a page of sql for each query I have entered.Normally there are 3 - 6 queries associated with each title. Each sql page loads a new table and all tables have the same columns. below are examples of the results for queries UsedCar and NewCar which is 2 of the 6 queries involved in the Title Automotive.

CREATE TABLE IF NOT EXISTS NewCar(Name char(255) DEFAULT NULL,
Phone char(255) DEFAULT NULL,
Address char(255) DEFAULT NULL,
Website char(255) DEFAULT NULL,
Email char(255) DEFAULT NULL,
Category char(255) DEFAULT NULL,
UNIQUE KEY Name (Name))
ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

LOCK TABLES NewCar WRITE;
REPLACE INTO NewCar VALUES('whats it Auto','1-888-555-7327','63 Beech Hill Rd, Beechville, NS B1G 2R9','http://www.whatsit.com','','New Car Dealers');
REPLACE INTO NewCar VALUES('Hugh\'s Chev','888-555-6990','376 Main St, lower bohemia, NS B5G 2D6','','','New Car Dealer');
REPLACE INTO NewCar VALUES('Be It Motors Ltd','866-555-2771','90 Main St, Upper Bohemia, NS B9G 2K8','','','New Car Dealers');
UNLOCK TABLES;


CREATE TABLE IF NOT EXISTS UsedCar(Name char(255) DEFAULT NULL,
Phone char(255) DEFAULT NULL,
Address char(255) DEFAULT NULL,
Website char(255) DEFAULT NULL,
Email char(255) DEFAULT NULL,
Category char(255) DEFAULT NULL,
UNIQUE KEY Name (Name))
ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

LOCK TABLES UsedCar WRITE;
REPLACE INTO UsedCar VALUES('whats it Auto','1-888-555-7327','63 Beech Hill Rd, Beechville, NS B1G 2R9','http://www.whatsit.com','','Used Car Dealers');
REPLACE INTO UsedCar VALUES('Hugh\'s Chev','888-555-6990','376 Main St, lower bohemia, NS B5G 2D6','','','Used Car Dealer');
REPLACE INTO UsedCar VALUES('Be It Motors Ltd','866-555-2771','90 Main St, Upper Bohemia, NS B9G 2K8','','','Used Car Dealers');
UNLOCK TABLES;

These are then loaded into my Database.

My problem is in trying to draw off this information into my webpage. I would like to create a link that shows unique information under Automotive excluding the Category. I can do this from one table but have not been able to do this for multiple table. I have tried researching the problem and have tried selecting DISTINCT and UNIQUE. As well as doing table JOIN and UNION - I am sure it is probably something very stupid I am forgetting.

Below is the PHP I use to draw from a single table that has been modified to try to draw from 2 tables and returns "no records found".

<?php
$result = mysql_query("SELECT DISTINCT Name FROM (SELECT * FROM UsedCar UNION    SELECT*FROM NewCar) ORDER BY name ASC");
if(mysql_num_rows($result) == 0){
echo("no records found");
} ELSE {
echo "<table border='0'; table id='address'>
<tr>
<th>Name</th>
<th>Phone</th>
<th>Address</th>
<th>Website</th>
</tr>";

while($row = mysql_fetch_array($result))

  {
  echo "<tr>";
  echo "<td>" . $row['Name'] . "</td>";
  echo "<td>" . $row['Phone'] . "</td>";
  echo "<td>" . $row['Address'] . "</td>";
  echo '<td><a href="' . $row['Website'] . '" target="_self\">' . $row["Website"] . '</a></td>';
  echo "</tr>";

  }
echo "</table>";
}

?>

Sorry for long post..:)

Upvotes: 1

Views: 3072

Answers (1)

Taryn
Taryn

Reputation: 247850

Part of the problem is your SQL syntax, you are missing the alias on the sub-select

You query with no alias (see SQL Fiddle):

SELECT DISTINCT Name 
FROM 
(
  SELECT * 
  FROM UsedCar 
  UNION    
  SELECT *
  FROM NewCar
) 
ORDER BY name ASC

With alias works (see SQL Fiddle)

SELECT DISTINCT Name 
FROM 
(
  SELECT * 
  FROM UsedCar 
  UNION    
  SELECT *
  FROM NewCar
) x
ORDER BY name ASC

But if you only want the name then you don't need the subquery (see SQL Fiddle):

SELECT name 
FROM UsedCar 
UNION    
SELECT name
FROM NewCar

Upvotes: 2

Related Questions