I am having an SQL INNER JOIN problem...?

Question

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!!

Answer 1

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

)

Answer 2

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 = )



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.

Answer 3

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.

Answer 4

The sq. is great. the project is you want some thank you to confirm which purchasers are the 1st 3 purchasers. with the intention to try this you would be able to desire to incorporate a time ingredient interior the team via. you ought to use the HAVING clause with the time portion of confirm the order. case in point HAVING orderdate > 'xx-xx-xxxx'. Order the different quantity indicator case in point you ought to use the final order quantity using the ORDER via orderid (if this might nicely be a quantity) DESC or ASC. wish this helps.

Answer 5

... WHERE 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.

Answer 6

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.

Answer 7

can you at least provide the table schemas ?