/    Sign up×
Community /Pin to ProfileBookmark

Help w/ Join query in MySQL?

Copy linkTweet thisAlerts:
Jan 28.2005

I’m having trouble getting my brain around the (Inner?) Join used in a db query in MySql. Can someone help me with this? Here’s what I need to do:

presscategories
==========

presscategoryID*
presscategoryName

presspublish
========

presspublishID*
presspublishDate

press
====

pressID
pressPublish (*presspublishID goes here)
pressCategory (*presscategoryID goes here)
pressTitle
…

Here’s the first query:
SELECT * FROM presspublish ORDER BY presspublishDate DESC
…
$pressPublish = $row[‘presspublishID’];

Here’s the second query (within the first query):
SELECT * FROM press WHERE presspublish=$pressPublish

I need to modify the second query so that I can list the results by presscategoryName. BUT NOT ALL PRESSCATEGORIES WILL BE USED EVERYTIME. SO I NEED TO ONLY SHOW PRESSCATEGORIES WITH PRESS ASSOCIATED WITH THEM. For example:

01/28/05 (presspublishDate)
Automobile (presscategoryName #1)
* Article #1 (pressTitle #1)
* Article #2 (pressTitle #2)
Home (presscategoryName #3 — #2 has no related articles this date)
* Article #1 (pressTitle #3)
…

I hope this explains it well. Any help will be greatly appreciated. Thank you so much!

to post a comment
PHP

10 Comments(s) ↴

Copy linkTweet thisAlerts:
@ramiebJan 28.2005 — Maybe something like,

<i>
</i>SELECT *
FROM press WHERE presspublish=$pressPublish &amp;&amp; presscategoryName!=NULL ORDER BY presscategoryName
Copy linkTweet thisAlerts:
@msmith29063authorJan 28.2005 — Getting close... But I think I may need to rethink the queries/logic altogether. I need the results (from the press table) listed by presscategoryName (from the presscategories table):

01/28/05 (presspublishDate <-- presspublish table)

Automobile (presscategoryName #1 <-- presscategories table)

* Article #1 (pressTitle #1 <-- press table)

* Article #2 (pressTitle #2)

Home (presscategoryName #3 -- #2 has no related articles this date)

* Article #1 (pressTitle #3)

...

Any thoughts? Thank you so much!
Copy linkTweet thisAlerts:
@jajtiiiJan 28.2005 — I can't get my arms around your foreign keys (i.e. what are they)
Copy linkTweet thisAlerts:
@msmith29063authorJan 28.2005 — Would it help if I posted my PHP script?
Copy linkTweet thisAlerts:
@jajtiiiJan 30.2005 — I guess I don't understand what you want. If you want to get your results based on category name and date, you only need one query :

SELECT * FROM press, presspublish, presscategories WHERE presscategories.presscategoryID = press.pressCategory AND presspublish.presspublishID = press.pressPublish ORDER BY presspublish.presspublishDate, presscategories.presscategoryName

On the other hand, if you want to only find those results where the category name is a certain value (like 'News'), you would do this :

SELECT * FROM press, presspublish, presscategories WHERE presscategories.presscategoryID = press.pressCategory AND presspublish.presspublishID = press.pressPublish AND presscategories.presscategoryName = 'News' ORDER BY presspublish.presspublishDate

Then again, if you simply want all categories that are similar to 'News' (so you get 'Site News', 'Code News', 'Blog News'), you would need to do a 'LIKE' statement and probably a wildcard (%). I would need to review my notes on this one for MySQL to go further.
Copy linkTweet thisAlerts:
@jajtiiiJan 30.2005 — On further reflection (simply for efficiency), you should change the 'SELECT *' to only what you actually want to display. For example, if you really only want the 'title', 'news_item' and 'author', you would only need a 'SELECT press.title, press.news_item, press.author' instead of 'SELECT *'.

This will make your code more efficient.
Copy linkTweet thisAlerts:
@msmith29063authorJan 30.2005 — Here's my code. This may help. I want to show a list of presspublishDates with the "linked" presscategoryNames that have "linked" press entries. Right now, this code just shows the presspublishDate and a list of press entries for this date. I need the entries to be listed by presscategory. Note that all presscategoryNames don't get used every time, so I need to only show presscategoryNames that have press entries linked to them. Does this explan better? Thank you so much for any help!

[code=php]// Build query to fetch items from database
$query1 = "SELECT * FROM presspublish ORDER BY presspublishDate DESC";

// Execute query
$result1 = @mysql_query($query1);

// If query was okay...
if ($result1 && @mysql_num_rows($result1) > 0) {

// For each item returned from query...
while($row1 = mysql_fetch_array($result1)) {

print "<b>" . date("F j, Y", strtotime($row1['presspublishDate'])) . "</b>";

// Build query to fetch items from database
$pressPublish = $row1['presspublishID'];
$query2 = "SELECT * FROM press WHERE presspublish=$pressPublish";

// Execute query
$result2 = @mysql_query($query2);

// If query was okay AND we have at least 1 item...
if ($result2 && @mysql_num_rows($result2) > 0) {

print "<br>n";
print "<br>n";

// For each item returned from query...
while($row2 = mysql_fetch_array($result2)) {

print "<li><a href="press_detail.php?pressID=" . $row2['pressID'] . "">" . $row2['pressTitle'] . "</a><br>n";
}

} else {

// Tell no items were found
print "<br>n";
print "<br>n";
print "No articles were found<br>n";
print "<br>n";
}

print "<br>n";
}

} else {

// Tell no items were found
print "<i>No press clippings were found</i><br>n";
print "<br>n";
}[/code]
Copy linkTweet thisAlerts:
@msmith29063authorJan 31.2005 — I think we're getting close, but it's not giving me the results in the format I need. I'm sure it's ignorance on my end. Let me give a better idea of what the tables look like and the output I need. Thank you so much for everone's help! I'm learning.

presspublish
=======================


presspublishID* || presspublishDate
=======================


1 || 2005-01-30

2 || 2005-01-31

3 || 2005-02-01
=======================



presscategories
===========================


presscategoryID** || presscategoryName
===========================


1 || Auto

2 || Business

3 || Home
===========================



press
====================================


pressID || pressPublish* || pressCategory** || pressTitle
====================================


1 || 1 || 1 || Title #1

2 || 1 || 1 || Title #2

3 || 1 || 3 || Title #3

4 || 2 || 2 || Title #4
====================================



Output I need:

2005-01-30

Auto

1. Title #1

2. Title #2

Home

1. Title #3

2005-01-31

Business

1. Title #4

Your query may be doing what I need and I just don't have the PHP chops to process the results. Any ideas? Thank you again!
Copy linkTweet thisAlerts:
@Stephen_PhilbinJan 31.2005 — Have you tried using the Innodb engine of mysql instead of the MyISAM?
Copy linkTweet thisAlerts:
@msmith29063authorJan 31.2005 — I have no clue what you're talking about... Man, I fell dumb...
×

Success!

Help @msmith29063 spread the word by sharing this article on Twitter...

Tweet This
Sign in
Forgot password?
Sign in with TwitchSign in with GithubCreate Account
about: ({
version: 0.1.9 — BETA 5.18,
whats_new: community page,
up_next: more Davinci•003 tasks,
coming_soon: events calendar,
social: @webDeveloperHQ
});

legal: ({
terms: of use,
privacy: policy
});
changelog: (
version: 0.1.9,
notes: added community page

version: 0.1.8,
notes: added Davinci•003

version: 0.1.7,
notes: upvote answers to bounties

version: 0.1.6,
notes: article editor refresh
)...
recent_tips: (
tipper: @AriseFacilitySolutions09,
tipped: article
amount: 1000 SATS,

tipper: @Yussuf4331,
tipped: article
amount: 1000 SATS,

tipper: @darkwebsites540,
tipped: article
amount: 10 SATS,
)...