Order by a subquery

[syoka]

I have been working with SQL queries for a while now but only recently has there been a need to introduce sub queries.



The following code works fine but I now need to be able to order the data by “table4.price” I have tried adding “ORDER BY table4.price” to the very end of the string and get an error informing me of an object required, so I tried adding it to the table4 sub query but there is not order to the results.


SELECT table1.*
FROM table1
WHERE table1.ID IN(SELECT ID FROM table2 WHERE allow=true)
AND table1.ID IN(SELECT ID FROM table3 WHERE catalogue=1)
AND table1.ID IN(SELECT ID FROM table4 WHERE price>0 AND price<500)
AND table1.ID NOT IN(SELECT ID FROM table5 WHERE date='01/11/2010')


<!-- ORDER BY table4.price -->



I built the string in this was so that the program can chop and change the string depending on how many search parameters are entered is there a way I can get this to work?

 

[Galaxiom]

I suspect your structure is unnecessarily seperating the data into different tables. The problem would be trivial if combined into one table.

If this cannot be done then the query would be better built with joins between the tables.

SELECT table1.* 
FROM table1 
  INNER JOIN table2 ON table1.ID = table2.ID
  INNER JOIN table3 ON table2.ID = table3.ID
  INNER JOIN table4 ON table3.ID = table4.ID
  INNER JOIN table5 ON table5.ID = table4.ID
WHERE table2.allow 
  AND table3.catalog = 1
  AND table4.price BETWEEN 0 AND 500
  AND table5.[date] <> '01/11/2010'
ORDER BY table4.price

That date format assumes you are querying SQL server rather than Access and the intended date is January 11.

A boolean field does not need an operator:
WHERE fieldname
is the same as:
WHERE fieldname = true

Date should not be used as a fieldname as it is a reserved word. Otherwise it must always be surrounded by square brackets.

 

[syoka]

Cheers Galaxiom,

that's had the added benefit of cleaning up my code. Unfortunately its been giving me an error I cannot find the solution for:
Microsoft JET Database Engine error '80040e10'

No value given for one or more required parameters.

/testerproductsByCategory.asp, line 144

Line 144 is where the Command is executed.

below is the code I'm using (I've including the actual table & field names this time, I though it would be easier to understand if i replaced them, but perhaps not).

SELECT artists.* FROM artists
INNER JOIN users ON artists.ID = users.ID
INNER JOIN prices ON catart.ID = prices.ID
INNER JOIN calendar ON calendar.ArtistID = prices.ID
WHERE users.allow
AND prices.berkshire BETWEEN 0 AND 1000
AND calendar.cal_date <> '06/02/2011
ORDER BY artist.contact

All the values are in and it brought back what I needed with the previous Command (except for the order).

Oh and thanks for the 'between' and sorry for late reply.

 

[Galaxiom]

Probably just a typo here but there is no closing quote mark on the cal_date comparison value. However that might be interpreted by JET as a parameter name.

Otherwise maybe it needs some bracketing in the Where clause to make sure the And in the between statement is not confused with the other ands.

Also note that this query assumes that the ID field is present in every table because with the inner join no record will be returned if even one of them doesn't have the matching value. I haven't checked again but I think your original query might return records under those circumstances.

 

[syoka]

yes that was a typo, i also realised the ORDER BY had artist instead of artists. so fixed those.
But the same error appears, all the tables have the ID field but not every table has every ID in it. users keeps all the ID's but they are only entered into the other table if the user chooses to fill in that information.

I changed the between to a less than (<), but no go.

I tried creating a string with less requirements, which threw up the same error so maybe its something in here?

SELECT artists.* FROM artists
INNER JOIN users ON artists.ID = users.ID
WHERE users.allow
ORDER BY artists.contact