mysql query to serve same purpose

[daniel0816]

Can anyone provide me with an alternative query that does the same job as the following solution. Using UNION causes the result to not be suitable for what I want to do with it.

Any help would be greatly appreciated thanks.

$sql="SELECT CUST_ID, CUST_Forename, CUST_Surname, CUST_Email, CUST_Mobile, 
CUST_HomeNum, CUST_AddressL1, CUST_AddressL2, CUST_AddressL3, 
CUST_Postcode, '' as J_RefNum, '' as MANU_Name, '' as J_Model, '' as 
OS_Name, '' as J_ReceivedBy, '' as J_DateRec, '' as J_FaultDesc, '' as 
J_PassWinAdmin, '' as J_DataRecYN, '' as J_PowerSuppYN, '' as 
JS_Status FROM Customers  WHERE (CUST_ID LIKE 
'%$criteria%') OR (CUST_Forename LIKE '%$criteria%') OR (CUST_Surname 
LIKE '%$criteria%') OR (CUST_Email LIKE '%$criteria%') OR (CUST_Mobile 
LIKE '%$criteria%') OR (CUST_HomeNum LIKE '%$criteria%') OR 
(CUST_AddressL1 LIKE '%$criteria%') OR (CUST_AddressL2 LIKE '%$criteria%') 
OR (CUST_AddressL3 LIKE '%$criteria%') OR (CUST_Postcode LIKE 
'%$criteria%')  UNION SELECT '', '', '', '', '', 
'', '', '', '', '', J_RefNum, '', J_Model, '', J_ReceivedBy, J_DateRec, 
J_FaultDesc, J_PassWinAdmin, J_DataRecYN, 
J_PowerSuppYN,'' FROM Jobs  WHERE (J_RefNum LIKE 
'%$criteria%') OR (J_Model LIKE '%$criteria%') OR (J_ReceivedBy LIKE 
'%$criteria%') OR (J_DateRec LIKE '%$criteria%') OR (J_FaultDesc LIKE 
'%$criteria%') OR (J_PassWinAdmin LIKE '%$criteria%') OR (J_DataRecYN 
LIKE '%$criteria%') OR (J_PowerSuppYN LIKE 
'%$criteria%')  UNION SELECT '', '', '', '', '', 
'', '', '', '', '', '', MANU_Name, '', '', '', '', '', '', '', 
'', '' FROM Manufacturers  WHERE (MANU_Name LIKE 
'%$criteria%')  UNION SELECT '', '', '', '', '', 
'', '', '', '', '', '', '', '', OS_Name, '', '', '', '', '', 
'', '' FROM OperatingSystems  WHERE (OS_Name LIKE 
'%$criteria%')  UNION SELECT '', '', '', '', '', 
'', '', '', '', '', '', '', '', '', '', '', '', '', '', '', 
JS_Status FROM JobStatus  WHERE (JS_Status LIKE 
'%$criteria%')";

[mac_gyver]

the solution will likely involve JOIN'ing the related tables. if the tables aren't related in any way, they would need a separate (unrelated) query.

 

rather than posting a query that doesn't do what you want (that doesn't show the relationship between the tables or what the desired result is), if you post some sample data and the result you want for that data, along with what relationship there exists between the tables, someone can help.

[daniel0816]

Say I have the following queries:

"SELECT CUST_ID, CUST_Forename, CUST_Surname

FROM Customers

WHERE  (CUST_ID LIKE '%$criteria%') OR (CUST_Forename LIKE '%$criteria%') OR
 (CUST_Surname LIKE '%$criteria%')";

 

"SELECT MANU_Name FROM Manufacturers 

WHERE (MANU_Name LIKE '%$criteria%')";

 

What I want to do is have these two queries in one query. 

I also want the query to echo the results into an HTML table like the following:

 

<table name='details'  border='2'>
<thead>
 <tr>
  <th>Customer ID</th>
  <th>Forename</th>
  <th>Surname</th>

  <th>Manufacturer</th>
</tr>

</thead>

<tbody>

 

echo "<tr>
  <td>{$row['CUST_ID']}</td>
  <td>{$row['CUST_Forename']}</td>
  <td>{$row['CUST_Surname']}</td>
  <td>{$row['MANU_Name']}</td>
  </tr>\n";

 

Thanks Again

[mac_gyver]

your example contains no relationship between the tables, therefore, they don't belong in the same query.

 

if your Customers table had, me, angus macgyver in it, your Manufacturers table had Mack Trucks in it, and your search $criteria was 'mac', you are stating the result should be - myid, angus, macgyver, Mack Trucks. that makes no sense.

[daniel0816]

Ok bad example for talk sake assume that the customer table has a MANU_ID.

[mac_gyver]

your customer table should only have information about the customers.

 

if what you are trying to do concerns what a customer ordered (which you haven't stated), you would have an order table that relates the customer_id with each item_id in each order that customer placed. you would also have an item/product table that relates each item to the manufacturer, in a manufacturer table, using the manufacture id.

 

since these tables would now have related information stored in them, your query would JOIN them using the appropriate id fields.

[vinny42]

 

 for talk sake assume that the customer table has a MANU_ID.

 

I cannnot , because that is not what you have in your tables.

 

You seem to want to get all data about a customer, it's manufacturers, operatingsystems etc at once. This smells like a common misunderstanding  about how databases work.

Each customer can have any number of manufacturers, operatingsystems etc. UNION, as you have notices, simply doesn't work for this. A JOIN on the other hand will return every combination of all record that match the ON clause, so if a customer has 4 manufacturers and 3 operatingsystems, you would get the customerdata 12 times. Not every usefull.

 

I think the best thing you can do is simply use separate queries. if the number of queries is an issue then you should select multiple customers and manufacturers etc per query and merge them together in you application.