How to use the result from the second choice in my first choice
I am trying to use the second one SELECT
to get some id, and then use that id in the second SELECT
, and I have no idea how.
SELECT Employee.Name
FROM Emplyee, Employment
WHERE x = Employment.DistributionID
(SELECT Distribution.DistributionID FROM Distribution
WHERE Distribution.Location = 'California') AS x
This message is long, but here's a short "tip"
As long as the syntax of my choice is bad, the logic is not. I need something "x". Thus, the second SELECT
is the most important. Then I have to use this "x" in the first one SELECT
. I just don't know how
/ Tip
This is the only thing I could imagine, I am very new to Sql, I think I need a book before I practice, but now that I have started, I would like to finish my little program.
EDIT:
Ok, I looked, joins, I still don't understand.
SELECT Employee.Name
FROM Emplyee, Employment
WHERE x = Employment.DistributionID
LEFT JOIN Distribution ON
(SELECT Distribution.DistributionID FROM Distribution
WHERE Distribution.Location = 'California') AS x
Get the msg at error AS
andLeft
I am using name to find the ID from the top red, I am using the ID I found from the top red in the bottom table. Then I match the ID I found to Green. I am using Green ID to find the matching name
I have it California
as output from C #. I want to use California
to find DistributionID. I am using DistributionID to find EmployeeID. I am using EmployeeID to look up Name
My logic:
Parameter: Distribution.Name (from C#)
Find DistributionID that has Distribution.Name
Look in Employment WHERE given DistributionID
reveals Employees that I am looking for (BY ID)
Use that ID to find Name
return Name
Tables:
NOTE. ... In this example, the Employee image is repeating due to selection, they are actually the only ones
In "Locatie" (middle table) is Location, I am getting the location (again) from C #, I am using California
as an example. I need to find it ID
first!
Sory they are not in English, but here are the create tables:
source to share
Try the following:
SELECT angajati.Nume
FROM angajati
JOIN angajari ON angajati.AngajatID = angajari.AngajatID
JOIN distribuire ON angajari.distribuireid = distribuire.distribuireid
WHERE distribuire.locatie = 'california'
Since you have a table mapping employees to their distribution locations, you just need to join this in the middle to create a mapping. You can use variables if you like the WHERE clause so that you can call it like a stored procedure or whatever you need from the output of your C # code.
source to share
Try this solution:
DECLARE @pLocatie VARCHAR(40)='Alba'; -- p=parameter
SELECT a.AngajatID, a.Nume
FROM Angajati a
JOIN Angajari j ON a.AngajatID=j.AngajatID
JOIN Distribuire d ON j.DistribuireID=d.DistribuireID
WHERE d.Locatie=@pLocatie
You have to add a unique key in the Angajari (Employment) table like this:
ALTER TABLE Angajari
ADD CONSTRAINT IUN_Angajari_AngajatID_DistribuireID UNIQUE (AngajatUD, DistribuireID);
This will prevent duplication (AngajatID, DistribuireID)
.
source to share
I don't know how you connect Emplyee (sic?) And Employment, but you want to use a join to connect two tables, and in the join, specify how these tables are related. They usually look best when they have aliases, so you don't have to repeat the name of the entire table. The following query will give you all the information from the occupancy and distribution tables where the distribution location is california. You can join an employee at work to get a name.
SELECT *
FROM Employment e
JOIN Distribution d on d.DistributionID = e.DistributionID
WHERE d.Location = 'California'
This will return the contents of both tables. Use an alias to select specific entries. [Col_Name] comma separated in select statement, for example d.DistributionID to return the DistributionID from the distribution table
source to share