SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  1. #1
    SitePoint Addict djh's Avatar
    Join Date
    Apr 2000
    Location
    Long Beach, CA
    Posts
    333
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    SELECT TOP field1, field2 question

    Hi folks...

    I've got a stumper I can't seem to get right. I have two tables, and I want to generate the top 8 records from table 1 inner join the second table and retrieve three fields.

    Here's what I've got - but it's not doing what i want because it's taking the TOP 8 from both tables...

    I guess my question is how do you do a SELECT TOP statement but not include the fields from table2 so that they get included in the SELECT TOP, but are still selected as part of the recordset?

    Code:
    strSql = "SELECT TOP 8 tbl1.P_ID, tbl1.P_Name, tbl2.V_Product, tbl2.V_Name, tbl2.V_Price " & "FROM tbl1 INNER JOIN tbl2 ON tbl1.P_ID = tbl2.V_Product WHERE tbl1.P_Name <> '' AND tbl1.P_Live = 'y' ORDER BY P_ID DESC"
    Much obliged!

  2. #2
    ☆★☆★ silver trophy vgarcia's Avatar
    Join Date
    Jan 2002
    Location
    in transition
    Posts
    21,235
    Mentioned
    1 Post(s)
    Tagged
    1 Thread(s)
    An inner join will only select records have criteria in both tables that you want, so you'll only get 8 (since you're selecting the top 8). If you want the top 8 from one table and 3 from another table, you'll have to UNION two selects together.

  3. #3
    SitePoint Guru
    Join Date
    Jan 2004
    Location
    Uppsala, sverige
    Posts
    697
    Mentioned
    2 Post(s)
    Tagged
    1 Thread(s)
    Code:
    SELECT dt.P_ID, dt.P_Name, tbl2.V_Product, tbl2.V_Name, tbl2.V_Price 
    FROM (select top 8 * from tbl1 WHERE tbl1.P_Name <> '' AND tbl1.P_Live = 'y' ORDER BY P_ID DESC) dt
    INNER JOIN tbl2 ON dt.P_ID = tbl2.V_Product


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •