SQL Query for finding specific row in posgresql server

Question

I have two tables respectively department_location and project

I would like to find any projects running at a different location than the department location.

The resulting should be like this:

The code that I've implemented :

SELECT pro.pnumber,pro.pname,pro.plocation,dept.dlocation
FROM project AS pro,dept_location AS dept
WHERE pro.plocation != dept.dlocation AND pro.dnum = dept.dnumber
GROUP BY pro.pnumber,dept.dlocation

What am I missing?

Edit: Here is my.sql file

You appear to be storing both foreign key and its corresponding *location* value in second table. Only foreign keys should be stored and avoid any duplicate, redundant related data that can be retrieved through `join` query. — Parfait, Dec 22 '19 at 19:31
Also, avoid old-style, implicit join with comma-separated tables with `where`. Instead, use the [explicit join](https://stackoverflow.com/q/1018822/1422451) (current standard of over 25 years). — Parfait, Dec 22 '19 at 19:39

zip · Answer 1 · 2019-12-22T18:35:57.597

0

I see, here is a corrected version:

SELECT pro.pnumber,pro.pname,pro.plocation,dept.dlocation
FROM dept_location AS dept left join project AS pro on 
pro.plocation = dept.dlocation AND pro.dnum = dept.dnumber
Where pro.plocation is null

edited Dec 22 '19 at 18:35

answered Dec 22 '19 at 18:13

zip

3,938
2
11
19

it totally gives irrelevant result which I cannot solve it how. Could you try it [my .sql file](https://gofile.io/?c=icR4qG) – Real Slim Shady 112 Dec 22 '19 at 18:22
Would you believe me if I say it doesn't give any result. @zip – Real Slim Shady 112 Dec 22 '19 at 19:00
Do you mind posting the sample in text so we can run tests with data Slim? – zip Dec 22 '19 at 19:06
Here is my [sql](https://gofile.io/?c=ja377k) table. Could you download it – Real Slim Shady 112 Dec 22 '19 at 19:19

Kevin Spaghetti · Answer 2 · 2019-12-22T19:16:15.777

0

SELECT distinct pname, plocation, dlocation
FROM project INNER JOIN department_location ON project.dnum=department_location.dnumber 
WHERE plocation <> dlocation;

First join the 2 tables using an inner join, then you will get a single table with all the info for every project, then keep only the rows where the project location differs from the department location.

edited Dec 22 '19 at 19:16

answered Dec 22 '19 at 18:36

Kevin Spaghetti

620
4
16

I shaped into according to my tables of your code but it doesn't work SELECT distinct pro.pname, pro.plocation, dept.dlocation FROM project as pro INNER JOIN dept_location as dept ON pro.plocation=dept.dlocation WHERE plocation <> dlocation; – Real Slim Shady 112 Dec 22 '19 at 18:58
see the corrected answer, the inner join creates a new table so you can't refer to columns as pro.pname you need to put just pname, plocation and dlocation – Kevin Spaghetti Dec 22 '19 at 19:16

score 0 · Answer 3 · answered Dec 22 '19 at 19:59

You can group by project and use string_agg() to return the results as a comma separated list:

SELECT 
  p.pnumber, p.pname, p.plocation, 
  string_agg(CASE WHEN d.dlocation <> p.plocation THEN d.dlocation END, ',') deptlocations
FROM project AS p INNER JOIN dept_location AS d
ON p.dnum = d.dnumber 
GROUP BY p.pnumber, p.pname, p.plocation
HAVING SUM((p.plocation <> d.dlocation)::int) > 0
ORDER BY p.pnumber

See the demo.
Results:

> pnumber | pname          | plocation | deptlocations     
> ------: | :------------- | :-------- | :-----------------
>       1 | ProductX       | Bellaire  | Sugarland,Houston 
>       2 | ProductY       | Sugarland | Bellaire,Houston  
>       3 | ProductZ       | Houston   | Bellaire,Sugarland
>      20 | Reorganization | Houston   | Ankara            
>      99 | Geomatics      | Ankara    | Houston

score 0 · Answer 4 · answered Dec 23 '19 at 03:09

I think the simplest solution is:

SELECT p.pnumber, p.pname, p.plocation, d.dlocation
FROM project p JOIN
     dept_location d
     ON p.dnum = d.dnumber  AND
        p.plocation <> d.dlocation;

Functionally, this is your query without the GROUP BY However, you should learn to use proper, explicit JOIN syntax.

SQL Query for finding specific row in posgresql server

4 Answers4