January 7, 2020 at 11:54 pm
I cannot think of an easy way
Two Tables
Table 1 PROJECT Project_ID , Client ID
Data
Project_ID , Client ID
101000 A00001
101000 A00002
102000 A1111111
Table 2 CLIENT Client_ID ROLE
Client ID ROLE
A00001 SYS
A00002 SYS
A1111111 SYS
SELECT * FROM PROJECT A INNER JOIN CLIENT B ON A.CLIENT_ID = B.CLIENT_ID
101000 A00001
101000 A00002
102000 A1111111
I am looking to only get the following output
101000 A00001
101000 A00002
There shouldn't be two rows for the PROJECT for the same PROJECT_ID in the table 2 CLIENT
January 8, 2020 at 3:11 am
Back up a step. What's the question you're trying to answer?
Why not post some consumable data so people can just run it and help solve your problem?
use tempdb;
go
CREATE TABLE #Projects (
ProjectID INT NOT NULL,
ClientID VARCHAR(8) NOT NULL
);
GO
INSERT INTO #Projects
VALUES (101000,'A00001'),(101000,'A00002'),(102000, 'A1111111');
CREATE TABLE #Clients (
ClientID VARCHAR(8) PRIMARY KEY,
ClientRole CHAR(3)
);
GO
INSERT INTO #Clients VALUES
('A00001', 'SYS'),
('A00002', 'SYS'),
('A1111111','SYS');
January 8, 2020 at 4:10 am
Thank you.
The end result should just return 101000, as it has two records in client table.
If there is one record in projects and one record in clients this is a match.
January 8, 2020 at 4:37 am
What's the business question you're trying to answer?
January 8, 2020 at 2:43 pm
SELECT ClientID
FROM #Projects
GROUP BY ClientID
HAVING COUNT(DISTINCT ClientRole) > 1
ORDER BY ClientID
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
January 8, 2020 at 5:09 pm
Thank you for your help
January 14, 2020 at 5:59 pm
>> Two Tables <<
where is the DDL for these tables? We need to know the keys, the datatypes of the columns, the constraints and the references. What you posted is pretty much useless garbage. You don't even seem to know that a table because it models a set of entities, has had a plural or collective name. Here's my attempt at fixing what you gave us.
CREATE TABLE Projects
(project_id CHAR(5) NOT NULL PRIMARY KEY,
..);
CREATE TABLE Clients
(client_id VARCHAR(8) NOT NULL PRIMARY KEY
CHECK (client_id LIKE 'A%'),
client_role CHAR(3) NOT NULL
CHECK(client_role IN ('SYS', ...))
..);
Since Clients and Projects are clearly distinct entities and should have their own tables. What you are calling projects is actually a relationship between them. Your incorrect design is what screwing you up. Please notice I've made an assumption that what you're calling a "<something>_role" is an attribute of the client. But because you don't know how to name an attribute, it might be in an attribute of projects.
CREATE TABLE Project_Participants
(project_id CHAR(5) NOT NULL,
REFERENCES Projects,
client_id VARCHAR(7) NOT NULL
REFERENCES Clients,
PRIMARY KEY (project_id, client_id),
..);
INSERT INTO Project_Participants
VALUES
('101000', 'A00001'),
('101000', 'A00002'),
('102000', 'A1111111');
>> There shouldn't be two rows for the Projects for the same project_id in the table 2 Clients <<
But that's what you put in your sample data! Can you please post something that is coherent and follows basic netiquette?
Please post DDL and follow ANSI/ISO standards when asking for help.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply