« by mistake i joined one group, now i wnt to unjoined from that group, what all are the procedures, please?
How to calculate the difference of the same field in two rows in Microsoft Access? »

I am having an SQL INNER JOIN problem…?

I need some help!! I’m having trouble writing the script for an SQL query. I need to show the name and salary of all salespeople who do NOT have an order with a specific company. I think this is a RIGHT JOIN problem, but I need some assurance on that. I also need help with the script.

Someone please help!!

You want to see personal info
select personal_info.*, other_table_where_you_do_not_want_to_have_any_records.key_field
from personal_info
LEFT JOIN other_table_where_you_do_not_want_to_have_any_records
ON …
where …
other_table_where_you_do_not_want_to_have_any_records.key_field is null

this last where clause will return records that do NOT have joined records in second table…

or

SELECT *
FROM personal_info pi
WHERE …
NOT EXISTS
( SELECT *
FROM Holidays
WHERE key = pi.key
)

This entry was posted on Friday, February 12th, 2010 at 7:15 pm and is filed under inner join. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.

6 Responses to “I am having an SQL INNER JOIN problem…?”

  1. can you at least provide the table schemas ?
    References :

  2. We are going to need a little more information to help you out. How many tables are you working with, and the schema of them tables or at least what columns belong to what table.
    References :

  3. LEFT and RIGHT is arbitrary depending which one you put on right or left. I personally would put LEFT JOIN if the employee info is on the LEFT.
    References :
    http://www.w3schools.com/sql/sql_join_left.asp

  4. … WHERE <company ordered from> NOT IN(SELECT COMPANY FROM …)

    would be something close to what you need – the exact syntax depends on the dialect of SQL and the layout of your tables.
    References :

  5. You want to see personal info
    select personal_info.*, other_table_where_you_do_not_want_to_have_any_records.key_field
    from personal_info
    LEFT JOIN other_table_where_you_do_not_want_to_have_any_records
    ON …
    where …
    other_table_where_you_do_not_want_to_have_any_records.key_field is null

    this last where clause will return records that do NOT have joined records in second table…

    or

    SELECT *
    FROM personal_info pi
    WHERE …
    NOT EXISTS
    ( SELECT *
    FROM Holidays
    WHERE key = pi.key
    )
    References :

  6. TheMadProfessor on February 13th, 2010 at 2:22 am

    I’d agree a bit more info would help but from what you specify, it sounds more like a NOT IN situation. Assuming there is some sort of Orders table where salesID is a foreign key to Employee and customerID is a foreign key to Customer, it would look something like:

    SELECT firstName + ‘ ‘ + lastName AS Salesperson, salary AS Salary FROM Employee

    WHERE employeeID NOT IN
    (SELECT DISTINCT salesID FROM Order WHERE customerID = <whatever>)

    The NOT EXISTS approach suggested above would also work, but it’s been my experience that correlated subqueries like that tend to have far poorer performance, since the subquery must be run for each row instead of just once to create the NOT IN comparison values.
    References :

Leave a Reply