a query type of questiton

[sde]

Database Schema is something like this:

table Issues:
  IssueID
  ContactID
  Comments

table IssueProduct:
  IssueID
  ProductID

table Products:
  ProductID
  ProductName

I need to use the IssueProduct table so each issue can support multiple products.

I would like to query ONLY the last 50 Issues.

The problem here is that if an issue has multiple products, it will show up as as a dupplicate issue id on a new row with the other product id.

I could write programs to find for a repeated IssueID, however I can not figure out how to make it return 50 seperate issues if a multiple product issue is found.

For Example. If one of the 50 issues has 2 products assigned to it, .. then my query would only result in 49 issues.

this is a much lighter version of the actual query, with only the necessary fields to ask this question.

SELECT Issues.ContactID,Issues.Comments,Products.ProductName 
  WHERE Issues.IssueID='1' 
    AND Issues.IssueID=IssueProduct.IssueID 
    AND IssueProduct.ProductID=Products.ProductID
  ORDER BY IssueID DESC LIMIT 50

[npa]

select distinct top 50
  x,
  y
from 
  table
order by
 someid desc

?

[sde]

top 50 works great =) i'm used to mysql. i'm using mssql for this project though, and i did not know that.

Distinct however doesn't work for this one.

i think i will have to query the product in while iterating through the results of what my first query generates.

[joe_bruin]

not sure if mysql can do this, but something along the lines of:

select *
from Issue, IssueProduct, Product
where IssueProduct.ProductID=Product.ProductID and
      IssueProduct.IssueID=Issues.IssueID and
      IssueID in 
      ( select IssueID
        from Issues
        order by IssueID desc
        limit 50 )

[Belisarius]

The only way to do it is with mulitple queries. I can't figure a way to combine them in SQL, so you'll have to execute one query to get the last 50 Issues, then execute queries on each issue, or create on really big OR statement.

[Belisarius]

I was under the impression mysql couldn't execute subqueries.

[sde]

i'm actually using c#/mssql for this project so that might work.

i actually wanted to make one of the fields return all the products seperated by commas, .. but i'm pretty sure that will require another query while iterating through the results.

thanks!