none
Join returns more records RRS feed

  • Question

  • I have 100 records in table Category, 150 in table CategoryDetail 

    My query below returns 125 records which is more than the number of records in Category. How do I return records that are only in Category?

    SELECT a.LastName, a.FirstName, a.EID, c.LocID, b.FullName 
    FROM Category a
    INNER JOIN CategoryDetail b ON a.EID = LTRIM(Replace(REPLACE(LTRIM(REPLACE(Replace(b.EntityID, ' ','*'),'0', ' ')), ' ', '0'),'*',' ')) 
    INNER JOIN Locationc ON b.EntityID = c.EntityID
    Thursday, October 10, 2019 4:49 PM

Answers

  • Hi MikeGanem2,

    Here is a conceptual example for you.

    -- DDL and sample data population, start
    DECLARE @Category TABLE (ID INT IDENTITY PRIMARY KEY, category VARCHAR(30));
    INSERT INTO @Category (category)
    VALUES
    ('car')
    ,('PC')
    ,('phone');
    
    DECLARE @CategoryDetail TABLE (ID INT IDENTITY PRIMARY KEY, category VARCHAR(30), [type] VARCHAR(20));
    INSERT INTO @CategoryDetail (category, [type])
    VALUES
    ('car', 'white')
    ,('PC', 'Desktop')
    ,('PC', 'Notebook')
    ,('phone', 'Android')
    ,('phone', 'iOS')
    ,('phone', 'Windows');
    -- DDL and sample data population, end
    
    ;WITH rs AS
    (
    	SELECT *, ROW_NUMBER() OVER (PARTITION BY category ORDER BY ID) AS rn
    	   FROM @CategoryDetail
    )
    SELECT c.ID, c.category, rs.type 
    FROM @Category AS c INNER JOIN 
    	rs ON rs.category = c.category
    WHERE rs.rn = 1;
    

    • Marked as answer by MikeGanem2 Friday, October 11, 2019 12:35 PM
    Thursday, October 10, 2019 7:00 PM

