SELECT To obtain results from two tables where a value occurs once only in a table column

inner join
sql join
left outer join
sql join multiple tables with conditions
left join
inner join with where condition
sql select from multiple tables
how to retrieve data from two tables with one sql statement

Sorry I know this should be relatively simple and probably has been answered before, but I've got myself blocked on this and can't find a post here that matches.

I have two tables companies and contacts. A contact can be linked to 0 or 1 company, a company can have 1 or more contacts and MAY have one set as a 'primary contact'

companies(ccyID,ccyname,primconID)

________________________________________________
|  ccyID      |  ccyname      |  PrimconID    |
------------------------------------------------
|  aaaaaaa    |  Company A    |    NULL       |
|  bbbbbbb    |  Company B    |    NULL       |
|  ccccccc    |  Company C    |   vvvvvvv     |
________________________________________________

contacts(conID,firstname,lastname,ccyID)

__________________________________________________
|  conID    | first     | last      | companyID |
--------------------------------------------------
|  zzzzzzz  |  Stand    |   Alone   |   NULL    |
|  yyyyyyy  |  Only     |   Contact |  aaaaaaa  |
|  xxxxxxx  |  CompanyB |   First   |  bbbbbbb  |
|  wwwwwww  |  CompanyB |   Second  |  bbbbbbb  |
|  vvvvvvv  |  CompanyC |   Only    |  ccccccc  |
_________________________________________________

I need a SELECT that will return the companyID and contactID when the company has exactly one contact, AND does not have a PrimconID set i.e. for the above data I want returned

conID   ccyID
----------------
yyyyyyy aaaaaaa

(The eventual idea is that I'm then going to update the tables to make the solitary contact for companies the primary contact)

Group contacts by company to keep only those with a count of 1 and make sure they are in the set of companies without primary contact:

select companyid, max(conid)
from contacts
group by companyid
having count(*) = 1
and companyid in (select ccyid from companies where primconid is null)
order by companyid;

Retrieving Records from Multiple Tables, Get down and dirty with mySQL by learning the basics of SQL syntax In this case, rows are selected from the named table: you're selecting only rows with equal values in the specified columns: If multiple tables are named, any column name that appears in only one table is similarly unambiguous. SQL Query - Selecting records that repeat / occur more than once in a table -- newbe question. Oracle Database Forums on Bytes.

I would use a join and group by:

select c.ccyid, max(co.conid)
from companies c join
     contacts co
     on co.companyid = c.ccyid
where c.PrimconID is null
group by c.ccyid
having min(co.conID) = max(co.conID);

As an update statement, you can do:

update c
    set PrimconID = co.conid
    from companies c join
         (select co.companyid, max(co.conid) as conid
          from contacts co
          group by companyid
          having min(co.conid) = max(co.conid)
         ) co
         on co.companyid = c.ccyid
    where c.PrimconID is null ;

SQL Inner Join to Two or More Tables, database workhorse. Use it to combine columns from two or more tables via a join condition. You'll find that most of the joins you'll use are equijoins. A common Below is an example of simple select statement with an INNER JOIN clause. SELECT Just place them after the first reference of the table name. Here is a� have a table that has a column called article_title. Let's say the table name is articles. I need to find out the records where the article_title data is the same on more than one record. Sound like to me you also need to have the id because you want to find records based on article_title because you have duplicates

Use a CTE to first identify all contacts having only a single company, and then join to the company table:

WITH cte AS (
    SELECT ccyID, MAX(conID) AS conID
    FROM contacts
    GROUP BY ccyID
    HAVING COUNT(*) = 1
)

SELECT
    t.conID,
    c.ccyID
FROM companies c
INNER JOIN cte t
    ON c.ccyID = t.ccyID
WHERE
    c.PrimconID IS NULL;

See the demo below to see the query working.

Demo

SQL Query To Obtain Value that Occurs more than once, For SQL Server 2005+ ;WITH T AS ( SELECT *, COUNT(*) OVER (PARTITION BY Lastname) as Cnt FROM Students ) SELECT * /*TODO: Add� You can think of it as a WHERE clause that only applies to one of the tables. You can tell that this is only happening in one of the tables because the 1000memories permalink is still displayed in the column that pulls from the other table: Filtering in the WHERE clause. If you move the same filter to the WHERE clause, you will notice that the

Select rows with same id but different value in another column , SELECT * FROM Table WHERE ARIDNR IN ( SELECT ARIDNR FROM have a matching ARIDNR in another row in the table with a different LIEFNR. AS --the CTE portion to find the duplicates ( SELECT ARIDNR FROM @T FROM @T t -- shows results from main table INNER JOIN duplicates d on t. I have two tables, one on sheet1 and the other on sheet2. They are setup in identical position, and their headers are identical. What I am looking to do is: 1. find where in the first table a variable occurs (say variable a1). 2. Count the instances of each unique variable. (I've accomplished this with copy and paste from pivot tables)

Finding Duplicates with SQL, You could also use a variant of this SQL to find rows that occur exactly once: To Get Duplicate Values form table do this: add one more identity column in table. for example we "Group BY" only allows one row to be selected instead of the whole row. count how many records in two tables and return sum of both tables . Example 9: Select all the rows from tables T1 and T2 and order the rows such that the rows from table T1 are first and are ordered by column C1, followed by the rows from T2, which are ordered by column C2. The rows of T1 are retrieved by one subselect which is connected to the results of another subselect that retrieves the rows from T2.

The EXCEPT and INTERSECT Operators in SQL Server, The INTERSECT operator returns all rows that are in both result sets. Quite often, you're faced with the task of comparing two or more tables, or query results, left of the EXCEPT operator, only values that are not in Set B are returned. Once again, you can see that the join is more complex, though the� This is an easy way to count distinct values in the Pivot Table as you only need to add a helper column to the source data. Once you have added a helper column, you can then use this new data set to calculate the distinct count. While this is an easy workaround, there are some drawbacks to this method (covered later in this tutorial).

Comments
  • I like this answer - should have realised myself that the min & max values being the same if there's only one entry.
  • For the update (there's actually a bit more to that than I let on in my question). I used this SELECT to populate a cursor which I then looped through. Possibly not very efficient but this is a one-off exercise and coming from a traditional programmer background rather than starting in SQL I find the FOR loop logic of cursor processing easier to follow.
  • Wrong way round - all contacts have either 0 or 1 company. It's companies that have only 1 contact that I am trying to identify. But a CTE is a good solution.
  • @grl570810 My answer is working in this demo. Did you bother to check it?
  • Sorry I didn't pick up on your reply earlier I'm not a frequent Stack Overflow attendee. It's very interesting looking at the demo the query does indeed work when used on MySQL but I was using Microsoft SQL Server and it came up with syntax errors. Possibly in attempting to correct those I munged the logic.
  • @grl570810 The same code also runs in SQL Server.