[freik]

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.

[Advertisements]

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.

[freik]

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.

[freik]

I just went with querying the tables I needed separately. A little messy but it works. Woohoo! Thank you for your time, everyone.