Filtering results from one table with a value from a second table

Report · Sep 15, 2010

(cross-posted from PHP development)

Using Dreamweaver CS3 & PHP & MySQL.

Let's say I have a bunch of cars from different dealers in one table, with dealer-idbeing one of the values.

Another table holds the dealer's values, including whether they are active/inactive, withdealer-id being the primary key.

I want to search the car records, but only return the results from active dealers.

I can build an array of active dealers, and filter the results as they are produced from a standard search, but this is not optimum.

It gives me a number for total results that includes the results rom inactive dealers, and also screws with pagination of results.

Dreamweaver puts the recordset code in the header of the page, but....

One solution I see is to do a do { } while for all the active dealers that includes a query and a do { } while for results for just that dealer, right in the body code, but is there a more elegant one?

One problem I see is that results are not going to be able to be broken up into paginated groups...

I don't suppose there's a way to do this at the database level with a column that pulls a live value from another table during the query, is there?

Any MySQL gurus out there? Bueler...?

My copy of phpMyAdmin says: "The additional features for working with linked tables have been deactivated." this sounds hopeful, but I get bogged down in the documentation. Can someone give me a boost?

Thanks in advance.

edit:

(from what I can tell, linked tables allow you to pull values from another table to populate a pulldown: not what I want.)

Report · Sep 15, 2010

>I don't suppose there's a

>way to do this at the database

>level with a column that pulls a l

>ive value from another table during

>the query, is there?

Of course there is.

>Any MySQL gurus out there? Bueler

You don't need a SQL guru. Really, this is SQL 101. You simply need to join the two tables together:

Select * from car, dealer where

car.dealer_id = dealer.dealer_id and

dealer.active = 'TRUE'

A few notes:

1) You should not use dashes in your column or table names as this is not allowed in many implementations without escaping the character

2) You can use the SQL join syntax rather than joining in the where clause if you prefer

3) You won't get anywhere building dynamic sites without a basic understanding of SQL. Here's one good tutorial site:http://www.w3schools.com/SQl/default.asp

Report · Sep 16, 2010

Thank You! Yes, I'm working my way back to front in many areas, and there are gaping holes in my knowledgebase. Will google "SQL join syntax", and try to work through the tutorials.

The actual query is: (this specific search is for a year range of vehicles, and web_in is either a 'Y' or an 'N')

SELECT * FROM vehicles WHERE vehicles.dealer = dealers.dealer AND dealers.web_in = 'Y' 
AND cyr BETWEEN %s AND %s ORDER BY cyr DESC

It is giving an error of: 'Unknown column 'dealers.dealer' in 'where clause''.

It works without 'vehicles.dealer = dealers.dealer AND'

However: this works:

SELECT * FROM vehicles INNER JOIN dealers ON vehicles.dealer = dealers.dealer 
WHERE dealers.web_in = 'Y' AND cyr BETWEEN %s AND %s ORDER BY cyr DESC

Report · Sep 16, 2010

>It is giving an error of: 'Unknown column

>'dealers.dealer' in 'where clause''.

Correct. If you join the tables in the Where clause, then both tables also need to be in the From clause.

Report · Sep 16, 2010

I did get this working, and the JOIN method works for me; I was shaky on the syntax of the other method.

this works:

SELECT * FROM vehicles INNER JOIN dealers ON vehicles.dealer = dealers.dealer 
WHERE dealers.web_in = 'Y' AND cyr BETWEEN %s AND %s ORDER BY cyr DESC

and this:

(I just find it interesting that my filter value works on either side of the WHERE)

SELECT * FROM vehicles INNER JOIN dealers ON vehicles.dealer = dealers.dealer AND dealers.web_in = 'Y'
WHERE cyr BETWEEN %s AND %s ORDER BY cyr DESC

Thanks again for the help.

Report · Sep 16, 2010

>I just find it interesting that my filter

>value works on either side of the WHERE

Me too. It's certainly not a standard way of including filter criteria and would not be suprised if it is not supported in most other SQL implementations.

However, looking again at your query and realizing that it's not pulling any data from the dealer table, it may be better to use a subquery for your results. Something like:

SELECT * FROM vehicles 
WHERE cyr BETWEEN %s AND %s
AND
vehicles.dealer in
(select dealers.dealer from dealers 
where dealers.web_in = 'Y' )
ORDER BY cyr DESC

This may perform better than a join. Depending on the conditions, a correlated subquery could perform better or worse. If you're not dealing with a lot of data, it probably doesn't matter which approach you take.

Report · Sep 16, 2010

I integrated your suggestion, and it works right out of the box. Thanks!

It's not a large database, so if it's a matter of miliseconds, the difference is no doubt imperceptible.

(See, it's a matter of some simple syntax rules that I haven't learned yet...)