matching tables = true - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

matching tables = true

Author

Topic

design dog
Starting Member

2 Posts

Posted - 2010-05-23 : 02:22:55

I'm trying to simply compare the data from two different tables with the same matching data (i.e. -Inventory items with CategoryID = 14) and if there is a match in the two tables, the value is true, therefore print a gallery thumbnail.

elseif (isset($gallery))
{

if ($gallery=="cars")
{
// MySQL queries used to output lists of categories in each section.
$carslist = mysql_query("SELECT * FROM categories_db
WHERE cartype='blue' ORDER BY CategoryName");

echo "<div id=\"gallery_head\"><img src=\"images/galleries/gallerytitle-cars.gif\" border=\"0\" margin=\"0\" height=\"50\" width=\"700\"></div>";

makeGallery($carslist);
}

This code displays a gallery thumbnail
The problem is I want to ONLY display the ones that have at least 1 in inventory.
The inventory is in a table called items_db.

I'm just trying to MATCH to see if there is a category item (item) in another table, then if the value is true, spit out the thumbnail. Therefore, no thumbnails with zero match display or zero inventory.
I know there must be a few lines of code verses 20.
I'm not sure whether I need to use preg_match, Union, Count or JOIN.

if my dispay is $carslist = mysql_query("SELECT * FROM categories_db
WHERE cartype='blue' ORDER BY CategoryName");
but the table I need to match is
$inventory = mysql_query("SELECT * FROM items_db
WHERE CategoryID");
if CategoryName and CategoryID = say 14 then at least one match, one in inventory so therefore value is true and print gallery thumbnail.

I'm wondering what I can use with maybe a few lines.
COUNT would just COUNT the Matches.
I know there might be some way with pre_match.
I think there is some way with join.
or maybe a way with simple array.

just not sure of the best way to match two tables for this.

malpashaa
Constraint Violating Yak Guru

264 Posts

Posted - 2010-05-23 : 03:39:46

This is SQL Server forum, and you may get better help from MySQL forums. But you may try something like this (If it works in MySQL):


SELECT *
  FROM categories_db AS C
 WHERE cartype = 'blue'
   AND EXISTS(SELECT *
                FROM items_db AS I
               WHERE I.CategoryID = C.CategoryID)
 ORDER BY CategoryName