Need Help Joining FOUR Tables

[bnownlater]

I am having problems with my database and need someone to take a look at it for me. Here are my table setups:

PUB
---------
PUB_ID
PUB_NAME
PUB_DATE
ALT_PUB_DATE
POST_DATE
TITLE
ABSTRACT
PAGE URL


COMPANY
----------
ID
PUB_ID
CO_NAME


METATAG
-----------
ID
PUB_ID
TOPIC


STATE
-----------
ID
PUB_ID
THESTATE

Here is my SQL statement:

$whereand="";
If (isset($topic) && trim($topic)!="All Topics&quot{
$whereand=$whereand." AND TOPIC='".addslashes($topic)."'";
}
If (isset($company) && trim($company)!="All Companies&quot{
$whereand=$whereand." AND CO_NAME='".addslashes($company)."'";
}
If (isset($state) && trim($state)!="All States&quot{
$whereand=$whereand." AND THESTATE='".addslashes($state)."'";
}
If ($whereand!="&quot{
$whereand=substr($whereand, 4);
$where="WHERE $whereand GROUP BY PUB.PUB_ID";
}else{
$where="";
}

$SqlStr="SELECT PUB.*,METATAG.*,STATE.*,COMPANY.* FROM PUB
LEFT JOIN METATAG
ON PUB.PUB_ID = METATAG.PUB_ID
LEFT JOIN STATE
ON PUB.PUB_ID = STATE.PUB_ID
LEFT JOIN COMPANY
ON PUB.PUB_ID = COMPANY.PUB_ID ".$where;


echo $SqlStr;
$result = @mysql_query($SqlStr, $connection) or die("Couldn't execute select query"

If I don't use GROUP BY PUB.PUB_ID I get multiples of the same record? Not sure why. Also some records would not have a company or state associated with the record. So I guess I have to use a LEFT JOIN?? Another problem I am having is that I try to show all of the companies or states by using a SQl statement like:

//Select all company names from the COMPANY table to be displayed for a particular pub_id
$SqlStr="SELECT * FROM COMPANY WHERE PUB_ID=".$row['PUB_ID'];
//echo $SqlStr;
$result1 = mysql_query($SqlStr, $connection) or die("Couldn't execute select query"

The problem comes into to play when there is not a company (or state). The $row['PUB.PUB_ID'] has no value, but there is a record in the PUB table??


Really need help with the FOUR table SQL. Thanks.

 

[cluelessNewbie]

1) "If I don't use GROUP BY PUB.PUB_ID I get multiples of the same record? Not sure why."

Linking many to one relationships will duplicate the data common to the two tables.

You can "GROUP BY" (as you've done) or "SELECT DISTINCT"

2) Also you can replace the "PUB.*,METATAG.*,STATE.*,COMPANY.*" with a plain "*". You only need to table qualify the field list when columns duplicate.

3) When I write these kinda multi table queries I "grow them" a table at a time with a "limit 10" clause. I also do this from the command line rather than a program so I can immediately see what's happening.

4) Without the data it's hard to put #3 into practice but here it goes: (I'M USING LOCAL TABLES HERE SO IT JUST GUESSING FOR YOUR FORMAT.) Strange... my tables don't have missing foreign keys

a) As I read your message, you want an inner join on the PUB & METATAG tables.

SELECT * FROM PUB INNER JOIN METATAG ON PUB.pub_id = METATAG.pub_id limit 10;

b) Now Try & left join in your COMPANY table

SELECT * FROM PUB INNER JOIN METATAG ON PUB.pub_id = METATAG.pub_id LEFT JOIN COMPANY ON COMPANY.pub_id = PUB.pub_id limit 10;

c) Now try & link in the last table, STATE.

SELECT * FROM PUB INNER JOIN METATAG ON PUB.pub_id = METATAG.pub_id LEFT JOIN COMPANY ON COMPANY.pub_id = PUB.pub_id LEFT JOIN STATE ON STATE.pub_id = PUB.pub_id limit 10;

d) If you still have duplicates after this then try...

SELECT DISTINCT * FROM PUB INNER JOIN METATAG ON PUB.pub_id = METATAG.pub_id LEFT JOIN COMPANY ON COMPANY.pub_id = PUB.pub_id LEFT JOIN STATE ON STATE.pub_id = PUB.pub_id limit 10;

Bet others can get this query to run more efficiently... but I gave it a try. At any rate you can see the process.