Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Select id from one table and its value from another table to search

I have two tables named company and customers.

In company table there are 3 fields ID, Company, and Type as:

ID    Company    Type
1     ABC        Running

2     XYZ        Current

Now again in customers table I submitted the value of company and customers, but here the value of company is submitted as an ID as:

Company     Customer Name
1              monty

2             sandeep

now I want to search the company name in customer table but when i put company name in search box its showing nothing because the value of company name is in ID form in customer tabe.how can i achieve it.

Here is my query for search:

$sql = "Select * from customers where name like '%$term%' or location like '%$term%' or company like '%$term%'";
like image 642
Montiyago Avatar asked Sep 02 '13 09:09

Montiyago


People also ask

How do I pull data from one table to another table?

To copy column definitions from one table to another. Open the table with columns you want to copy and the one you want to copy into by right-clicking the tables, and then clicking Design. Click the tab for the table with the columns you want to copy and select those columns. From the Edit menu, click Copy.

How can I match data from two tables in SQL?

(INNER) JOIN : Returns records that have matching values in both tables. LEFT (OUTER) JOIN : Returns all records from the left table, and the matched records from the right table. RIGHT (OUTER) JOIN : Returns all records from the right table, and the matched records from the left table.


Video Answer


2 Answers

By JOINing the two tables:

Select * 
from customers AS cust
INNER JOIN companies AS comp ON cust.Company = comp.Id
where comp.location like '%$term%' 
   or comp.company like '%$term%'
like image 156
Mahmoud Gamal Avatar answered Oct 19 '22 19:10

Mahmoud Gamal


try this

SELECT co.*, cu.* FROM company as co, customers as cu where co.company_id = cu.company_id and co.company_name = '%$term%';
like image 32
Ankur Dhanuka Avatar answered Oct 19 '22 18:10

Ankur Dhanuka