CODEWITHSUNDEEP
sql
anhtv13
anhtv13

Reputation: 1808

Search SQL statement

I have a table name chat_customer which has customer attribute: 

id, name, status, ...

I also have a table name chat_friendRelationship and myId and my friendId can be either in column1 or column2. How to search all my friendId?

My query is

SELECT 
    CHAT_CUSTOMER.CUSTOMERID, CHAT_CUSTOMER.CUSTOMERNAME,
    CHAT_CUSTOMER.STATUS, CHAT_CUSTOMER.LASTLOGIN, CHAT_CUSTOMER.ISADMIN
FROM 
    CHAT_CUSTOMER 
LEFT JOIN 
    CHAT_FRIENDRELATIONSHIP ON CHAT_CUSTOMER.CUSTOMERID = CHAT_FRIENDRELATIONSHIP.CUSTOMERID1
                            OR CHAT_CUSTOMER.CUSTOMERID = CHAT_FRIENDRELATIONSHIP.CUSTOMERID2
WHERE 
    CHAT_CUSTOMER.CUSTOMERID = '1';

But it returns some rows with my data. I want it to return my friend data.

Those images below are my tables.

chat_customer Table:

chat_customer Table

chat_relationship Table:

chat_relationship Table

Upvotes: 0

Views: 59

Answers (3)

user5166903
user5166903

Reputation:

Another way to write your query, longer for sure, but maybe a little more understandable for some of us and it will display all the data of the friends of costumer_id = 1:

    SELECT   
      CHAT_CUSTOMER.CUSTOMERID  
      CHAT_CUSTOMER.CUSTOMERNAME,  
      CHAT_CUSTOMER.STATUS,  
      CHAT_CUSTOMER.LASTLOGIN,  
      CHAT_CUSTOMER.ISADMIN  
    FROM   
      CHAT_CUSTOMER   
        JOIN CHAT_FRIENDRELATIONSHIP ON 
           CHAT_CUSTOMER.CUSTOMERID = CHAT_FRIENDRELATIONSHIP.CUSTOMERID1
    WHERE
      CHAT_FRIENDRELATIONSHIP.CUSTOMERID2 = 1
    UNION
    SELECT  
      CHAT_CUSTOMER.CUSTOMERID  
      CHAT_CUSTOMER.CUSTOMERNAME,  
      CHAT_CUSTOMER.STATUS,  
      CHAT_CUSTOMER.LASTLOGIN,  
      CHAT_CUSTOMER.ISADMIN  
    FROM
      CHAT_CUSTOMER   
        JOIN CHAT_FRIENDRELATIONSHIP ON 
          CHAT_CUSTOMER.CUSTOMERID = CHAT_FRIENDRELATIONSHIP.CUSTOMERID2
    WHERE 
      CHAT_FRIENDRELATIONSHIP.CUSTOMERID1 = 1
  ;

Upvotes: 2

Paul Maxwell
Paul Maxwell

Reputation: 35583

For full details of frinds:

select
      c.*
from chat_customer c
inner join (
      select distinct customerid2
      from chat_relationship
      where customerid1 = '1'
      ) friends on c.customerid = friends.customerid2

nb: It's not clear from the example if DISTINCT is required or not

For just a list of friends by an id:

      select distinct customerid2
      from chat_relationship
      where customerid1 = '1'

Upvotes: 2

FirebladeDan
FirebladeDan

Reputation: 1069

Here's an example of getting both searches:

select 
c.customerid, 
c.customername, 
c.status,
cr.columnname1,
cr.columnname2,
cr2.columnname1,
cr2.columnname2
from 
chat_customer c
join chat_relationship cr
on c.customerid = cr.customerid1
join chat_relationship cr2
on c.customerid = cr2.customerid2
where c.customer_id = '1';

Upvotes: 1

Related Questions