Results 1 to 3 of 3
Jul 13, 2004, 01:12 #1
- Join Date
- Apr 2000
- Long Beach, CA
- 0 Post(s)
- 0 Thread(s)
SELECT TOP field1, field2 question
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?
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"
Jul 13, 2004, 06:19 #2
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.
Jul 13, 2004, 09:16 #3
- Join Date
- Jan 2004
- Uppsala, sverige
- 2 Post(s)
- 1 Thread(s)
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