We help IT Professionals succeed at work.

MySQL Select from several tables

554 Views
Last Modified: 2013-12-12
I need to create a single select statement that gets values from one table based on data in another table.

The first table (prodcategories) has this structure (columns)
category
product
clientcategory (integer)

The existing SQL statement I deed to modify is this:

"select * from clienttype where companyid='$companyid'";

I need to modify this so that it is like the following "pseudo" SQL statement:

SELECT * from clienttype where companyid = '$companyid' and clientcategoryid = (SELECT clientcategory from prodcategories where category = "xxx").

Can you show me the proper MySQL syntax?
Comment
Watch Question

CERTIFIED EXPERT
Top Expert 2004
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
CERTIFIED EXPERT
Top Expert 2011

Commented:
which version of mysql are you using the syntax varies considerably between versions...

SELECT a.*
FROM clienttype as a, prodcategories as b
Where  a.clientcategory=b.clientcategory
and a.companyid='$companyid'
 AND b.category='xxx'


is probably a more generic answer
Richard KortsBusiness Owner / Chief Developer

Author

Commented:
To routinet and Lowfatspread,

I tried both your versions. Neither produced a result set with any rows.
I wrote a test script, as follows. Qry1 returns 13 rows. Qry2 returns 7.

Note: I alternately commented out your suggested SQL & ran it both ways.

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">

<html>
<head>
      <title>SQL Test</title>
</head>

<body>
<?
$host = "db979.perfora.net";
$un = "dbo207429934";
$pw = ".w5F7vaS";
$dbname = "db207429934";
$Link = mysql_connect ($host, $un, $pw);

$companyid = 6;

// Test 1
$Qry1 = "SELECT * from clienttype where companyid = $companyid";
$res1 = mysql_db_query ($dbname, $Qry1, $Link);
$n1 = mysql_num_rows ($res1);

print("Rows returned from Qry1 = $n1<br>\n");

$Qry2 = "SELECT * from prodcategories where category ='CLnet'";
$res2 = mysql_db_query ($dbname, $Qry2, $Link);
$n2 = mysql_num_rows ($res2);

print("Rows returned from Qry2 = $n2<br>\n");

$Qry = "SELECT a.* FROM clienttype a INNER JOIN prodcategories b ON a.clientcategory=b.clientcategory WHERE a.companyid=$companyid AND b.category='CLnet'";
// $Qry = "SELECT a.* FROM clienttype as a, prodcategories as b Where a.clientcategory=b.clientcategory and a.companyid='$companyid' AND b.category='CLnet'";
$result = mysql_db_query ($dbname, $Qry, $Link);

$nr = mysql_num_rows ($result);

if ($nr == 0) {
      print("None returned\n");
} else {
      print("These returned:<br>\n");      
      for ($i = 0; $i < $nr; $i++) {
            $row = mysql_fetch_row ($result);
            print("companyid = $row[1], clientcategoryid = $row[2]<br>\n");
      }
}            
?>


</body>
</html>

What's wrong?

CERTIFIED EXPERT
Top Expert 2004

Commented:
It may be that you have no records to return.  The individual table queries return records, yes, but that does not mean they will have any matches in the other set.  Can you post the return from the two single-table queries?
Richard KortsBusiness Owner / Chief Developer

Author

Commented:
To routinet:

You're right. I had a syntax error.

It returns the following (from your SQL syntax).  I need unique results (no duplicate rows). Can I just add unique somewhere in the SQL? I'm not sure where in this complex statement.

These returned:
companyid = 6, clientcategoryid = 2
companyid = 6, clientcategoryid = 2
companyid = 6, clientcategoryid = 7
companyid = 6, clientcategoryid = 6
companyid = 6, clientcategoryid = 6
companyid = 6, clientcategoryid = 8
companyid = 6, clientcategoryid = 9
CERTIFIED EXPERT
Top Expert 2004

Commented:
You can use the DISTINCT keyword to limit duplicates.  

SELECT DISTINCT a.* FROM ...
Richard KortsBusiness Owner / Chief Developer

Author

Commented:
To routinet:
Yes, I figured that out.

I gave you the points for the original question. You got them, right?

It works perfectly now.

Thanks!
CERTIFIED EXPERT
Top Expert 2004

Commented:
Yes, I saw the question had been closed, but I wanted to make sure you got the answer to that last question.  I'm happy to hear it is working.  Good luck!

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.