SQL Query using a JOIN to select from two tables

Question

I have this SQL Query in PHP:

$sql="SELECT * from customer c JOIN commsone_phonelines b where b.phone_number LIKE '%".$_POST["search"]."%' AND ";
    $sql.="c.company like '%".$_POST["search"]."%' OR ";
    $sql.="c.forename like '%".$_POST["search"]."%' OR ";
    $sql.="c.surname like '%".$_POST["search"]."%' OR ";
    $sql.="CONCAT_WS(' ',c.forename, c.surname) LIKE '%".$_POST["search"]."%' OR ";
    $sql.="c.phone like '%".$_POST["search"]."%' OR ";
    $sql.="c.accountnumber like '%".$_POST["search"]."%' OR ";
    $sql.="c.customerid like '%".$_POST["search"]."%' OR ";
    $sql.="c.voip_account like '%".$_POST["search"]."%' OR ";
    $sql.="REPLACE(c.postcode,' ','') LIKE '%".$_POST["search"]."%' OR ";
    $sql.="REPLACE(c.postcode,'',' ') LIKE '%".$_POST["search"]."%' OR ";
    $sql.="c.postcode LIKE '%".str_replace(' ','',$_POST["search"])."%' ";
    $sql.=" AND c.resellerid = '' ORDER BY company ASC";

i am basically, trying to select from two different tables and i need to echo information only from the customer table.

If something is found in the commsone_phonelines table, it should link commsone_phonelines.customer_seq = customer.sequence

UPDATE:

I have just run this Query:

SELECT c.* from customer c INNER JOIN commsone_phonelines b ON c.sequence = b.client_seq where b.phone_number LIKE '%boat%' OR c.company like '%boat%'

it is returning rows from the customer table which is correct, however it shows the same row 5 times

What does "OR REPLACE(c.postcode,'',' ') LIKE" do? And how might it differ from the line below it? — Strawberry
– Strawberry, Commented Jul 25, 2014 at 9:10
If you like, consider following this simple two-step course of action: 1. If you've not already done so, provide proper DDLs (and/or an sqlfiddle) so that we can more easily replicate the problem. 2. If you've not already done so, provide a desired result set that corresponds with the information provided in step 1. — Strawberry
– Strawberry, Commented Jul 25, 2014 at 12:16

Strawberry · Accepted Answer · 2014-07-25 12:12:22Z

1

Personally, I find this easier to read...

 $sql="
 SELECT c.* 
   FROM customer c 
   JOIN commsone_phonelines b 
     ON b.customer_seq = c.sequence
  WHERE b.phone_number                         LIKE '%{$_POST['search']}%' 
    AND ( c.company                            LIKE '%{$_POST['search']}%' 
       OR c.forename                           LIKE '%{$_POST['search']}%' 
       OR c.surname                            LIKE '%{$_POST['search']}%' 
       OR CONCAT_WS(' ',c.forename, c.surname) LIKE '%{$_POST['search']}%' 
       OR c.phone                              LIKE '%{$_POST['search']}%' 
       OR c.accountnumber                      LIKE '%{$_POST['search']}%' 
       OR c.customerid                         LIKE '%{$_POST['search']}%' 
       OR c.voip_account                       LIKE '%{$_POST['search']}%' 
       OR REPLACE(c.postcode,' ','')           LIKE '%{$_POST['search']}%'
        )
    AND c.resellerid = '' 
  ORDER 
     BY company ASC";

edited Jul 25, 2014 at 12:12

answered Jul 25, 2014 at 9:16

Strawberry

34k14 gold badges43 silver badges58 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

user3815283 Over a year ago

doesnt help me tho "/

user3815283 Over a year ago

i can search for rows in commsone_phonelines and it returns the correct data from the customer table but i cannot search the customer table now

user3815283 Over a year ago

Ok, i just changed your JOIN to LEFT JOIN and it works fine! :)

Strawberry Over a year ago

A LEFT JOIN? But you're not selectng any columns from the RIGHT table !?!? (and "WHERE b.phone_number" renders it an INNER JOIN anyway) Still, whatever floats your boat.

Mudassir Hasan · Accepted Answer · 2014-07-25 08:52:51Z

0

....I need to echo information only from the customer table.

Then select columns of customer table only.

$sql="SELECT c.* from customer c JOIN commsone_phonelines b where b.phone_number LIKE '%".$_POST["search"]."%' AND ";

Rest of query remains same.

Edit

 $sql="SELECT DISTINCT c.col1,c.col2,c.col3 from customer c JOIN commsone_phonelines b where b.phone_number LIKE '%".$_POST["search"]."%' AND ";

edited Jul 25, 2014 at 8:52

answered Jul 25, 2014 at 8:37

Mudassir Hasan

29k21 gold badges104 silver badges135 bronze badges

11 Comments

user3815283 Over a year ago

if i search for a row in commsone_phonelines it shows the row from the customer table but multiple times. then if i search for a row in the customer table it shows nothing

user3815283 Over a year ago

do you have an example ?

Mudassir Hasan Over a year ago

see edit .. replace col1,col2,col3 nad so on with actual column names of customer table

user3815283 Over a year ago

thats working - how can i ensure if i search for a row in the commsone_phonelines it will match commsone_phonelines.customer_seq = customer.sequence ?

Mudassir Hasan Over a year ago

To get exact match use INNER JOIN

|

Collectives™ on Stack Overflow

SQL Query using a JOIN to select from two tables

2 Answers 2

4 Comments

11 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

11 Comments

Your Answer

Sign up or log in

Post as a guest

Related