Jump to content

Welcome to Geeks to Go - Register now for FREE

Geeks To Go is a helpful hub, where thousands of volunteer geeks quickly serve friendly answers and support. Check out the forums and get free advice from the experts. Register now to gain access to all of our features, it's FREE and only takes one minute. Once registered and logged in, you will be able to create topics, post replies to existing threads, give reputation to your fellow members, get your own private messenger, post status updates, manage your profile and so much more.

Create Account How it Works
Photo

Multiple Table Joins in SQL


  • Please log in to reply

#1
freik

freik

    Member

  • Member
  • PipPip
  • 21 posts
Hi all,
I'm stuck with this join. I have these tables:

ccr_reports:
-------------
- ccr_rptID
- title
- source
- imgURL
- imgAlt
- description
- month
- year
- categoryID
- dateposted

ccr_category:
-------------
- ccr_catID
- ccr_catDesc

ccr_featured:
-------------
- ccr_featID
- ccr_rptID

ccr_pdf:
-------------
- ccr_pdfID
- ccr_rptID
- pdfURL

ccr_web:
-------------
- ccr_webID
- ccr_rptID
- webURL

This database tracks reports found online. Each report has the ability to be featured if needed. If the report is featured, it gets entered into the table. If an existing featured report is edited to be not featured anymore, the record in the featured table gets deleted. The website I'm creating will pull the most recent featured record and display all relevant info for that report.

The website will also sort and display the reports by category and year.

A report can have a pdf link, a web link, or both.

The problem I'm having is getting a result set that has all the info of the report, the pdf and web links IF they exist, and the featured id IF that exists.


I've tried:

SELECT rpt.title, rpt.source, rpt.imgURL, rpt.imgAlt, rpt.description, rpt.month, rpt.year, cat.ccr_catDesc, pdf.pdfURL, web.webURL
FROM ccr_reports as rpt, ccr_pdf as pdf, ccr_web as web, ccr_category as cat
WHERE rpt.ccr_rptID = pdf.ccr_rptID
AND web.ccr_rptID = rpt.ccr_rptID
AND rpt.categoryID = cat.ccr_catID
AND rpt.ccr_rptID = strPassedID


Where "strPassedID" is the record ID passed in from the site.

This only worked if there was a pdf and a web record listed in the tables. If a report is missing either a pdf or web link, the result set is nothing.

Is there a way to conditionally include record data when querying a database? or perhaps a way to display a null for that field but still return other relevant data?

Please help me. Any bit of info would be greatly appreciated.
  • 0

Advertisements


#2
freik

freik

    Member

  • Topic Starter
  • Member
  • PipPip
  • 21 posts
Also, I'm building in ASP Classic with MySQL. I'm curious to know if there are methods other than querying the database for each table.

Thanks.
  • 0

#3
freik

freik

    Member

  • Topic Starter
  • Member
  • PipPip
  • 21 posts
I just went with querying the tables I needed separately. A little messy but it works. Woohoo! Thank you for your time, everyone.
  • 0






Similar Topics

0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users

As Featured On:

Microsoft Yahoo BBC MSN PC Magazine Washington Post HP