Home » SQL & PL/SQL » SQL & PL/SQL » SQL query
SQL query [message #682613] Mon, 02 November 2020 12:42 Go to next message
Sql ia
Messages: 3
Registered: November 2020
Junior Member
Based on the below 3 tables, write an SQL query which respond a table with all employee details including department and device details.
Device details should be in single column concatenated with “,”

TBL_ Employees
ID Name Telephone Address Town Department_ID Device_ID
1 Avi 036521458 Herzel 36 Holon 1 145
2 Tali 0543265874 Jerusalem 152 Tel Aviv 1 200
3 Noam 042546987 Dolev 1 Haifa 3 34
4 Sharon 048528524 Gefen 110 Haifa 2 200
5 Yosi 0525467895 Gefen 28 Tel Aviv 4 34
6 Anat 092564857 Golan 95 Raanna 1
7 Moshe 036547852 Herzel 111 Tel Aviv 2 765

TBL_DEPARTMENTS
ID Department
1 R&D
2 Support
3 Sales
4 Marketing

TBL_DEVICES
ID manufacture Model
145 LG G4
200 iPhone S7
34 Samsung Galaxy S6
4 LG G5

what is the correct query for this question? thanks
Re: SQL query [message #682614 is a reply to message #682613] Mon, 02 November 2020 13:28 Go to previous messageGo to next message
Michel Cadot
Messages: 67648
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Welcome to the forum.
Please read the OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Indent the code, use code tags and align the columns in result.

Also always post your Oracle version, with 4 decimals (query v$version), as often solution depends on it.

With any SQL or PL/SQL question, please, Post a working Test case: create table (including all constraints) and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.

What did you try so far?

Re: SQL query [message #682616 is a reply to message #682614] Tue, 03 November 2020 01:42 Go to previous messageGo to next message
Sql ia
Messages: 3
Registered: November 2020
Junior Member
i dont think oracle version is necessary for this question, but i will add it next time.
I just need to solve the question above. thanks
Re: SQL query [message #682617 is a reply to message #682616] Tue, 03 November 2020 02:24 Go to previous messageGo to next message
Michel Cadot
Messages: 67648
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

We don't do homework but we'll help to achieve it.
What did you try? Where are you stuck?

Note: yes, you/we need the Oracle version as (efficient) solution will be different depending on it.

Re: SQL query [message #682618 is a reply to message #682617] Tue, 03 November 2020 04:36 Go to previous messageGo to next message
Sql ia
Messages: 3
Registered: November 2020
Junior Member
I wrote this:
SELECT
Emp.*, d.Department,
devices = STUFF(
(SELECT ','+a.manufacture+' '+a.Model
FROM TBL_DEVICES a
WHERE a.id = emp.Device_ID
FOR XML PATH()), 1, 1, )
FROM
TBL_ Employees emp
Join TBL_DEPARTMENTS d on emp.Department_ID = d.id

would it do the mission?
Re: SQL query [message #682621 is a reply to message #682618] Tue, 03 November 2020 05:21 Go to previous messageGo to next message
Michel Cadot
Messages: 67648
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
would it do the mission?

I don't know i have not the tables and data to check.
For me it is:
SQL> SELECT
  2  Emp.*, d.Department,
  3  devices = STUFF(
  4  (SELECT ','+a.manufacture+' '+a.Model
  5  FROM TBL_DEVICES a
  6  WHERE a.id = emp.Device_ID
  7  FOR XML PATH()), 1, 1, )
  8  FROM
  9  TBL_ Employees emp
 10  Join TBL_DEPARTMENTS d on emp.Department_ID = d.id
 11  /
devices = STUFF(
        *
ERROR at line 3:
ORA-00923: FROM keyword not found where expected
Post what is requested in the format it is requested.
I repeat:

Michel Cadot wrote on Mon, 02 November 2020 20:28

...
Please read the OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Indent the code, use code tags and align the columns in result.

Also always post your Oracle version, with 4 decimals (query v$version), as often solution depends on it.

With any SQL or PL/SQL question, please, Post a working Test case: create table (including all constraints) and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.
...
Re: SQL query [message #682623 is a reply to message #682618] Tue, 03 November 2020 05:34 Go to previous message
John Watson
Messages: 8458
Registered: January 2010
Location: Global Village
Senior Member
STUFF is SQL Server function. Nothing to do with Oracle.
Previous Topic: ORA-29268, 400 - Bad Request trying to send Tweet using REST Api from PL/SQL using UTL_HTTP.
Next Topic: ORA-02315: incorrect number of arguments for default constructor
Goto Forum:
  


Current Time: Fri Jan 22 11:06:20 CST 2021