Results 1 to 4 of 4
  1. #1
    Join Date
    Sep 2014
    Posts
    3

    Unanswered: Query Problem: Connecting 2 tables through id and include null rows from one table

    Hello,

    I have two tables:
    contact
    company

    they are both connected through the company id (datatype int). I wrote a search query to lookup all contacts and their connected company. But there might be contacts which do not have a company but match the search result (i.e. I search by contact name). How can I include the contacts in my ResultSet that have companyid = null?
    con.isPrivate & con.createdBy are needed to not show private contacts of a different user.

    My working query which only outputs searchresults with a valid company id:
    Code:
    select con.contactid,con.firstname,con.lastname,con.city,com.name from contact con, company com where con.companyid = com.companyid AND (con.isPrivate = '0' or (con.isPrivate = '1' and con.createdBy = '"+v_user+"')) AND con.firstname like '%%' AND con.lastname like '%%' AND con.city like '%%' order by lastname" limit 0,10;
    My idea to include contacts without a company (does not work):
    Code:
    select con.contactid,con.firstname,con.lastname,con.city,com.name from contact con, company com where ((con.companyid = com.companyid) or (con.companyid is null)) AND (con.isPrivate = '0' or (con.isPrivate = '1' and con.createdBy = 'christian')) AND con.firstname like '%%' AND con.lastname like '%%' AND con.city like '%%' order by lastname limit 0,10;
    Thanks for your help

  2. #2
    Join Date
    Sep 2014
    Posts
    3
    ok as far as i read i need a left join, right? Now i am happy even contacts without a company are in my resultset but now the result said does not use my search strings (lastname like '%test%'). Any suggestions or am i still in the wrong direction?
    Code:
    select con.contactid,con.firstname,con.lastname,con.city,com.name FROM contact con LEFT JOIN company com ON (con.companyid = com.companyid) where con.isPrivate = '0' or (con.isPrivate = '1' and con.createdBy = 'christisn') AND con.firstname like '%max%' AND con.lastname like '%%' AND con.city like '%%' order by lastname limit 0,10;

  3. #3
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    like '%%' is never going to work
    the % is the SQL 'wildcard' matching symbol

    so you need something to match against
    where userid like 'veri%' //find users starting with veri
    where userid like '%tas' //find users ending with tas
    where userid like '%veri%' //find users which include veri somewhere (beginnign, end or inthe moiddle soemwhere

    i'd suggest get into some good habits early... format your SQL so its legible. eg:-
    Code:
    select con.contactid,
      con.firstname,
      con.lastname,
      con.city,
      com.name 
    FROM contact con
    LEFT JOIN company com
      ON (con.companyid = com.companyid)
    WHERE
      con.isPrivate = '0' 
      OR 
        (con.isPrivate = '1'
         and con.createdBy = 'christisn')
      AND con.firstname like '%max%'
      AND con.lastname like '%%'
      AND con.city like '%%'
    ORDER BY lastname limit 0,10;
    I'd rather be riding on the Tiger 800 or the Norton

  4. #4
    Join Date
    Sep 2014
    Posts
    3
    thanks for the advice, i excluded the variables i used in the wildcard search (java '%"+v_user+"%'). The solution is working

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •