Skip to main content
TheHillbillyGeek
Participant
Sep 16, 2010 3:07 AM UTC
Question

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

(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.)

This topic is closed to new replies. Start a new post to keep the conversation going.

6 replies

Participating Frequently
Sep 16, 2010 4:54 AM UTC

>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

Translate
TheHillbillyGeek
Participant
Sep 16, 2010 1:35 PM UTC

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
Translate
Participating Frequently
Sep 16, 2010 4:40 PM UTC

>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.

Translate