All replies

  • Hi MikeGanem2,

    Please provide DDL and sample data population.

    Thursday, October 10, 2019 4:53 PM
  • My apologies, I will not be able to provide data due to some restrictions.

    The Category table has 100 records, the CategoryDetail table has 150 but I want to return only 1 matching records from CategoryDetail.  Query should return 100 records.

    Thursday, October 10, 2019 4:59 PM
  • My apologies, I will not be able to provide data due to some restrictions.

    The Category table has 100 records, the CategoryDetail table has 150 but I want to return only 1 matching records from CategoryDetail.  Query should return 100 records.

    Hi MikeGanem2,

    We don't need all of your data, just a small obfuscated subset of it.

    Along the following:

    -- DDL and sample data population, start
    DECLARE @tbl1 TABLE (ID INT IDENTITY(1,1) PRIMARY KEY, city VARCHAR(30));
    INSERT INTO @tbl1
    VALUES
    ('Miami')
    , ('Orlando');
    
    DECLARE @tbl2 TABLE (ID INT IDENTITY(1,1) PRIMARY KEY, [state] VARCHAR(30));
    INSERT INTO @tbl2
    VALUES
    ('Florida')
    , ('Texas');
    -- DDL and sample data population, end


    Thursday, October 10, 2019 6:41 PM
  • ;with mycte as
    (
    select FullName 
    , LTRIM(Replace(REPLACE(LTRIM(REPLACE(Replace(EntityID, ' ','*'),'0', ' ')), ' ', '0'),'*',' ')) EntityID
    ,row_number() Over(Partition by 
    LTRIM(Replace(REPLACE(LTRIM(REPLACE(Replace(EntityID, ' ','*'),'0', ' ')), ' ', '0'),'*',' ')) 
    Order by FullName ) rn
    From  CategoryDetail )
    
    SELECT a.LastName, a.FirstName, a.EID, c.LocID, b.FullName 
    FROM Category a
    INNER JOIN mycte b ON a.EID = b.EntityID and b.rn=1 
    INNER JOIN Locationc ON b.EntityID = c.EntityID

    Thursday, October 10, 2019 6:53 PM
    Moderator
  • Hi MikeGanem2,

    Here is a conceptual example for you.

    -- DDL and sample data population, start
    DECLARE @Category TABLE (ID INT IDENTITY PRIMARY KEY, category VARCHAR(30));
    INSERT INTO @Category (category)
    VALUES
    ('car')
    ,('PC')
    ,('phone');
    
    DECLARE @CategoryDetail TABLE (ID INT IDENTITY PRIMARY KEY, category VARCHAR(30), [type] VARCHAR(20));
    INSERT INTO @CategoryDetail (category, [type])
    VALUES
    ('car', 'white')
    ,('PC', 'Desktop')
    ,('PC', 'Notebook')
    ,('phone', 'Android')
    ,('phone', 'iOS')
    ,('phone', 'Windows');
    -- DDL and sample data population, end
    
    ;WITH rs AS
    (
    	SELECT *, ROW_NUMBER() OVER (PARTITION BY category ORDER BY ID) AS rn
    	   FROM @CategoryDetail
    )
    SELECT c.ID, c.category, rs.type 
    FROM @Category AS c INNER JOIN 
    	rs ON rs.category = c.category
    WHERE rs.rn = 1;
    

    • Marked as answer by MikeGanem2 Friday, October 11, 2019 12:35 PM
    Thursday, October 10, 2019 7:00 PM
  • >> I have 100 records [sic: rows are not records] in table Category, 150 in table CategoryDetail <<

    A row is nothing whatsoever like a record; that was filesystems. Next why are you splitting out subcategories as if they're totally different from categories we have no sound samples so we don't really know exactly how you design this. But based on a few decades of experience I would guess that you've just committed a design flaw called attribute splitting. Finally tables represent sets of things, so their names should be collective or plural nouns; you telling us that you have only single instance by your singular names

    It is also been considered good manners for 30+ years to post DDL so people might be able to program from your posting.  Why do you think that "a" is a meaningful alias for table named category? A category is pretty clearly an attribute property on a nominal scale, not an entity. According to ISO 11179 rules and data modeling, this column should have been named "<something in particular>_category" since there's no such thing as a generic category floating around the universe. Likewise, the idea of a "entity_id" is absurd. Well, actually it's Kabbalah magic; the belief that God put a magic number (in Hebrew) on everything in the universe to identify it.

    Here is a wild guess as to what you might have met to post. Notice that the table has a key which is not an option. The full name which looks like a computed column is redundant. If you really have a volatile or long list of values for an attribute, then you can reference them in a lookup table. If the values are stable and the domain is small, then use a check () constraint on that column.

    CREATE TABLE Personnel
    (emp_id CHAR(10) NOT NULL PRIMARY KEY,
     last_name VARCHAR(20) NOT NULL, 
     first_name VARCHAR(20) NOT NULL, 
     location_id CHAR(5) NOT NULL,
     job_category CHAR(4) NOT NULL,
      REFERENCES Dictionary_of_Occupational_Titles (job_category)
    );

    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

    Thursday, October 10, 2019 9:56 PM
  • Are you familiar with the novels of Borges? He describes an ancient Chinese encyclopædia entitled Celestial Emporium of Benevolent Knowledge. The list divides all animals into 14 categories:

    • Those that belong to the emperor
    • Embalmed ones
    • Those that are trained
    • Suckling pigs
    • Mermaids (or Sirens)
    • Fabulous ones
    • Stray dogs
    • Those that are included in this classification
    • Those that tremble as if they were mad
    • Innumerable ones
    • Those drawn with a very fine camel hair brush
    • Et cetera
    • Those that have just broken the flower vase
    • Those that, at a distance, resemble flies 

     

    This sounds like what you are trying to do! 


    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

    Thursday, October 10, 2019 10:01 PM
  • I have 100 records in table Category, 150 in table CategoryDetail 

    My query below returns 125 records which is more than the number of records in Category. How do I return records that are only in Category?

    SELECT a.LastName, a.FirstName, a.EID, c.LocID, b.FullName 
    FROM Category a
    INNER JOIN CategoryDetail b ON a.EID = LTRIM(Replace(REPLACE(LTRIM(REPLACE(Replace(b.EntityID, ' ','*'),'0', ' ')), ' ', '0'),'*',' ')) 
    INNER JOIN Locationc ON b.EntityID = c.EntityID

    Your request does not really make sense to me. There are 100 rows in Category, but then you join to CategoryDetail which has more rows. So if one row in Category matches three rows in CategoryDetail, you will get the row in Category three times. So that you get more than 100 rows is perfectly to be expected.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Thursday, October 10, 2019 10:06 PM
  • Hi Mike,

    Try this-

    SELECT T.LastName, T.FirstName, T.EID, T.LocID, T.FullName
    FROM
    (
     SELECT a.LastName, a.FirstName, a.EID, c.LocID, b.FullName,
     ROW_NUMBER() OVER (PARTITION BY a.EID 
     ORDER BY 
     LTRIM(REPLACE(REPLACE(LTRIM(REPLACE(REPLACE(b.EntityID, ' ','*'),'0', ' ')), ' ', '0'),'*',' ')) ) AS r_no
     FROM Category a
     INNER JOIN CategoryDetail b 
     ON a.EID=LTRIM(REPLACE(REPLACE(LTRIM(REPLACE(REPLACE(b.EntityID, ' ','*'),'0', ' ')), ' ', '0'),'*',' ')) 
     INNER JOIN [Location] c ON b.EntityID = c.EntityID
    ) T
    where T.r_no=1;

    Please mark this as answer if it solved your issue. 
    Please vote this as helpful if it helps to solve your issue. 

    Thanks,
    Arulmouzhi

    Friday, October 11, 2019 6:07 AM
  • Hi MikeGanem2,

     

    Could you  please share us your table structure (CREATE TABLE …) and some sample data (INSERT INTO…) along with your expected result? So that we’ll get a right direction and make some test.

     

    Also , I do an example to reappear your issue . But because your connection condition could be 1 to many, this is what happens. Please modify your join conditions to suit your situation.

     
    IF OBJECT_ID('Table1') IS NOT NULL drop table  Table1  
    IF OBJECT_ID('Table2') IS NOT NULL drop table  Table2  
    IF OBJECT_ID('Table3') IS NOT NULL drop table  Table3
    go 
    create table table1 
    (a1 int, b1  int )
    create table table2
    (a2 int)
    create table table3
    (a3 int, b3  int )
    insert into table1 values (1,1),(2,2) ----2 rows
    insert into table2 values (1),(2),(3) ----3 rows
    insert into table3 values (1,1),(1,2),(2,2) ----2 rows
    
    -----we get three rows
    select * 
    from table1 a 
    join table2 b on a.a1=b.a2
    join table3 c on a.a1=c.a3
    /*
    a1          b1          a2          a3          b3
    ----------- ----------- ----------- ----------- -----------
    1           1           1           1           1
    1           1           1           1           2
    2           2           2           2           2
    */
    
    -----we get three rows
    select * 
    from table1 a 
    join table2 b on a.a1=b.a2
    join table3 c on a.a1=c.a3 and a.b1=c.b3
    /*
    a1          b1          a2          a3          b3
    ----------- ----------- ----------- ----------- -----------
    1           1           1           1           1
    2           2           2           2           2
    */

    Hope it will help you.

     

    Best Regards,

    Rachel


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, October 11, 2019 6:16 AM
  • thanks
    Friday, October 11, 2019 12:35 PM