Help with query

diesel_ · #1 27-Oct-2008, 08:49

Hi All,

I am asking users to enter a search term this will search the database and list the matches. here is a sample of the table structure

Code:

ASSET TABLE       CATEGORY TABLE
Asset_id              Category_id
name                  name
Category_id

now my problem is i want users to be able to search the ASSET TABLE records for a category `name` that is found in the CATEGORY TABLE but my query produces a cartesian product i want to find a way of checking if ASSET TABLE is using a category `name` via the PK->FK link

here is my failed query

PHP Code:


$query = "SELECT * FROM asset, category, manufacturer, model, location, contact, deployment, condition
                                                            WHERE asset.category_id = category.category_id 
                                                            AND asset.manufacturer_id = manufacturer.manufacturer_id
                                                            AND asset.condition_id = condition.condition_id
                                                            AND asset.location_id = location.location_id
                                                            AND asset.contact_id = contact.contact_id
                                                            AND asset.deployment_id = deployment.deployment_id
                                                            AND asset.asset_id LIKE \"%$trimmed%\"
                                                            OR asset.asset_name LIKE \"%$trimmed%\"
                                                            OR category.category LIKE \"%$trimmed%\"
                                                            OR manufacturer LIKE \"%$trimmed%\"
                                                            OR model_name LIKE \"%$trimmed%\"
                                                            OR condition LIKE \"%$trimmed%\"
                                                            OR location LIKE \"%$trimmed%\"
                                                            OR contact.name LIKE \"%$trimmed%\"
                                                            OR description LIKE \"%$trimmed%\"
                                                            OR comment LIKE \"%$trimmed%\"
                                                            OR deployment.deployment LIKE \"%$trimmed%\"
    order by asset_id";

ignore the \"%$trimmed%\" this is just the result of the search term entered by users

Thanks

diesel_ · #2 28-Oct-2008, 11:35

Hi

TABLE1
Asset_id
Asset_name
Type_id

TABLE2
Type_id
Type_name

I need a query to use the two tables but exclude the type_id details, for example

SELECT table1.asset_id, table1.asset_name, table2.type_name
FROM table1, table2
Where table1.type_id = table2.type_id

It gets tricky becuase i use a search field to form a dynamic query. The search field allows a user to enter a type_name but type_name is not in table1 so it searches table2. The problem i get is that if it finds a match in table2 it prints the results but i only want to print the result if the type_id is used in table1 (i.eTABLE1 asset_id =1, asset_name= packaging, type_id = 2) (TABLE2 type_id=2, type_name = MEDIUM).

This database is an inventory database so table2 consists of box types (i.e big, small, medium etc) so when i run a query for all medium boxes in stock i want it to print only the medium boxes in table1. But ignores the condition i awant in place.

Thanks

TurboPT · #3 28-Oct-2008, 12:47

This still seems relative to the first post?

The query you have will work, because the join is in place by the type_id's, but another condition is needed in the WHERE clause:

Code:

SELECT table1.asset_id, table1.asset_name, table2.type_name
FROM table1, table2
WHERE table1.type_id = table2.type_id
AND   table2.type_name = 'MEDIUM'

That's what I perceive about what you're asking, but it's not 100% clear.
Is this what you need?

diesel_ · #4 29-Oct-2008, 08:35

The table2.type_name is unknown. This value is dynamic (user selects the search criteria)

table2_type_name LIKE \"%$trimmed%\"

with trimmed being the entered data. I just cant get the query to disregard the type_names unused in table1

The best example i can offer is if i ask people to visit the actual site

<link removed>

I wanted to create a query to replace the type_id with the type_name for table1 then run a query against the initial query.

diesel_ · #5 29-Oct-2008, 12:05

Hi
This is working fine now
can the mods remove the links to the site

TurboPT · #6 29-Oct-2008, 12:09

So what was the issue, or what did you decide to do?

diesel_ · #7 29-Oct-2008, 12:16

because TABLE1 needed the type_name from TABLE2 i did an INNER JOIN

Select *
From Table1
INNER JOIN Table2 ON Table1.type_id = Table2.type_id

I didnt do a select from Table2 therefore not producing the results in that table

Thanks for responding it kick started my thought process again

Thread Tools	Search this Thread
Show Printable Version Email this Page	Search this Thread: Advanced Search
Rate This Thread
Rate This Thread:

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Query objects	giugio	C++ Forum	5	03-Oct-2008 04:28
apache 2.2.1 doesn't support nested query	ctrohaya	Apache Web Server Forum	0	15-Nov-2006 20:46
Net-ok.net - Traceroute, Ping, Domain Name Server (DNS) Lookup, WHOIS, and DNS Records Lookup Network Query Tool	netoknet	Web Hosting Forum	1	19-Jan-2006 02:37
Simple SQL Query - ?	firebird	MySQL / PHP Forum	2	16-Feb-2005 09:00
Search Engine Positioning 101 and 201 "How To" Tips...	000	Search Engine Optimization Forum	0	29-May-2003 11:34

Help with query

Re: Help with query

Re: Help with query

Re: Help with query

Re: Help with query

Re: Help with query

Re: Help with query