SQL Query - pretty simple

[johnsmith153]

Table 1

---

uniqueID / employeeName

1 / Dave

2 / Steve

3 / Robert

 

Table 2

---

job / employeeID

"telephone Mr Smith" / 2

"complete report abc" / 1

 

Table 3

---

notes / employeeID

"various text entered here" / 2

"hello, more notes" / 2

 

 

For my sql query I need to check which employees (from table 1 and if any) DO appear in table 2, but then not in table 3. In this case this would be YES, employee 1.

[denno020]

SELECT table1.id, table1.name, table2.job, table3.notes FROM table1, table2, table3 WHERE table1.id = table2.employeeID and table2.employeeID != table3.employeeID;

 

That seemed to work for me .

 

Denno

[denno020]

Sorry retract that... didn't work as well as I had hoped :S

[jay7981]

This should do you good

 

SELECT * FROM name, job, notes WHERE name.id NOT IN (SELECT notes.employeeid FROM notes) AND name.id IN (SELECT job.employeeid FROM job)

 

EDIT: I just built a table structure exactly as you built it and it functioned perfectly. the result was Dave

[Muddy_Funster]

or....

SELECT uniqueID, employeeName 
FROM table1 LEFT JOIN table2 ON
(table1.uniqueID = table2.employeeID)
LEFT JOIN table3 ON 
(table1.uniqueID = =table3.employeeID)
WHERE (table2.employeeID IS NOT NULL) AND (table3.employeeID IS NULL)

 

@jay7981 : SELECT * from a two field database?!?!?! REALLY?? 

[jay7981]

why not use * ? seems like it would be easier to pull all the data incase he plans on using it later

[Muddy_Funster]

It's only got two fields, "SELECT field1, field2 FROM" is a more solid selection method, returning all the data anyway.  That said, what if he doesn't need it?  Do you pack all the clothes, books, games, furnature you own to go on holiday for a weekend "just in case"?   

I haven't heard a single GOOD argument for using SELECT * (ever).

[johnsmith153]

Thanks all for the help.

 

Muddy_Funster, the way you have written it is the most familiar to me as this is how I would write a normal join.

 

In fact the reason I asked the question was the complication with the fact that they must appear in table 2 but can't in table 3.

 

However, this part of your code:

WHERE (table2.employeeID IS NOT NULL) AND (table3.employeeID IS NULL)

...is the main bit that isn't too familiar to me (and is obviously the key bit about there must be a record in t2 and can't in t3).

 

Just one question if I could.

- What if there was a record in table 3 but it held an empty value, lets say:

 

notes / employeeID

"various text entered here" / 2

(empty value) / 3

"hello, more notes" / 2

 

How would I change the query to ensure it still works? (can you ensure it works if there are a few empty values as well?) e.g.:

"various text entered here" / 2

(empty value) / 3

"hello, more notes" / 2

(empty value) / 3

 

The example here is a small part of something bigger, I just wanted to use something like this to explain it. Thanks.

 

Thanks again for the help everyone.

[Muddy_Funster]

NULL is an empty value.  Even white space takes up space, it has ascii code that represents it, so I think you are asking, what would happen in the event of white space being in there?  Then, as you suspect, there would be a result returned.  You can tweek the code to include any combination of fitering that you want.  for other conditions that you want to exclude simply add them using OR within encompasing brackets, eg:

WHERE (table2.employeeID IS NOT NULL) AND (table3.employeeID IS NULL) OR (table3.employeeID = ' ') OR (table3.employeeID NOT BETWEEN 0 AND 9999999))

just like in complex mathimatics, the brackets help dictate the execution order.

 

[johnsmith153]

Brilliant.

 

Thanks again Muddy F.